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

Filter and Lookup nested within another Filter - delegation warning

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

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.

delegation.jpg

 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,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

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.

delegation.jpg

 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,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (7,732)