filter collection from other collection value in dropdown

[marial16]

hi,

I have drop down fields in a form. I want to filter dd Country based on division Lookup Field dd selection.

Those Fields and Project Owner dd are pre populated based on the logged in User.

dd Country : Items:currentuserCountry  

dd division: Items:currentuserDivision

i get the values in Items from 2 collections on the on Visible of  the main screen:

ClearCollect(currentuserDivision,ShowColumns(Divisions,"Title","ID"));
ClearCollect(currentuserCountry,ShowColumns(Countries,"Title","ID","division"));  (division is a lookup column to SP list Divisions)

on the on Select of a button in the main screen i use :

Clear(currentuserCountry);
ForAll(Filter(PMs,Users.Email=User().Email), Collect(currentuserCountry,Country.Value));  and same logic for division.

How can i filter the country based on the division?

[marial16]

Since i have many lists with lookup fields and nested tables in my app, i am afraid i cant change the implementation i already have.

[EvanTse]

hmmm... right. I don't think you would need to change the implementation too much.

My understanding of the issue is that your division and country collections do not 'connect'.

your issue is the way you are collecting the division and country data. It should be collected into one collection.

I don't think this formula is great:

ForAll(Filter(PMs,Users.Email=User().Email), Collect(currentuserCountry,Country.Value)); 

You should be doing something like:

ClearCollect(colCurrentuserPMs,(Filter(PMs,Users.Email = User().Email))

Which would filter your PM table to only show relevant rows to the current user, you should then use that collection for the drop downs as it should have the relationship between division and country

I don't really see how it makes sense to split up the Divisions and countries into separate collections then find a way to marry them back up again, when you can just filter the main source of the data and then slice and dice it as you need.

[marial16]

Isnt there a workaround, to connect the two collections, using addColumns for example?

[EvanTse]

I don't really understand why you want to use addcolumns over actually collecting the data together into 1 collection.

What are you trying to achieve by having 2 separate, unrelated collections?

from my point of view, you are trying to use 1 data source and create 2 separate collections then add them back together.

You created extra work for yourself by separating them then trying to join them back up which doesn't make sense to me.

feel free to correct me if my understanding above is incorrect.

[marial16]

Hello again,

I have changed the logic based on what you indicated.

main screen onVisible: ClearCollect(PMs,ShowColumns('Project Managers',"ID","Country","Division","Users","Title"));

                                     ClearCollect(AllData,Filter(PMs,Users.Email=User().Email));   (to only get my data as a user)

at division dropdown Items:

Distinct(AllData,Division.Value)   (it works)

at country dropdown Items: It seems to bring the records but since i haven't declared the Country.Value the values are Blank!

Filter(AllData, DataCardValue5.Selected.Result in Division.Value).Country

I do not have any code onSelect of the button on the main screen where i was using the ForAll function

[EvanTse]

Hey @marial16 - Looks like you are almost there.

Try changing your formula to:

Filter(AllData, DataCardValue5.Selected.Result in Division.Value,Country.Value)

If that doesn't work, try doing some debugging by checking if the AllData collection, country column records actually have data.
You can do this by going to collections, selecting the all data collection then clicking on a purple box in the country column.

[marial16]

Nope..I am afraid it brings no data at all. Country has values as shown below.Perhaps there s a middle step we are missing