Filtering most recent records in modern table control

(1) Share

Report

Posted on by Dej

Hi,

I have a dataverse table Payroll which contains records of all months for two companies.(monthname, company, employee, paydetails) . I want to display only the most of recent records of a particular company in modern table control,

If I use MAX(modifiedOn) together with filter function, I get delegation warning. Please help!

Thank you for any assistance!

All responses (3)

Answers (0)

Dej 6 on at

Like (0)

Report

Filtering most recent records in modern table control

Thank you for time @Mark, @ANB.

@Mark, Now FirstN selects only 1 record. I need to show the payroll for all employees in the selected company.

@ANB, yes i tried with MAX, it showed delegation warning. In the example link it shows the fix for date range, but i need max(date) , is there any workaround for it ?
Suggested answer

Mark Nanneman 831 on at

Like (0)

Report

Filtering most recent records in modern table control

One way to do this, if you want to be able to select a company and see the most recent records in your table control, is to use a DropDown or ComboBox to select the company you want.  Then you just need to filter your results by that company and sort it by "modifiedon".

If you wanted to get only the most recent payroll record for a select company, the "Items" expression for your table would look like this:

FirstN(
  SortByColumns(

  Filter('Your Payroll Table',

  Company = DropdownCanvas1.Selected Or IsBlank(DropdownCanvas1.Selected)

),

"modifiedon",

  SortOrder.Descending),

  1

)

If you wanted to see a list of only the most recent payroll for each company, you could create a gallery for your companies. Each company is in the collection that goes in the "items" of your gallery. Then for each gallery item you can insert a table control that filters for the FirstN() of all Payroll records for the current company.

E.g. something like:

FirstN(Sort(Filter('Your Payroll Table',Company = ThisItem.Value),'Modified On',SortOrder.Descending),1)

Here I have a gallery of US States in Dataverse, and for each State I've included a Table Control showing the top 3 cities by population for that state.

My items expression for each table in the gallery is:

FirstN(Sort(Filter(Cities,'City Name' <> Blank() And State.State = ThisItem.State),Population,SortOrder.Descending),3)
Suggested answer

ANB 7,049 on at

Like (0)

Report

Filtering most recent records in modern table control

Hi,

You must be aware that Dataverse DateTime column is not delegable in PowerApps.

Please check Power Apps Filter Date Range Delegation Workaround

Please click Does this answer your question 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 a Like.

Thanks,
ANB