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?

[EvanTse]

Hey @marial16 

I don't really understand you data source set up, but you can filter the dropdown of the country based on the selected filter by updating the country dropdown items property using the filter() function.

As an example, your country drop down item property would be:

Filter(currentuserCountry, Division = DivisionDropDown.Selected.Value)

You will need to change the red text to reference the division drop down field.

[marial16]

i an afraid this doesn't work in my case.

I connect the form to a List called Tasks , where i have the Country and Division columns as lookup fields.

I have another list Pr Managers with Country and Division columns as lookup fields

i put this list in an initial collection PMs to filter the Users and pre fill the drop downs in the form

ClearCollect(PMs,ShowColumns('Pr Managers',"ID","Country","Division","Users","Title"));

[EvanTse]

@marial16 - which part of it doesn't work?

[marial16]

I have only 1 country for domestic division and filtering the User i get >1 countries for international division

so drop down divisionLookupFiled -> items: Distinct(currentuserDivision,Value)

for Country drop down -> DisplayFields: ["Value"]     Items: Filter(currentuserCountry, Division in DataCardValue5.Selected.Result)

I cant use Value first of all. I can only use Result  but i get an error (cannot use text values in this context)

I tried to change the DisplayField to ["Result"] . Nothing works

[EvanTse]

@marial16 

Using the Result rather than value is fine.

I believe the formula doesn't work because your syntax is incorrect. Try switching the Division and DataCardValue5.Selected.Result:

Items: Filter(currentuserCountry, DataCardValue5.Selected.Result in Division).

In regards to the display field issue, try the above formula first, if that doesn't work, try the formula below:

Filter(currentuserCountry, DataCardValue5.Selected.Result in Division, Division)

The difference is that the second formula will only return a list of divisions.

[marial16]

When you say 'in Division' do you mean the division.Value?

Which column is the Division you are referring to?

[EvanTse]

The formula should be put in the Country drop down items property.

[marial16]

True, it is where i write it.

these are the 2 collections i want to interconnect.

i get no results at this point because of Clear(currentuserCountries); i use at the button of my main screen

[EvanTse]

hmmm... looks like there is an issue with your clearcollect statements.

I would suggest collecting the data into 1 collection instead, like this:

ClearCollect(colCollectionName, Filter(SharePointListName, 'Pr Manager' = User().email))

Where:

colCollectionName is whatever you want to name the collection

SharePointListName is where the data is coming from (I assume its a sharepoint list).

 'Pr Manager' = User().email is your logic to filter the list based on the current signed in person (update this to be more relevant to your case, I don't know how you are filtering this)

You can then use that collection in the dropdowns.

In Divisions drop down items property you would have something like Distinct(colCollectionName,Divisions)

In countries drop down item property you would have something like Filter(colCollectionName, DataCardValue5.Selected.Result in Division, Division)