Skip to main content

Notifications

Power Apps - Building Power Apps
Answered

Filter Datasource based on collection

(0) ShareShare
ReportReport
Posted on by 133

So I have looked at this solution, but have not been able to apply it to my situation successfully.

 

https://powerusers.microsoft.com/t5/Building-Power-Apps/Filter-collection-by-values-of-another-collection/m-p/296436

 

I have a dataverse table that I have a filed of related record primary keys stored in a string separated by "::" (ie: SB22023-001001::SB22023-001061).  I have successfully been able to split that string into a collection (colRelated, with a single column of SB2)  I then add the record's own primary key because if I am looking at the parent record it will not have a value in the related field string.

 

Clear(colRelated);ForAll(Split(varRecordDetail.RelatedSB2Numbers, "::"), Collect(colRelated, {SB2: Result})); Collect(colRelated, {SB2: varRecordDetail.SB2Number})

 

So for an example let's say I was viewing the record with Primary Key "SB22023-001000" and in it's related string it had the value of "SB22023-001060::SB22023-001061"  My collection would look like this:

SB2

SB22023-001060

SB22023-001061

SB22023-001000

 

I then want to take all the values of the collection and filter my data source for those values to display the records in a gallery.  The link above had the below formula as the answer.

Filter(Table1,Column1 in Table2.Column2).Column1

 

I was substituting my data source for table 1, and my collection for table2, but when I tried that, intelisense would not give me the column name as a choice (ie: colRelated.SB2, the .SB2)

Categories:
  • ScottRMeredith Profile Picture
    ScottRMeredith 133 on at
    Re: Filter Datasource based on collection

    So I had been thinking about it a little bit wrong.  I have a list in my collection of my primary keys that were found in the related key field.  My error in logic was I was wanting to continue to search off the related key field that had a string of numbers in it.  I changed the field in the filter to the primary key field and it worked like a charm.

  • ScottRMeredith Profile Picture
    ScottRMeredith 133 on at
    Re: Filter Datasource based on collection

    I didn't initially try that as since my source comparison field is storing the values as a string (ie: SB22023-001001::SB22023-001061) I would get limited or no hits back on the filter.  I tried it just now to double check that is how it would behave, but I was correct in my assumption.  Basically I want to do 

    Filter(datasource, colRelated.SB2 in RelatedSB2Numbers)

    So it returns values for all "colRelated.SB2" values in the collection.  I don't know if there is a way to wrap this in a ForAll somehow.  Maybe I can try and create a second collection within a ForAll that is gathering each matching record and I can use that second collection for my gallery data source.

     

  • Verified answer
    Rusk Profile Picture
    Rusk 1,369 on at
    Re: Filter Datasource based on collection

    I think the issue could be that the example formula you linked is for a multi-column table.  In your case, your colRelated collection is a single column.  

     

    What happens if you try:

     

    Filter(YourDataSource, ColumnName in colRelated)

     

     

    ...replacing ColumnName with whatever that column is in your data source

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,508

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,839

Leaderboard