Answered

Filter and Sort based on User ID and Item ID

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi.

Can I use filter and sort on two Sharepoint List by using Data Table to display the data?

For example, I have two SharePoint List which is Image 001 and Image 002 and at the data table I would like to use the data table and set a filter to show the items based on Item ID that matches with the User ID

Thank you.

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (1)

Sort by

WarrenBelz 155,495 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

@Anonymous ,
You have posted exactly the same question as this post. Which I am about to answer. Please close this one.

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @Anonymous ,

Could you please share a bit more about the Name field in your Request List? Is it a Person type column or Text type column?

Do you want to filter your Data Table records based on the Item ID (from 'Purpose of Data extraction' list) which matches with User ID (User Display Name from "Request List")?

Based on the needs that you mentioned, I have made a test on my side, please consider take a try with the following workaround:

I assume that you store the Display Name of the user within the Name field in your 'Request List', please set the Items property of the Data Table to following:

Filter( 'Purpose of data extraction', ID in Filter('Request List', Name = User().FullName).ID )

Best regards,

Was this reply helpful? Yes No
WarrenBelz 155,495 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

@Anonymous ,
I am quite happy to leave you with @v-xida-msft here.
You need to close the other post if solved on this one.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @v-xida-msft,
Thank you for your reply,

It is a Text type column

I want to filter my Data Table records based on the ID (from 'Purpose of Data extraction' list) which matches with a User ID (from "Request List")?.
In the request list, a user had submitted the request and the SharePoint auto-generate an ID for that particular user

In the Purpose of Data extraction' list, it stores all the record that was been inserted by the user and also had the same user ID.

In short, I would filter based on these two lists ID column as since they had the same ID
Thank you

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @Anonymous ,

Do you want to filter your Data Table records (connected to 'Purpose of Data extraction') based on current sign in user who has same ID in your 'Purpose of Data extraction'?

I have made a test on my side, please try the following workaround:

Set the Items property of the Data Table to following:

Filter( 'Purpose of Data extraction', TrackingID in Filter('Request List', 'Created By'.DisplayName = User().FullName).ID )

or

Filter( 'Purpose of Data extraction', TrackingID in Filter('Request List', 'Created By'.Email = User().Email).ID )

Please take a try with above solution, check if the issue is solved.

Best regards,

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @v-xida-msft,
Thank you for your reply.

I want to filter my Data Table records (connected to 'Purpose of Data extraction') based on the user's name that submitted the form who has the same ID in your 'Purpose of Data extraction

I tried the formula but is not working, it showed a delegation warning.
Thank you.

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @Anonymous ,

How many records existed in your 'Purpose of Data extraction' List? More than 2000 records?

The Delegation issue is not an error in PowerApps canvas app, it just means that you could not delegate the data process from your canvas app to your SP List itself, instead, you could only process the data locally in your app.

In default, you could process 500 records locally in your app, you could change the "Data row limit for Non-delegable queries" option to maximum value -- 2000, then you could process 2000 records locally in your app.

Please check the following article for more details about Delegation:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

Please set the OnStart property of App to following:

Set(CurrentUser, User().FullName)

Set the Items property of the Data Table to following:

Filter( 'Purpose of Data extraction', TrackingID in Filter('Request List', 'Created By'.DisplayName = CurrentUser).ID )

then re-publish & re-load your canvas app, check if the issue is solved.

Based on the needs that you mentioned, I think the formula I provided above could achieve your needs.

Best regards,

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @v-xida-msft,

Thank you for your reply,

Is there any way to remove the warning sign ?

Thank you

Was this reply helpful? Yes No
Verified answer

v-xida-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @Anonymous ,

Yeah, if the amount of your SP List records is not more than 2000, I think the collection could achieve your needs.

Please take a try with the following workaround:

Please set the OnStart property of App to following:

Set(CurrentUser, User().FullName); ClearCollect(DataExtraction, 'Purpose of Data extraction'); ClearCollect(RequestListCollection, 'Request List')

Set the Items property of the Data Table to following:

Filter( DataExtraction, TrackingID in Filter(RequestListCollection, 'Created By'.DisplayName = CurrentUser).ID )

If the amount of your SP List is more than 2000, you could consider bulk-load your SP List records into a collection, then use the collection as data source in your above Filter formula.

Please check and see if the following threads would help in your scenario:

http://rezadorrani.com/index.php/2019/09/18/powerapps-delegation-with-sharepoint-as-a-data-source-with-respect-to-all-column-types/#respond

https://powerusers.microsoft.com/t5/Building-Power-Apps/Sort-gallery-with-multiple-fields/td-p/379730

Best regards,

Was this reply helpful? Yes No