cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AlexHelliwell
Regular Visitor

Issue with cascading dropdown from multiple tables within CDS/Dataverse

Hi there,

 

I am currently trying to build a Near Miss Accident reporting solution within PowerApps for our subsidiaries within the construction industry to try.  I would like the user to select which Subsidiary they belong to from the first dropdown, which would then filter the 2nd dropdown selection of customers. This would finally filter a third dropdown box with only sites belonging to the selected customer. 
I have set up tables for Subsidiaries, Sub Customers, Sub Sites and a Near Miss Log table to record the incident reports themselves. These have the necessary lookup columns/relationships.

 

I am building the app to report near misses and am getting an error from the 2nd dropdown when I try and filter it. 

 

the formula I am using is- 

Items= Filter(Subsidiaries,'Sub Name'= Dropdown1.Selected)

 

'Sub Name' is the look up field in the 'Sub customer' table.

 

PowerApps is saying that = is an invalid argument type and I cannot find any help from online videos or posts that seem to help.

 

Any ideas on what I'm missing or what I've set up incorrectly? 

 

Many thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions

'Owing Subsidiary' looks like a lookup to an entity, try "." after to select the desired filter criteria. 

For the Selected.Value, if your control is bound to the whole entity/table you don't need the ".Value" and can pick the entity field/column directly.

Hope this helps...

View solution in original post

4 REPLIES 4
EricRegnier
Super User II
Super User II

Hi @AlexHelliwell,

If I understand correctly, the 1st dropdown is Subsidiaries, then customers and Sites, respectively. If so, then the formula/expression pasted seems invalid. There's not filter required on Subsidiaries since it's the 1st dropdown, if filter the 2nd (i.e. Customers) it would look something like this:

Filter(Customers, 'Sub Name' = Dropdown1.Selected.Value)

Note that I put .Value after Selected and Dropdown1 are the Subsidiaries.

Hope this helps...

Hi @EricRegnier 

Thank you very much for your reply.
While I was waiting for someone to get back to me I tried to simplify my data in a test to try and figure out the problem. 
I created 2 tables-
'Test Sub' with 'Sub Name' as the Primary Column.
'Test Customer' with 'Customer name' as the primary and a lookup column to the 'Test Sub' table named 'Owning Subsidiary'.
Created an app with 2 dropdowns and tried your formula and I am still getting the same errors. Could this be something to do with the way I'm setting the data up?

 

It doesn't seem to like the .Value stating- "Name isn't valid. The identifier isn't recognised." 

AlexHelliwell_0-1607685868312.png

Also giving an "invalid argument type" still for the "=".

Any assistance would be muchly appreciated! 

'Owing Subsidiary' looks like a lookup to an entity, try "." after to select the desired filter criteria. 

For the Selected.Value, if your control is bound to the whole entity/table you don't need the ".Value" and can pick the entity field/column directly.

Hope this helps...

View solution in original post

Thank you Eric, that seemed to do the trick and now all cascading dropdowns are working as required.

 

The final formula that ended up being used in case anyone else has similar issues was-

Filter('Sub Customers', 'Owning Sub'.'Sub Name' = Dropdown1.Selected.'Sub Name')

 

Very much appreciated 👍

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (2,140)