Answered

Sort(Filter for Gallery

(0) Share

Report

Posted on by Community Power Pla...

I originally had this in my Gallery Items. It wouldn't pull all the data.

Filter(ForAll(Distinct(MemberCertificationandQualificationCollection,CQTitle),LookUp(Sort(Filter(MemberCertsandQualsList,(DteofCQ>=MemberProfileDatePicker1.SelectedDate)&&(DteofCQ<=MemberProfileDatePicker2.SelectedDate)&&FullNm=MemberProfileFullNmDropdown.Selected.FullNm),DteofCQ,Descending),CQTitle=Result)),!IsBlank(CQTitle))

I now have this; which is pulling the data the above was not.

Sort(Filter(MemberCertsandQualsList, FullNm=MemberProfileFullNmDropdown.Selected.FullNm), CQTitle, Ascending)

I need to work the DatePicker condition(s) into the above. Currently user have the option of determining a date range or allowing the Gallery to filter all from 1/1/1980 to current date.

LookUp(Sort(Filter(MemberCertsandQualsList,(DteofCQ>=MemberProfileDatePicker1.SelectedDate)&&(DteofCQ<=MemberProfileDatePicker2.SelectedDate)&&FullNm=MemberProfileFullNmDropdown.Selected.FullNm),DteofCQ,Descending),CQTitle=Result)),!IsBlank(CQTitle))

Categories:

Building Power Apps

I have the same question (0)

All responses (21)

Answers (2)

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report

@Anonymous

You were actually closer on your first formula. What was the problem you had with that?

According to what you wrote, your formula would be returning a single column table with a Value column that would contain a record for the result of your LookUp.

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

Like (0)

Report

The first condition didn't return on the data. The new condition does return all the data, but now it is showing me everything, when previously the Gallery only displayed the most recent docs.

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

Like (0)

Report
@Anonymous
Can you describe what you mean by didn't return on the data?

The way you wrote your formula:

For all of the Distinct values of your CQTitle column you are looking up the most recent DteofCQ where the DteofCQ is between the start and end dates chosen in the two datepickers and with a FullNm based on the dropdown.

You are finally filtering all of this for CQTitle not being blank - which will actually not do anything in the context of your ForAll return values.

You might consider this formula to replace that:

ForAll( Filter(Distinct(MemberCertificationandQualificationCollection, CQTitle), !IsBlank(CQTitle)), First( Sort( Filter( MemberCertsandQualsList, CQTitle=Result && (DteofCQ>=MemberProfileDatePicker1.SelectedDate) && (DteofCQ<=MemberProfileDatePicker2.SelectedDate) && FullNm=MemberProfileFullNmDropdown.Selected.FullNm ), DteofCQ,Descending ) ) )

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

Like (0)

Report

The first example, as written, returns the desired content; specifically the Portable Fire Extinguisher Maintenance, which I uploaded last Friday.

However, it also returns ALL items associated with the name in the Dropdown; not desired. The original condition (at the bottom) only returned the MOST RECENT docs, as is the desire. No duplicates from year over year.

The original condition (below) does not return the Portable Fire Extinguisher Maintenance item; but, it does ONLY show the most recent - no duplicates from year over year.

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

Like (0)

Report

@Anonymous

How many records are in your list at this point and what is the Record Limit set to in your app?

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

Like (0)

Report

4800+ records; Row retrieval limit is set to 200; FullNm, CQTitle, UnqID, and other columns have been Indexed in the SP list.

Sure would love to know why the data returns with one condition and not the other; and whether I can simply add the ascending, most frequent only/no duplicates, and datepicker range choice to the working condition?

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report
@Anonymous

Yep...this is why I asked. You are using the Distinct function which is NOT delegable. When you mentioned "added recently" that was the clue.

So, your formulas with the Distinct function are not giving you the latest results. The formulas without it are delegable and are returning the proper results.

The first thing I would start to consider (besides the obvious) is how to pre-filter. So, I am not sure of your order of precedence, but you might consider this formula:

ForAll( Filter( Distinct( Filter(MemberCertificationandQualificationCollection, FullNm=MemberProfileFullNmDropdown.Selected.FullNm ), CQTitle ), !IsBlank(CQTitle) ), First( Sort( Filter( MemberCertsandQualsList, CQTitle=Result && (DteofCQ>=MemberProfileDatePicker1.SelectedDate) && (DteofCQ<=MemberProfileDatePicker2.SelectedDate) && FullNm=MemberProfileFullNmDropdown.Selected.FullNm ), DteofCQ,Descending ) ) )

That might at least pre-filter the Distinct to under 2000 rows...but maybe not depending on your data.

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

Like (0)

Report

Not received very well...

I changed it to this, changing First to FirstN (based on a system error message stating First was not allowed here); the delegation errors in the condition went away, but...

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

Like (0)

Report
@Anonymous

Sorry...formula should have been:

ForAll( Filter( Distinct( Filter(MemberCertificationandQualificationCollection, FullNm=MemberProfileFullNmDropdown.Selected.FullNm ), CQTitle ), !IsBlank(Result) ), First( Sort( Filter( MemberCertsandQualsList, CQTitle=Result && (DteofCQ>=MemberProfileDatePicker1.SelectedDate) && (DteofCQ<=MemberProfileDatePicker2.SelectedDate) && FullNm=MemberProfileFullNmDropdown.Selected.FullNm ), DteofCQ,Descending ) ) )

Although this will show a delegation warning, it can be ignored (and we can take it out if annoying) because we are really passing the results of the Filtered and sorted list (which will automatically only be 2000 records max) to the First function, so at that stage it is not relevant.

The real catch is to get past the Distinct in the first part of the formula. I accidentally forgot to change the IsBlank to check the Result column in the previous post.

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

Like (1)

Report

Yep!

Had to update the data source 'Collection' at the top to 'List', so that it match the data source 'List' in the sort.

I still have the delegation warning, as you cautioned, but all the data seems to be coming. 👍

Was this reply helpful? Yes No