How to compare table field value to table value from another collection

[rajesh6]

Hello,

I have two collections. the below collection has region field which has table column 

ClearCollect(
QuickLinksColl,
AddColumns('Quick Links',
"Title",Name,"Divisions",Division,"RegionValues","region",Region
)
);

 this is the one more collection

Clear(RegionBaseListColl);
Collect(RegionBaseListColl,RenameColumns(Filter(Split(RegionBaseList,","), !IsBlank(Result)), "Result", "Value1"));

both collections region field can give table values if I use ForAll function to loop those collection based on region field not working as expected 

ForAll(
QuickLinksColl,
ForAll(
RegionBaseListColl,
If(region=Value1,
Collect(TestFinalQuickLinks,
{
    name1:Title,
    divisions1:Divisions,
    TestValue1:Value1
}
)
)
)
);

[RandyHayes]

@rajesh6 

You are trying to use ForAll functions as For/Next loops...they are not!  They are functions that return tables based on other tables of records.

You have a lot of collections going on that you basically don't need, but if for some reason you need all of these things to be collections, then your formula should be:

Collect(TestFinalQuickLinks,
    ForAll(
        Filter(QuickLinksColl, region in RegionBaseListColl.Value),
        {name1: Title,
         divisions1: Divisions,
         TestValue: Value1
        }
    )
)

 The whole formula can be simplified to reduce the amount of collections with the following:

Collect(TestFinalQuickLinks,
    With({_baseList: Filter(Split(RegionBaseList,","), !IsBlank(Result))},
        ForAll(
            Filter(
                'Quick Links', Region in _baseList.Result
            ),
            {name1: Name,
             divisions1: Division,
             TestValue: Region
            }
        )
    )
)

And, if you're using this for an Items property ultimately, you can eliminate the collect part of the above and use it in the Items property.

So, that should cover most of the scenarios.

I hope this is helpful for you.

[rajesh6]

thanks for the reply

still some issue region field also table value and baselist also table 

[RandyHayes]

@rajesh6 

So then what is Region in your list?

[rajesh6]

Region field is multichoice field example:1.APAC,NA,IMEA

               2.NA,TC 

[RandyHayes]

@rajesh6 

That's a key element to know!

When you deal with these type of multi-select columns, you start to get into problems from a delegation standpoint...so, if your list is less than 2000 record and not expected to grow beyond that, then you can use the following:

Collect(TestFinalQuickLinks,
    With({_baseList: Filter(Split(RegionBaseList,","), !IsBlank(Result))},
        ForAll(
            Filter(
                'Quick Links', LookUp(ForAll(Region, Value in _baseList.Result), Value, true)
            ),
            {name1: Name,
             divisions1: Division,
             TestValue: Region
            }
        )
    )
)

The key here is the Filter part for Quick Links.  In order to match a record, the criteria needs to be true.

So, the criteria here will be a LookUp.  LookUp will look at a table and find the first record that meets its criteria. 

In this case, the table will be a table of Values.  How it is made is the ForAll will iterate through the Region column.  A choice column will have a Value column in its table of records, so we (for each record) will see if any of the multi-selected values is in the baseList.  If it finds one, then that record of the table will have a Value of true, if not it will be false.  So, now we have the table from the for all - again, a single column table of Value that will contain either true or false.  Now, we provide that to LookUp as its table.  And then we give a simple criteria of Value.  So that means that any record in the ForAll table that has a Value that is true, then it will match the LookUp.  So, then, what we return from LookUp is just true.  THAT will be the criteria for the Filter.  Any record that the criteria evaluates to true will be used.

Hopefully that is clear and helpful.