Answered

Filtering from a collection by dates

(0) Share

Report

Posted on by MG-27051438-0

Hello, I have an app that loads the main gallery based on the following filters:

SortByColumns(
 Sort(
 Search(
 Filter(
 'Partner Details',
 IsBlank(CmbUseCase.SelectedItems.Value) || IsEmpty(CmbUseCase.SelectedItems) || Concat(
 CmbUseCase.SelectedItems,
 Value & ","
 ) in Concat(
 'Use Case',
 Value & ","
 ),
 IsBlank(CmbPartnerType.SelectedItems) || IsEmpty(CmbPartnerType.SelectedItems) || 'Partner Type' in CmbPartnerType.SelectedItems,
 IsBlank(CmbRiskProd.SelectedItems.Value) || IsEmpty(CmbRiskProd.SelectedItems) || Concat(
 CmbRiskProd.SelectedItems,
 Value & ","
 ) in Concat(
 'WC Product',
 Value & ","
 ),
 IsBlank(DPFrom.SelectedDate) || IsBlank(DPTo.SelectedDate) || Created >= DPFrom.SelectedDate && Created <= DPTo.SelectedDate
 ),
 SearchBox.Text,
 "Title"
 ),
 "ID",
 SortOrder.Ascending
 ),
 "ID",
 SortOrder.Ascending
)

This works, well; however, instead of filtering by Created date, I want to filter by Latest Update. The data in this field is populated by using this formula:

Text(DateTimeValue(LookUp(colLastUpPart, Partners = ThisItem.Title).'Latest Update'), "mm/dd/yyyy")

Since this information is coming from a collection, the column does not exist in the Sharepoint list; therefore, I cannot get the filter to recognize the values to filter properly.

How can I make the gallery filter the dates based on the values in the Latest Update? Could anyone please assist?

Thanks in advance.

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

Verified answer

WarrenBelz 156,100 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Monikk2711 ,

I have not looked at the rest of the code except you do not need the double sort - you need AddColumns as below

Sort( Search( Filter( AddColumns( 'Partner Details', "Latest", DateValue( LookUp( colLastUpPart, Partners = Title ).'Latest Update' ), IsBlank(CmbUseCase.SelectedItems.Value) || IsEmpty(CmbUseCase.SelectedItems) || Concat( CmbUseCase.SelectedItems, Value & "," ) in Concat( 'Use Case', Value & "," ), IsBlank(CmbPartnerType.SelectedItems) || IsEmpty(CmbPartnerType.SelectedItems) || 'Partner Type' in CmbPartnerType.SelectedItems, IsBlank(CmbRiskProd.SelectedItems.Value) || IsEmpty(CmbRiskProd.SelectedItems) || Concat( CmbRiskProd.SelectedItems, Value & "," ) in Concat( 'WC Product', Value & "," ), IsBlank(DPFrom.SelectedDate) || IsBlank(DPTo.SelectedDate) || Latest >= DPFrom.SelectedDate && Katest <= DPTo.SelectedDate ), SearchBox.Text, "Title" ), "ID", SortOrder.Ascending )

However the other items I was wondering about were your two many-to-many filters as using Concat on them both would require both the full content of the first to be in the second. I am assuming here that 'Latest Update' is a Text field (if a Date field, you do not need DateValue.)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
MG-27051438-0 91 on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz thank you! I will fix the double sorting issue. As for the 'Latest Update' field, it is a text field. I converted it into date thinking this could be the reason why it was not being recognized, but unfortunately, it didn't work. It would be so much easier to just add this field as a column in Sharepoint, but I am unable to do it. My list needs to get the values that are alike and retrieve the latest update for each value, I tried adding a calculated column, but for some reason these are crashing as I create them, so I am unable to do that and I need to enable that filter.

Was this reply helpful? Yes No
MG-27051438-0 91 on at

Like
a
(0)

Report
Copy link

Link copied!

I forgot to mention that the Latest Update field does not come from the same list as the rest of the fields. This is why it gets more complicated to extract.

Was this reply helpful? Yes No
MG-27051438-0 91 on at

Like
a
(0)

Report
Copy link

Link copied!

So, I made the changes you suggested and it worked! Thanks a lot!

Was this reply helpful? Yes No