Delegation issue: Find minimum date from sql database

(0) Share

Report

Posted on by SearchingYT

107

Hej power apps experts,

My datasource is SQL server with more than 5000 rows. I have a date column knowns as 'CreatedOn'.
I want to return the minimum date and maximum date. I am not able to avoid delegation problem.

I have tried:
Min(sql_table, CreatedOn) - delegation issue, I guess because Min function is for Number type.

First( Sort(sql_table, CreatedOn, Ascending)).CreatedOn - delegation issue because of First function. But, source of first funtion is sorted in ascending order (without delegation issues), and I am fetching the first row - Would I ever get issues here? Theoretically it seems that this should hold.

Any better ideas to work this out?

Can somebody help. Any help would be appreciated.
Thanks.

Okay, I found a work around but it looks too dramatic:
First( FirstN( Sort(sql_table, CreatedOn, Ascending), 1) ).CreatedOn

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (1)

Pstork1 68,707 Most Valuable Professional on at

Like (2)

Report

Although First() is not delegable, Sort() is. So using First( Sort(sql_table, Created on, Ascending)).Created on will sort the table by date, return the first 500 (2,000) rows, and give you the first record. That will reliably give you the earliest date even if the list exceeds the data row limit. You can then get the latest date using

First( Sort(sql_table, Created on, Descending)).Created on

Was this reply helpful? Yes No
Verified answer

timl 36,383 Super User 2025 Season 2 on at

Like (1)

Report
@SearchingYT

@Pstork1 is correct in that the following will reliably give you the earliest date, irrespective of the delegation warning.

First( Sort(sql_table, CreatedOn, Ascending)).CreatedOn

The syntax beneath will return the earliest date more efficiently because the FirstN function will return a single record, rather than a batch of 500 (2000) rows.

First( FirstN( Sort(sql_table, CreatedOn, Ascending), 1) ).CreatedOn

The First/FirstN/Sort pattern is a technqiue that I have traced/described beneath, and it offers a very efficient way to return the minimum and maximum values from a table.

http://powerappsguide.com/blog/post/how-to-return-the-last-record-from-a-table

Was this reply helpful? Yes No