01-05-2024 03:29 AM - last edited 01-08-2024 01:21 AM
I’m happy to introduce a solution for delegation with the SharePoint data source. You can download the solution and follow the setup instructions here.
What is included:
How it works:
For these reasons, the way you retrieve data into Power Apps will be different! Don't worry you can continue to write data the same way (Patch and SubmitForm) and you only need the OData style for the large lists in your projects. Filter should still be used with other lists.
Power Apps converts these functions to OData queries but it, unfortunately, does not take advantage of its full power. The idea is to generate it ourselves.
So let's look at some examples of some common patterns and how they can be re-interpreted to a dynamic OData query.
Let's start with some delegable queries:
Note
All column references are the internal names
Normal variant
TicketingSystem_ServiceTickets
Solution variant
With(
{
top: 100, //The number of items to retrieve. Maximum of 5000
ListName: "TicketingSystem_ServiceTickets" //The original list name. In case the list was renamed, get the name from the list’s URL
},
With(
{query: $"?$top={top}"}, //Generated OData query includes the $top parameter
UpdateContext(
{
/*4 parameters are required for the flow:
1. The name of the list
2. The generated OData query
3. A Boolean to check if certain filters are working with multiple columns. This will be covered later
4. A Boolean to determine if all the results may be incomplete even after pagination. This will be covered later.
The response is saved to the _res context variable.
*/
_res: SearchFlow.Run(
ListName,
query,
false,
false
)
}
);
)
);
Normal Variant
Filter(TicketingSystem_ServiceTickets, ServiceType.Value = "Problem")
Solution Variant
With(
{
top: 100,
ListName: "TicketingSystem_ServiceTickets",
basicFilter:"ServiceType eq 'Problem'" //OData filter query on a choice field. Note the single quotes around “Problem”
},
With(
{query: $"?$top={top}&$filter={basicFilter}"}, //Generated OData query now includes the $filter parameter for filters
UpdateContext(
{
_res: SearchFlow.Run(
ListName,
query,
false,
false
)
}
);
)
);
Normal Variant
Filter(TicketingSystem_ServiceTickets, ServiceType.Value = "Problem", ServiceCategory.Value = "Networking")
Solution Variant
With(
{
top: 100,
ListName: "TicketingSystem_ServiceTickets",
multipleConditions:"ServiceType eq 'Problem' and ServiceCategory eq 'Networking'" //OData filter conditions on 2 choice fields separated by the logical “and” operator
},
With(
{query: $"?$top={top}&$filter={multipleConditions}"},
UpdateContext(
{
_res: SearchFlow.Run(
ListName,
query,
false,
false
)
}
);
)
);