Non continous cascading filter on dataverse lookups

Like (0) Share

Report

Posted on 26 Jul 2023 08:00:01 by Community Power Pla...

Hi,

I have created 3 tables in Dataverse,
1. Country
Column - Name (Single Line Text)

2. State

Column - Name (Single Line Text), Country (Lookup)

3. City
Column - Name (Single Line Text), State (Lookup)

I need to configure Cascading Dropdown for City. For example, if the end-user selects any country, the state dropdown options should be filter-based on selected Country, then the end-user selected the state city dropdown should be a filter-based on the selected State. (That's easy!)
However if the state is not filtered, the end-user should be able to select based on all cities of that the selected country..

Is this possible with model driven app? May be with JS web resource?

Categories:

Microsoft Dataverse

All responses (14)

Answers (0)

Parvez Ghumra 1,579 Moderator on 31 Jul 2023 at 09:48:18

Like (0)

Report

Re: Non continous cascading filter on dataverse lookups

@Anonymous If the relationship between Product Group and Product Code is a simple 1:N, you don't need script to apply the filter on the Product Code lookup on Order. This can be achieved by simple form configuration.

Go into the properties for the Product Code lookup on the Order form, and simply select the filtering properties to only allow selection of Product Codes that are related to the selected Product Group.

Hope that makes sense.
Community Power Pla... on 31 Jul 2023 at 02:29:44

Like (0)

Report

Re: Non continous cascading filter on dataverse lookups

Hi Parvez,

I changed to code. No errors now. However I am getting all the selections instead of the filtered critieria.

formContext.getControl('cas_productcode_order').addPreSearch(function () {
productlookup();
});

function productlookup(executionContext) {
var formContext = executionContext.getFormContext();
if (formContext.getAttribute('cas_productgroup_order').getValue() != null) {
var productgroup = formContext.getAttribute('cas_productgroup_order').getValue();
var productgroupID = productgroup[0].id;
var productgroupName = productgroup[0].name;
fetchxml = "<filter type='and'><condition attribute='cas_productgroup_order' operator='eq' uiname='" + productgroupName + "' uitype='cas_productgroup' value='" + productgroupID + "' /></filter>";
console.log('kkkkk',fetchxml)
formContext.getControl('cas_productcode_order').addCustomFilter(fetchxml, 'cas_productcode');
};
}

Seeking your advice as where the mistake is.
Thank you

Stephen

Capture101.JPG
Community Power Pla... on 29 Jul 2023 at 14:32:48

Like (0)

Report

Re: Non continous cascading filter on dataverse lookups

Hi Parvez,
I am running into some troubles with filter.
I have a ProductGroup entity and a ProductCode entity with a Productgroup lookup field to the ProductGroup entity.
I have an Order form with ProductGroup field ("cas_productgroup_order") and based on this field I will filter the Productcode field ("cas_productcode_order").
I have the following code:

function productlookup(executionContext) {
var formContext = executionContext.getFormContext();
if(formContext.getAttribute('cas_productgroup_order')!=null){
var productgroup = formContext.getAttribute('cas_productgroup_order').getValue();
var productgroupID = productgroup[0].id;
var productgroupName = productgroup[0].name;
formContext.getControl('cas_productcode_order').addPreSearch(function(){

if(productgroup!=null){
var fetchQuery = "<filter type='and'><condition attribute='cas_productgroup_order' operator ='eq' value='"+productgroupID+"'/></filter>";
console.log('rrrr',fetchQuery);
formContext.getControl('cas_productcode_order').addCustomFilter(fetchQuery);
}
}
}

When I select Productcode field ('cas_productcode_order'), I get error: 0x80041103 Query Builder Error.

console.log of var fetchQuery is <filter type='and'><condition attribute='cas_productgroup_order' operator ='eq' value='{2A3E0536-802B-EE11-BDF4-000D3ADA2B65}'/></filter>.

I suspect the error is in value='"+productgroupID+"'.
Where did i go wrong?
Parvez Ghumra 1,579 Moderator on 28 Jul 2023 at 10:30:21

Like (0)

Report

Re: Non continous cascading filter on dataverse lookups

@Anonymous Yes for accessing column controls and attribute values on the form, using the column logical name rather than schema name. The schema name is generally for access the data via the Web API
Community Power Pla... on 28 Jul 2023 at 08:57:30

Like (0)

Report

Re: Non continous cascading filter on dataverse lookups

Hi Parvez

Thank you for your help. I used that on another code block.
I ticked that checkbox, and pass also the logical name of the lookup field and it did not spit out an error.
Should the logical name be used instead of schema name? If I use the schema name, it spit out (Cannot read properties of null (reading 'getValue')).
Parvez Ghumra 1,579 Moderator on 28 Jul 2023 at 08:30:51

Like (0)

Report

Re: Non continous cascading filter on dataverse lookups

@Anonymous If you are calling this function directly from an event handler in your Model Driven App form, in the form designer, go to the event handler where this function is called. You should see a check box to check that will pass the execution context object to the function

If you're calling this function from another function, the make sure the function call passes in the execution context object
Community Power Pla... on 28 Jul 2023 at 08:26:29

Like (0)

Report

Re: Non continous cascading filter on dataverse lookups

Hi Parvez
it seem like I am not. What should I do? My objective is to get value of the lookup field "cas_productgroup".
Regards.
Parvez Ghumra 1,579 Moderator on 28 Jul 2023 at 08:14:08

Like (0)

Report

Re: Non continous cascading filter on dataverse lookups

@Anonymous Are you passing in the executionContext object when you wire up this function in your form?
Community Power Pla... on 28 Jul 2023 at 04:21:15

Like (0)

Report

Re: Non continous cascading filter on dataverse lookups

Thank you. Let me try that.
Aside from this, I am trying to query a lookup field:

function showhide(executionContext) {
var formContext = executionContext.getFormContext();
var subproductgroup = formContext.getAttribute("cas_productgroup").getValue();
console.log(subproductgroup)
if (subproductgroup !== null) {
formContext.getControl("Order_subproductgroup").SetVisible(false);
}
else {
formContext.getControl("Order_subproductgroup").SetVisible(true);
}
}

But I am getting an error:
Cannot read properties of undefined (reading 'getFormContext')Session Id: a0b926b4-dbc1-4eb2-9613-

What did I do wrong?
Fubar 8,047 Super User 2025 Season 2 on 28 Jul 2023 at 04:09:15

Like (0)

Report

Re: Non continous cascading filter on dataverse lookups

As per others, whilst they will still work for now avoid using the deprecated features, also to pass the Solution Checker javascript functions also need to 'use strict' but less of an issue than the deprecated api calls.

at least one issue with your FetchXML query, the value provided to the Like operator should be wrapped in '%' e.g. ........ operator='like' value='%" + Productgroup + "%' ......

Community site session details

Non continous cascading filter on dataverse lookups

Helpful resources

News and Announcements

Quick Links

Announcing our 2025 Season 2 Super Users!

Paul Stork – Community Spotlight

Congratulations to the June Top 10 Community Leaders!

Leaderboard > Power Apps

Featured topics

Product updates

Community site session details

Non continous cascading filter on dataverse lookups

Helpful resources

News and Announcements

Quick Links

Announcing our 2025 Season 2 Super Users!

Paul Stork – Community Spotlight

Congratulations to the June Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Power Apps

Featured topics

Product updates