Unanswered

Filtering a collection using another collection and the Split function

(0) Share

Report

Posted on by Community Power Pla...

I've received an interesting request and I'd love some feedback on what the communities suggested "best practice" would be.

I have a collection that is simply a list of strings titled 'OptionsCollection'.

Value

Roof Style - Gable

Roof Material - Metal

Siding - Lap

I have a second collection that is a list of tasks titles 'TasksCollection'.

Task	Options	Price
Install Roof	Roof Material - Metal, Roof Style - Shed	1460.50
Install Roof	Roof Material - Metal, Roof Style - Gable	1660.00
Install Roof	Roof Material - Composite, Roof Style - Shed	1220.50

My goal is to display a gallery showing the TaskCollection, however I would like to only display records where Option exists in the OptionsCollection - if any - and if not to still display all records. In the case of the above example, only the 2nd record should be displayed.

I believe I could accomplish this with a combination of Filter, ForAll and Split but believe there might be a better method.

Thanks!

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (0)

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report
@Anonymous

The following formula will give you the results you are looking for:

Filter( TasksCollection, With({_items:Filter(Split(Options, ","), !IsBlank(Result))}, CountIf( ForAll(_items, Trim(Result) in OptionsCollection.Value), Value ) = CountRows(_items) ) )

I hope this is helpful for you.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

@RandyHayes this seems to almost do the trick, but I'm getting an error on Split(Options, ",").

Options doesn't appear to be a valid datasource, but I'm also unable to use a single reference, like ThisRecord.Option or ThisItem.Option.

Any suggestions?

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report

@Anonymous

In the formula I provided, the part was "Options", not "Option". That was based completely on the example you provided. In that example, you called your column "Options".

You also called your collection "TasksCollection".

Those are the two things that are dark underlined in your formula picture and the only difference that I see.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Oops! I misspelled the collection name. I am no longer receiving an error, however I also am not ever getting any results of the Filter().

I've tested both with and without matching Options existing in the OptionsCollection, including a completely empty OptionCollection.

Am I missing something from what would equate to the 'else' / 'without' logic of your filter?

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report
@Anonymous

I used your exact scenario in the Items property of a gallery.

Here is the Items property:

With({ OptionsCollection: ["Roof Style - Gable", "Roof Material - Metal", "Siding - Lap"], TasksCollection: Table( {Task: "Install Roof", Options: "Roof Material - Metal, Roof Style - Shed", Price:1460.5}, {Task: "Install Roof", Options: "Roof Material - Metal, Roof Style - Gable", Price:1660}, {Task: "Install Roof", Options: "Roof Material - Composite, Roof Style - Shed", Price:1220.5} ) }, Filter( TasksCollection, With({_items:Filter(Split(Options, ","), !IsBlank(Result))}, CountIf( ForAll(_items, Trim(Result) in OptionsCollection.Value), Value ) = CountRows(_items) ) ) )

Here is what it resulted in:

So, the formula works for the scenario you provided.

What are you now doing that is different than that scenario?

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
@RandyHayes The options that exist in the OptionsCollection aren't always an exact match of ALL of the options listed on the TasksCollection.

For example, if I refactor your snippet to the below (removing the 2nd option), I no longer receive any items in the gallery.

With({ OptionsCollection: ["Roof Style - Gable", "Siding - Lap"], TasksCollection: Table( {Task: "Install Roof", Options: "Roof Material - Metal, Roof Style - Shed", Price:1460.5}, {Task: "Install Roof", Options: "Roof Material - Metal, Roof Style - Gable", Price:1660}, {Task: "Install Roof", Options: "Roof Material - Composite, Roof Style - Shed", Price:1220.5} ) }, Filter( TasksCollection, With({_items:Filter(Split(Options, ","), !IsBlank(Result))}, CountIf( ForAll(_items, Trim(Result) in OptionsCollection.Value), Value ) = CountRows(_items) ) ) )

Do you think there is a way to still use Filter / With / Count to accomplish this?

Was this reply helpful? Yes No