Filter and Lookup nested within another Filter - delegation warning

[claz]

Hello, I've been stuck on this one for a while now and any help would be greatly appreciated.

So in my application (a canvas app writing and reading from an Azure SQL database), there is a table called Treaty, and a table called Treaty_Contract.
A Treaty can have multiple Treaty Contracts, so Treaty Contract has a foreign key column called Treaty.

I want to be able to select a value from a combo box, then Filter my list of Treaties to only show Treaties where at least one of their related Treaty Contracts contain the selected value from the combo box.

What I have now works, but it is non delegable:

Filter('[dbo].[treaty]',
    !IsBlank(
        LookUp(
            Filter('[dbo].[treaty_contract]',ChoiceField = ComboBox.Selected.ID).Treaty,
            Treaty = ID
        )
    )
)

Is it possible for this to be delegated?
Thanks

[v-yutliu-msft · Accepted solution]

Hi @claz ,

The key point about your original formula is IsBlank() function, which is not delegate.

Could you tell me how many records in your two sql tables?

If they are both less than 2000 records, you just need to change the delegation limit to 2000.

 In this situation, even if you have delegation warning, your app will still work good.

If your sql tables are larger than 2000 records, you have two solutions:

1)modify your formula to only use delegate functions

delegate function for sql:

Operation/Function	Number	Text	Boolean	DateTime	Guid
Filter	Yes	Yes	Yes	No [1]	Yes
Sort	Yes	Yes	Yes	Yes	-
SortByColumns	Yes	Yes	Yes	Yes	-
Search	No	Yes	No	No	-
Lookup	Yes	Yes	Yes	Yes	Yes
=, <>	Yes	Yes	Yes	No	Yes
<, <=, >, >=	Yes	No	No	No	-
in (substring)	-	Yes [2]	-	-	-
+	Yes	-	-	No	-
-	Yes	-	-	No	-
*	Yes	-	-	No	-
/	Yes	-	-	No	-
StartsWith	-	Yes [3]	-	-	-
EndsWith	-	Yes [4]	-	-	-
IsBlank	No [5]	No [5]	No [5]	No [5]	No [5]
Len (length)	-	Yes [6]	-	-	-
Sum	Yes	-	-	-	-
Average	Yes	-	-	-	-
Min	Yes	-	-	No	-
Max	Yes	-	-	No	-

2)use collection

If you could not avoid using IsBlank() function or "in", I think this solution will be better.

You need to save your sql tables in collections firstly.

set the app's OnStart:

ClearCollect(
collection1,Filter('[dbo].[treaty]',ID<=2000));
ClearCollect(
collection2,Filter('[dbo].[treaty]',ID>2000,ID<=4000));
ClearCollect(
collection3,Filter('[dbo].[treaty]',ID>4000,ID<=6000));....
//save '[dbo].[treaty]' to multiple collections
ClearCollect(
collection11,Filter('[dbo].[treaty_contract]',ID<=2000));
ClearCollect(
collection12,Filter('[dbo].[treaty_contract]',ID>2000,ID<=4000));
ClearCollect(
collection13,Filter('[dbo].[treaty_contract]',ID>4000,ID<=6000));....
//save '[dbo].[treaty_contract]' to multiple collections

 Then evaluate for these collections one by one.

In collection, there's no delegation limit.

For example:

Filter(collection1,
    !IsBlank(
        LookUp(
            Filter(collection11,ChoiceField = ComboBox.Selected.ID).Treaty,
            Treaty = ID
        )
    )
)

Best regards,

[claz]

Thanks a lot for your reply @v-yutliu-msft .

It looks like I'll have to go with the collection approach.
Unfortunately the IDs in the SQL database are GUIDs, so I don't have the option of sorting them like that to get each block of 2000.
Do you have any recommendation for another way to achieve that?

Thanks