Possible to Filter Datasource A by Datasource B & Still Be Delagatable?

(0) Share

Report

Posted on by andrew5083

Hello All,

I'm relatively new to PowerApps and stuck creating a filter formula - hoping someone here can help me out.

I'm using 2 SQL Connectors to connect to data in 2 related tables. Table A (we'll call it) is the Equipment Master table. Table B contains a record of all rental contracts for a piece of EQ. The link between the tables is a one to many relationship from A to B on Stock #. There are over 6,000 contract records in total, and this is likely to increase dramatically, so I would like to avoid using collections.

My app has a filter screen that contains 6 different combobox selectors all filtering on different data from tables A and B. Once the user selects the necessary filters, they will be taken to an "Equipment List" gallery screen (based on Table A) which shows all stock #s that fit their selected filters.

The part I'm stuck on is how to incorporate fields from Table B in the filter for the Equipment List screen based on Table A data while still keeping the 'Items' function delegated for the EQ List Gallery? The closest I've gotten is the below basic example, however the "in" function is not delegated in this context for SQL Server connectors.

Filter('[dbo].[vf_AAEquipmentList]', StockNumber in Filter('[dbo].[vf_AAContractDetails]', ContractType = "RNT").StockNumber)

Filter Screen

Equipment List

Categories:

Building Power Apps

All responses (3)

Answers (1)

Sort by

Verified answer

Pstork1 69,661 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

The best workaround for this complex type of data retrieval is to build a stored procedure that takes the 6 values as parameters. Then invoke a flow to run the stored procedure with those parameters as input parameters. The results will be passed back in JSON and can be returned to the Power App and loaded into a collection for display. This offloads all the selection process to SQL so delegation is not an issue and only loads the data set you want as a local collection, not all the records.
andrew5083 88 on at

Like
a
(0)

Report
Copy link

Link copied!

Wow, thank you so much for the insanely quick reply. I will look into this and post back if it ends up resolving the issue.
andrew5083 88 on at

Like
a
(0)

Report
Copy link

Link copied!

@Pstork1thank you so much! This solution ended up working perfectly!

For reference for anyone that may stumble upon this, I used the following guide to complete this task. Instead of using the "Execute a SQL Query"** action in the Flow, I used "Execute a Stored Procedure".

**Execute a SQL Query is not supported for On-Prem SQL Servers.

https://powerapps.microsoft.com/en-us/blog/return-an-array-from-flow-to-powerapps-response-method/