Default Selected Items based on different List with possible multiple Values

[Lephas]

Hi guys

I tried a couple of things how to solve this but I am a little bit stuck.

I have 2 Lists:

1) Input Form List: Two columns Country(A) and Regions(A). Both are Coice fields but Region can have multiple Regions selected.

2) Region Mapping List: Two columns, Countries(B) and Region(B) as well. Both Choice fields but Countries can have multiple Items selected.

Country(A) is a the country field of the currently logged in User

The Region is a Mapping list to associate Regions with countries.

For example

Region: EU

Countries: Germany, England, Denmark etc.

Region: NA

Countries: USA, Canada, Mexico

Region: America:

Countries: USA, Canada, Mexico, Brazil, Peru etc.

For Regions(A) I want to have selected by Default the Region(s) from the Region Mapping List(2) which contains the same Countries(B) like the currently signed in User Country (A)
So for example if i am from Germany, EU will be selected as a Region

If i am from Mexico i will have 2 Regeions selected: NA and America

So currently i think my best approach is the following but it's not working:

DefaultSelectedItem Property of Regions(A)

If(
    'FormA'.Mode=FormMode.New;
    {
		Value: Filter(
        ('Region Mapping');Country_DataCardValue.Selected.Value in Countries.Value
        ).Region 
        //Countries(B) is the Field from the Region Mapping List
        // Country_DatacardValue.Selected.Value is Country(AI
	};
    Parent.Default
)

Am i any close or do i have to apporach this differently?

Any help is highely appreciated!

[RandyHayes · Accepted solution]

@Lephas 

You are on the right road!

The only part that will be important is to make sure the the records table in the DefaultSelectedItems property has the exact same schema as the Items property of the combobox.  As long as they do and as long as the values are in the Items table, then you will have selected items.

Now, for your formula, you need to have a table, not just a single record.  So your formula should be:

Coalesce(
    ForAll(
        Filter(
            'Region Mapping'; Country_DataCardValue.Selected.Value in Countries.Value
        );
        {
            Value: Region 
        }
    );
    Parent.Default
)

This will give you a table of records with a Value column that contains the region based on the filter.

I hope this is helpful for you.

[Lephas]

Thank you so much! i never heard of this function but after reading into it - it makes a little bit more sense and it works just like i wanted.
I have a follow up question though: The formula works great but i get a warning about delegation and that it might not work for bigger Datasets - is this because of the In function?

[RandyHayes]

@Lephas 

Yes, the In operator is not delegable to the datasource and you will get this warning.

If your 'Region Mapping' datasource has more than 2000 records, then the In operator will be an issue.

If it is less than 2000 (and more significantly less than your record limit set in your app) then you can ignore the warning.