cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
moelhag
Microsoft
Microsoft

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

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! 

1 ACCEPTED SOLUTION

Accepted Solutions

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.

DataTableFilter.gif

 

 

 

 

 

 

 

 

 

 

 

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.

View solution in original post

8 REPLIES 8
WarrenBelz
Super User
Super User

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.

 

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!

 

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.

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!

 

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.

DataTableFilter.gif

 

 

 

 

 

 

 

 

 

 

 

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.

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

JACK_LAI_1117
Helper V
Helper V

@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 ??

@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.

 

Helpful resources

Announcements
Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Government Carousel

New forum: GCC, GCCH, DoD - Federal App Makers (FAM)

In response to the unique and evolving requirements of the United States public sector, Microsoft has created Power Apps US Government.

Users online (2,058)