Filter a Datatable with 2 Conditions and an Variable

(0) Share

Report

Posted on by CZ-03061553-0

Hello,

I try to filter a data table by two criteria and sort it after that. Basis of the table is a sharepoint list.

The following formula works:

Sort(Filter('Zeitdaten Mandanten';Bearbeiter = 'Office365-Benutzer'.MyProfile().Mail;'Datum und Uhrzeit'= DateValue1.SelectedDate);'Datum und Uhrzeit';SortOrder.Ascending)

The problem is delegation. Calling the Office365 user function makes it impossible as far as I have understood.

So I tried to swap the Ofice365 Email into a global variable, i call this function before i open the screen with the DataTable.

Set(VariableMail;'Office365-Benutzer'.MyProfile().Mail)

Unfortunately the following formula do not work :

Sort(Filter('Zeitdaten Mandanten';Bearbeiter= VariableMail;'Datum und Uhrzeit'= DatePicker1.SelectedDate);'Datum und Uhrzeit';SortOrder.Ascending)

The strange thing for me is, the first part:

Filter('Zeitdaten Mandanten';Bearbeiter=VariableMail)

works. But not in combination with the second condition (which i didn´t change). The second condition alone:

Filter('Zeitdaten Mandanten';'Datum und Uhrzeit'= DateValue1.SelectedDate)

Doesn't work either. This is where my understanding ends. Why does it work in combination with Office365 users, but not with the variable or alone?

Thanks a lot!

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

mdevaney 29,991 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@Spuff
I believe the delegation issue is caused by this FILTER criteria
'Datum und Uhrzeit'= DateValue1.SelectedDate

Date columns are currently not delegable for SharePoint. See the table below for a completing listing of delegation scenarios.

Link to original MS Blog post
https://powerapps.microsoft.com/en-us/blog/sharepoint-delegation-improvements/

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
yashag2255 24,769 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
Hey @Spuff

What is the total number of records in your SP list? If this is less than 2000, you can update a setting below:
App Settings -> Advanced Settings -> Data Row Limit for Non-Delegable queries -> Set this to 2000.

Also, can you try to update the expression as:

Sort(Filter(Filter('Zeitdaten Mandanten';Bearbeiter= VariableMail);'Datum und Uhrzeit'= DatePicker1.SelectedDate);'Datum und Uhrzeit';Ascending)

If you have more than 2000 records, you can follow my blog where I have created an extra auto calculated column in my SP list and used a collection to get more than 2000 records in the local collection. You can refer: https://www.appliedis.com/how-to-use-galleries-in-powerapps/

You can create a collection in a similar way I created the OrderCollection.

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@yashag2255
FYI, the URL in your link is slightly malformed. Just wanted to notify you so it can be corrected.

Was this reply helpful? Yes No
Verified answer

yashag2255 24,769 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Hey @mdevaney

Thank you so much for the note. Here is the corrected URL (might have made some space mistake while adding the URL to the post so apologies for that)

https://www.appliedis.com/how-to-use-galleries-in-powerapps/

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Was this reply helpful? Yes No