Filter Datasource based on collection

(0) Share

Report

Posted on by ScottRMeredith

141

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:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Verified answer

Rusk 1,369 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
ScottRMeredith 141 on at

Like (0)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
ScottRMeredith 141 on at

Like (1)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No