cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Delegation error using Filter(..., ... IN ...) - SQL

Hi,

 

I have a local collection in my app with a list of values which I would like to use as a filter against a column within a SQL query. Let's refer to the collection as tblLocal, the query as qrySQL and the respective table's columns as colLocal and colSQL.

 

I'm using the formula: Filter(qrySql, colSQL in tblLocal.colLocal), which is then returning a delegation warning and only one record when a few hundred is expected to be returned.

 

I consulted the PowerApps documentation, and would have thought Filter(..., ... IN ...) would have been delegated since it is listed as a delegable function. Changing this to Filter(..., ... = xyz) encourages delegation, but obviously does not return the set of records that I need.

 

Does anybody know whether I'm misinterpreting the guidance and Filter(..., ... IN ...) isn't actually delegable? Has anybody found any ways around this issue?

 

Thanks

Joe

2 REPLIES 2
Super User III
Super User III

Can you show your collection.

With your formula:

 Filter(qrySql, colSQL in tblLocal.colLocal), 

 

What is the relationship between colSQL and colLocal

 

Have you tried renaming the column in the collection to match the column name for SQL colSQL

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Frequent Visitor

Hi @eka24,

 

Thanks for your response!

 

To provide a bit more context, the gallery is presenting a ‘job board’ (i.e. list of jobs by weekday). The collection runs on screen load and collects the dates of the current week in ‘tblLocal’.

 

On the screen, there is a combo box that allows the user to change the view of the gallery based on the date, i.e.: start date, due date or upcoming deadline.

 

qrySQL = the list of jobs on SQL

colSQL = depending on the selection in the combo box; the corresponding column on SQL relating to the: start date, due date or upcoming deadline date

tblLocal = table of dates in current week

colLocal = list of week dates

 

I haven’t tried renaming colLocal to match colSQL, as colSQL will eventually switch depending on the combo box selection. Is it worth me creating three versions of colLocal within tblLocal so that the column name matches that exactly of the colSQL options?

 

Hope this makes sense!

 

Thanks

Joe

 

Edit:  I appreciate dates can be an awkward format to align between PowerApps and SQL, so the format of the dates in both tblSQL and tblLocal is an integer: YYYYMMDD

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (35,988)