HELP! Anyone know how to filter a collection but also show the first record value that matches a filter?

(1) Share

Report

Posted on by arpost

466

Greetings, all! Really hope someone can help with this as I've been going round in circles.

I need to create a collection that filters a collection to show specific records but also shows the longest text value for items with the same ID in the original collection. It's probably easier with an example.

Here's the collection I'm starting with named Data:

Item	flagField	Position	Length
Category	1	1	8
ABC	0	1	3
DEF Corporation	0	1	15
Subcategory	1	2	11
HI	0	2	2

Here's the new collection I want to create called TableFields:

Field	Position	MaxLengthText
Category	1	DEF Corporation
Subcategory	2	Subcategory

I currently get close to this by doing the following:

 ClearCollect(
 TableHeaders,
 Filter(
 Data,
 flagField = 1
));

I thought I could get the record with the greatest length this way but without success:

 ClearCollect(
 TableFields,
 AddColumns(Filter(
 Data,
 flagField= 1
 ),"MaxLengthRecord",FirstN(Sort(Filter(Data, flagField>0 && Position=Position),"Length",Descending),1)
 ));

Anyone have any ideas?

Categories:

Building Power Apps

All responses (7)

Answers (0)

Sort by

AJ_Z 3,711 Super User 2024 Season 1 on at

Like
a
(2)

Report
Copy link

Link copied!

put this in a button and add a gallery with the datasource TableFields.
:
Clear(TableFields);ClearCollect(TableHeaders,Filter(
AddColumns(Data,"MaxLengthText",""),
flagField = 1
)); ForAll(TableHeaders As TfTest, Collect(TableFields,{MaxLengthText:First(Sort(Filter(Data,Position = TfTest.Position),Length,Descending)).Item,Field:TfTest.Item,Position:TfTest.Position}))
AJ_Z 3,711 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

Let me know what happens when you do that
AJ_Z 3,711 Super User 2024 Season 1 on at

Like
a
(2)

Report
Copy link

Link copied!

I was able to mimic your case and create a solution this way
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
@arpost

Using your provided data, the following formula in a Gallery Items property would give you what you want:

ForAll( GroupBy(Data, "Position", "_data") As _items, {Field: First(_items._data).Item, Postion: _items.Position, MaxLengthText: LookUp(_items._data, Length= Max(_items._data, Length), Item) } )

If there is a specific reason you need an in-memory database Collection, then your formula would be:

ClearCollect(TableFields, ForAll( GroupBy(Data, "Position", "_data") As _items, {Field: First(_items._data).Item, Postion: _items.Position, MaxLengthText: LookUp(_items._data, Length= Max(_items._data, Length), Item) } ) )

Keep in mind that ForAll is NOT a ForLoop development function, it is a function that returns a Table of records based on your parameters. The above provides the three columns you specified in the data sample.

I hope this is helpful for you.
arpost 466 on at

Like
a
(2)

Report
Copy link

Link copied!

Thanks for the replies, @AJ_Z and @RandyHayes. I'm thinking I'll still need to capture this in a collection as I need to reference the collected data in other ways in my app. @RandyHayes, I'll give the second technique you shared a try when I have a chance.
AJ_Z 3,711 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

More than happy to help did my solution help?
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

@arpost

If you are using the formula to populate a Gallery...then you already have all the values for anywhere in your app. You can reference the Gallery.AllItems property from wherever you want and it will have the work already done for you.