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
Solved! Go to 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,
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,
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
User | Count |
---|---|
254 | |
251 | |
82 | |
45 | |
27 |
User | Count |
---|---|
351 | |
266 | |
127 | |
61 | |
58 |