cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SelfTeach
New Member

Filter collection against another collection.

I am having a difficult time understanding why filtering my collections this way yields nothing. I am trying to take a collection table with a set of controls and filter it against another collection table of controls with group identifiers. To do this I am taking the control column of collection1 and comparing it to the control column of collection2. The final filter output being; whatever controls in collection1 present in collection2. 

 

colControl

  • Control-ID (Unique Key)
  • Requirements
  • Questions

colCriteriaControlGroup

  • CCG-ID (Unique Key)
  • Control-ID (Foreign Key - N:1)
  • TC-ID (Foreign Key - N:1)

 

When the screen is visible I collect the colControl and colCriteriaControlGroup. I then re-collect the colCriteriaControlGroup by filtering based on the TC-ID group. All of which works perfectly fine. Done so by:

OnVisible = 

ClearCollect(colControl, 'tbl-Controls');
ClearCollect(colCriteriaControlGroup, 'tbl-Criteria-Control-Groups');

ClearCollect(colCriteriaControlGroup, Filter(colCriteriaControlGroup, varTypeCriteria in 'TC-ID'.'TC-ID'));

 

colControl is displayed in a gallery. I have tried several different ways of filtering, all of which give me no error message but result in an empty gallery. The filter I understand 'should' work:

Gallery Items: Filter(colControl, 'Control-ID' in colCriteriaControlGroup[@'Control-ID'].'Control-ID')

 

I have logically tested this by setting the gallery items to colControl and setting a label text property to:

  • If(ER_Gal_Control.Selected.'Control-ID' in colCriteriaControlGroup[@'Control-ID'].'Control-ID', true, false)

The result is false. I have further checked this by setting other text fields to the following formulas:

  • ER_Gal_Control.Selected.'Control-ID'
  • LookUp(colCriteriaControlGroup, 'Control-ID'.'Control-ID' = ER_Gal_Control.Selected.'Control-ID').'Control-ID'.'Control-ID'
  • LookUp(colCriteriaControlGroup, ER_Gal_Control.Selected.'Control-ID' in 'Control-ID'.'Control-ID').'Control-ID'.'Control-ID'

All of which give the exact same output. 

 

I am under the impression that this has something to do with the Lookup data types involved here seeing that the colCriteriaControlGroup's 'Control-ID' field is a foreign key of colControl's 'Control-ID'. 

 

Additionally I have tried this with mock tables which does work (gives me the result i'm after, a dataset composed of records that exist in both tables/collections.

 

Table1

-------

1

2

3

4

5

 

Table2

-------

1

2

3

 

Filter(Table1, Column1 in Table2.Column1)

Result = 1, 2, 3

 

I am open to anything at this point. Thank you for the insight in advance.

0 REPLIES 0

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (2,283)