How to filter a Data table dependent on if a column in a database matches a column in a sharepoint list?

(0) Share

Report

Posted on by moelhag Microsoft Employee

So my powerapps has three pages:

1) Homepage

2) Intake

3) Assign

On the intake page, the activities are pulled in from a database (reflected on a Datatable). They are assessed in my Powerapp and i need only the activities assessed to filter into a data table in my 'Assign' page for assignment.

The intake page uses sql to pull in relevant activities and details to the Data table, those activities are assessed within the powerapp and submitted to a sharepoint list via a editable form. The form contains the activity ID (the column name is called 'ID' in SP). I only want the Assign page data table to show activities that have been assessed in the SP list (but with all the same details from the database.)....is there a way to do some type of comparison of the activity ID in the database to the activity ID in the SP list and display activity details only when they match? Maybe via a lookup function or collection?

thanks!

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (1)

Sort by

WarrenBelz 155,489 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @moelhag ,
Making the assumption that you have the Activity ID somewhere on the Activity form in a control called RefID, the Filter on a gallery based on the SharePoint list called Assign would be
Filter( Assign, ID = Value(RefID.Text) )
There would be other ways of getting this depending on how the Activity record was selected.

Please click Accept as solution 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 Thumbs Up.

Was this reply helpful? Yes No
moelhag Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hey @WarrenBelz ,

This seems to either not populate or throw an incompatible argument error.

To further clarify...

The DataTable is linked to a the sql database, not the SP list. The two columns im trying to compare look something like this:

Database:
Title: RefID:
4527
5736
6529
9857
5724
(plus 500 more records)

SP List:
Title: ID:
5736
9857
3546
4527
(4 records total)

Im starting to think powerapps doesn't have the capability to read entire columns for matches to a SP list column and only display records where a match is found.

Any additional suggestions or ideas on how i can make this work?

Thank you for the help!

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

Like (0)

Report
Copy link

Link copied!

Hi @moelhag ,
A couple of things I need to clarify now I know you are looking for multiple matches.
When you say the records are pulled in, how are these presented (in a gallery?). If so, what is name and Items property of the Gallery?
Do you have less than 2000 items in your SQL table? This one may not be important depending on SQL delegation.

Was this reply helpful? Yes No
moelhag Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hey no worries!

The data is being presented in a data table. Called DataTable2_2

I currently have the row limit for non-delegable tables set to 1400.

I hope this helps!

Was this reply helpful? Yes No
Verified answer

WarrenBelz 155,489 Most Valuable Professional on at

Like (2)

Report
Copy link

Link copied!
OK @moelhag ,
The thing I did not ask was how you were selecting the SharePoint items. The example below is from a multi-choice Combo Box with the Items being the choices you would have in the SharePoint reference column. I have used a Device Name, but the principle is exactly the same. The Items property of the data table is.
Filter( YourDBTableName, ReferenceFieldName in ComboBoxName.SelectedItems )
If you have delegation issues with the In filter, you can collect the DB data and filter as above on that.

Please click Accept as solution 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 Thumbs Up.

Was this reply helpful? Yes No
moelhag Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

That logic worked great. Completely Solved my problem. Thank you @WarrenBelz !

Was this reply helpful? Yes No
JACK_LAI_1117 267 on at

Like (0)

Report
Copy link

Link copied!

@WarrenBelz

I know "in " function ,but if I use it ,the datatable will show "delegation warning " !!!
It is very confuse and trouble me , because my company has more than 4000 rows data , how can I deal with it ??

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

Like (0)

Report
Copy link

Link copied!

@JACK_LAI_1117 ,You are correct that the in function is not Delegable. I have done a blog on Delegation with some collection options that may be useful to you.

Was this reply helpful? Yes No