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 III
Super User III

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.

View solution in original post

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

JACK_LAI_1117
Resolver I
Resolver I

@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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Carousel April Dunnam Updated 768x460.jpg

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors
Users online (3,158)