cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
claz
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
v-yutliu-msft
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
v-yutliu-msft
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

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

PA Community Call

Power Apps Community Call

Next call is happening on April 21st at 8a PST.

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors
Users online (38,133)