How to deal with delegation when filtering from collection

[gadswebTwo]

Hello,

I'm connected to a SQL database.

I have a collection, SiteIds, which contains integer.

Now I want to filter my table '[dbo].[SitesByUser]' on the Site Id's.

Of course I have a delegation warning when I filter like that :

Filter('[dbo].[SitesByUser]';SiteId in SiteIds)

Is there a way to avoid this warning?

I don't want to place [SitesByUser] in a collection (it's the same problem, ClearCollect has the limitation) and I don't want to use a ForAll which would be ok with few items but not on a large table.

Thanks.

[eka24]

Try

Filter('[dbo].[SitesByUser]';SiteId = SiteIds)

If it doesn't work,

show the formula for the Collection

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

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.

[v-yutliu-msft]

Hi @gadswebTwo ,

Could you tell me:

1)the structure of collection?

2)Is this  collection named SiteIds?

3)Is SiteId a field in '[dbo].[SitesByUser]'?

4)Does  the field in the collection and SiteId the same data type?

5)the feather that you want in details?

Firstly, please notice that "in" only delegate for text type for sql server.

I assume that you want to filter items of sql table that has the same value in collection.

If so, I'm afraid you could only use "in" to compare a value with a table.

Since you do not want to place [SitesByUser] in a collection, I suggest you change the data type of SiteId and the field in SiteIds .

Change them both to text type.

Then "in" will be delegate.

Try this formula:

Filter('[dbo].[SitesByUser]';SiteId in SiteIds.field)

//pleace replace field with the fieldname in SiteIds collection

Here's a doc about delegate functions for sql server for your reference:

https://docs.microsoft.com/en-us/connectors/sql/

Best regards,

[gadswebTwo]

Hi,

SiteIds is a collection, "=" cannot be used-->invalid argument

[gadswebTwo]

Hi Phoebe

1-field name is "Id"

2-yes, SitesIds is the collection name

3-yes, SiteId is the name of the field in dbo.Users SQL table

4-yes, both are integer

5-SitesByUsers is a junction table, which represents SiteId for a UserId. It's a many-to-many relationship (I can have several SiteId for a UserId and I can have several UserId for a UserId.

What I want to achieve is collecting all UserId for which the SiteId is the collection "SitesIds"-->equivalent of

select UserId from dbo.SitesByUsers where SiteId in 'SitesIds'

where SitesIds would be for exemple (1,2,3) in SQL.

I cannot convert to text as Id is an identity column, auto-incremented.

I don't want to collect dbo.SitesByUsers because it's a bad idea as max 2000 items will be collected-->it doesn't solve my issue.

Thanks.

[v-yutliu-msft]

Hi @gadswebTwo ,

I'm afraid in your issue, you really need use "in".

The formula that you should use:

Filter('[dbo].[SitesByUser]';SiteId in SiteIds.Id)

However, "in" is not delegate , which will lead to get wrong result.

To solve this problem, you could only save your data to collection, then use "in" for your collection.

That's the only solution.

Set the app's OnStart:

ClearCollect(collection1;'[dbo].[SitesByUser]')

 the filter function:

Filter(collection1;SiteId in SiteIds.Id)

Best regards,

[gadswebTwo]

Hi Phoebe,

As I mentioned, this is not an acceptable solution.

If I collect, my collection will only contains 2000 items max. A filter on this collection will return incomplete results.

It's so strange that PowerApps isn't able to do that 😞

For instance, my workaround but so much complicate for an easy operation is to execute the sql query in flow then return the table in PowerApps.

Thanks 

[eka24]

Is the Advanced settings in PowerApps already set to 2000?

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

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.

[gadswebTwo]

No, not yet, but why?

500 or 2000 it's the same, you can't query the entire table if you have more than 2000 entities...

[eka24]

I thought you said you have only 2000 records?

"My collection will only contains 2000 items max.:

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

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.