Issue with cascading dropdown from multiple tables within CDS/Dataverse

[AlexHelliwell]

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

[EricRegnier]

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...

[AlexHelliwell]

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." 

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

Any assistance would be muchly appreciated! 

[EricRegnier · Accepted solution]

'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...

[AlexHelliwell]

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 👍