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

[jhanley]

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

[eka24]

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.

[jhanley]

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