Filter Dropdown items from Separate Table

[Revlock97]

I have 2 tables (Groups and GroupMembers) wherein I wish to filter all distinct ProductNames on a dropdown from Groups which is connected with GroupMembers via GroupID PK but I'm having trouble getting it to work. Thank you very much in advance for any help.

Screen OnVisible:

ClearCollect(colUserGroupID, Filter(SAP_GroupMembers, EmailAddress = varUserName.Email));
ClearCollect(colProductGroup, Filter(SAP_Groups, GroupID = colUserGroupID.GroupID));

Dropdown Items:

Distinct(colProductGroup, ProductName)

[v-bofeng-msft]

Hi @Revlock97 ,

Please try :

ClearCollect(colUserGroupID, Filter(SAP_GroupMembers, EmailAddress = varUserName.Email));
ClearCollect(colProductGroup, Filter(SAP_Groups, GroupID in colUserGroupID.GroupID));

Best Regards,

Bof

[Revlock97]

@v-bofeng-msft  It gives the following error for GroupID:

[v-bofeng-msft]

Hi @Revlock97 ,

Could you please tell me what the 'GroupID' in SAP_GroupMembers is? Could you show me its data structure? Or provide a demo of the SAP_GroupMembers in JSON format?

Best Regards,

Bof

[Revlock97]

@v-bofeng-msft This is its ERD. I just want to display the ProductName (Or still Product in the ERD) on the dropdown which is  filtered after taking into account all groups possibly assigned to a single group member. Thanks!

[v-bofeng-msft]

Hi @Revlock97 ,

Could you please tell me what the data source is ? Are there SharePoint lists? Could you provide some screenshots (please mosaic the private information)?

Can you determine that both groupids are string column? Is it possible that one of them is a lookup field?

I've made a simple test and it worked well:

Best Regards,
Bof

[Revlock97]

Hello @v-bofeng-msft 

I'm using Dataverse. As for the GroupIDs, yes the GroupID in SAP_GroupMembers is of LookUp datatype...the other GroupID is of Autonumber datatype

Apologies for being quite unclear I'm still kinda new to all this. Thank you!

[v-bofeng-msft · Accepted solution]

Hi @Revlock97 ,

Please try:

ClearCollect(
    colProductGroup,
    ForAll(
        Filter(SAP_GroupMembers, EmailAddress = varUserName.Email),
        GroupID
    )
)

Best Regards,

Bof

[Revlock97]

@v-bofeng-msft I  just made some adjustments and it's working perfectly as intended!  Thank  you very much!