Power Platform Community Forum Thread Details

I’m making a search engine with an Oracle database with approximately 300,000 records, I understand that due to delegation issues I must use this function so that it searches the entire database

SortByColumns(Distinct('DWJAMAR.TRAFICO_MALLAGRAMA';AGENCY);"Result")

The issue is that in the database I have a "date" column with the following structure: 2021-06-17 00:00:00.000

The search engine must search by specific year and month but when I try with the function:

SortByColumns(Distinct('DWJAMAR.TRAFICO_MALLAGRAMA';Left(Text(DATE; "yyyy-mm-dd");4));"Result")

It only brings me the year 2021, when in the database I have the years 2021 and 2022,

If instead I create my filters with the items: [“2021”;”2022”]

And I apply the filter function:

Filter('DWJAMAR.TRAFFIC_MALLAGRAMA';

Text(AGENCY) = Dropdown2.Selected.Value;

Text(Left(Text(DATE; "yyyy-mm-dd");4)) = Dropdown2_1.Selected.Value;

Mid(Text(DATE; "yyyy-mm-dd");6;2) = Dropdown2_2.Selected.Value)

I’m getting a delegation issue where it only shows me the first 2000 records.

Help me! Please

Categories:

Building Power Apps

Hi @Jhovanny ,

According to the Microsoft Docs (Distinct function in Power Apps - Power Apps | Microsoft Docs), Distinct can't be delegated (even though it doesn't give a warning when I try it).

SortByColumns(Distinct('DWJAMAR.TRAFICO_MALLAGRAMA';Left(Text(DATE; "yyyy-mm-dd");4));"Result")

only gives you 2021 because Distinct can't be delegated and you SortByColumns your results with the Default "Ascending" implied.

You get the first 2000 records which (I suppose) are all 2021. If you change the sort to "Descending" you would get the 2022-results first, but you still have no complete result.

One of the issues with:

Filter('DWJAMAR.TRAFFIC_MALLAGRAMA';

Text(AGENCY) = Dropdown2.Selected.Value;

Text(Left(Text(DATE; "yyyy-mm-dd");4)) = Dropdown2_1.Selected.Value;

Mid(Text(DATE; "yyyy-mm-dd");6;2) = Dropdown2_2.Selected.Value)

is that operations like Mid on the Fields (AGENCY,....) aren't delegatable.

If you want to select on year and month, try something that doesn't convert your dates to text:

Filter('DWJAMAR.TRAFFIC_MALLAGRAMA';

AGENCY = Dropdown2.Selected.Value;

DATE < DatePicker1.SelectedDate && DATE > DatePicker2.SelectedDate)

Hope this helps,

Marc

Community site session details

Delegation Oracle DB

Helpful resources

News and Announcements

Quick Links

Season of Sharing Community Challenge Winners!

Kudos to our 2025 Community Spotlight Honorees

Congratulations to the July Top 10 Community Leaders!

Leaderboard > Power Apps

Featured topics

Product updates