Get around delegation warning

[caf]

Hi all!

This is my first psot, so I hope to respect all the rules and to post it in the correct group 😀

I kindly ask your help to check if it is possible to manage and workaround a delegation warning.

The scenario is a gallery that filter an SQL table (with more than 3000 records, and they will grow up in future); each user is associated to a group of Location (an array of ID in "UserLocationTable" collection) and there is a filter to select a single location from the collection

If the variable associated to the filter is empty (no location selected), the gallery should show the data associated to all the location in the collection UserLocationTable, and in this case I have some problem related to the delegation warning

Anyone can help me?

Thank for your time and your help

[NativeNass_]

First of all, a collection only stores 2000 records, there are workarounds, but keep that in mind.

The problem could be that you already have more than 2000 records and you only load 2000 in your collection.

If this is not the case, maybe consider using the With function?

With({wUserLocation: UserLocationTable.ID_Location},

Start of your formula, and replace UserLocationTable.ID_Location with wUserLocation:

SortByColumns(Search(Filter(PU_Key = PUAssignedID, Cluster_Key = ClusterAssignedID, Country_Key = CountryAssignedID, And(GROUP_ID<> 1, GROUP_ID <> 2, GROUP_ID, 3), Location_IDref in wUserLocation, IsBlank(LocationAssignedID) || Location_IDref = LocationAssignedID, ...)

I use the With function a lot when working around delegation issues, I don't know if the exact same as above will help you in achieving your goal, but it might help 🙂

Kind regards

[caf]

Hi @NativeNass_ 

First of all thank you for your solution 😀

I have tried it but I have the same warning

Probably I was not clear in first explanation, but the collection I have (UserLocationTable) has inside less item than 2000, but the SQL table yes.

To make a real example:

SQL Table: a lot of records with ID_location 1,2,3,4,5,6...etc.

Collection UserLocationTable: variable numbers of ID, for example {1,2,3,4}

So my problem is how can I filter the SQL table for the ID_Location in the collection, without delegation error?

Take in mind that the SQL table can have more items associated to the same ID_Location

Thank you!

[NativeNass_]

Could you post a complete screenshot of your formula, including the delegation error?

[caf]

Hi @NativeNass_ 

Here you have the entire formula, with the delegation error:

As you can see, the warning is on "in" function, because I am filtering the sql table for all the item I have in the collection

Thank you

[NativeNass_]

@caf 

It's difficult for me to test it out since I don't have any SQL datasources to test it out,

Maybe try to collect only the ID_Location column, instead of referencing it through the 'UserLocationTable' table?

What could also work is store the Datasource into the With() funtion as well,

Going like:

With({ wDatasource: Datasource, wUserLocation: UserlocationTable.ID_Location}, FORMULA HERE)

And replace both Location_IDref with wDatasource.IDref and UserLocationTable.ID_LOCATION with wUserLocation.

Also filter on wDatasource instead of the Datasource directly.

I really think your solution lies here, i've overcome quite some nasty delegation issues in the past using With().

Maybe looking at some YT resources (Shane Young, Reza Dorrani, ...) might help in better understanding how to use With()

Hope it helps..

Kind regards