Unanswered

Handling Delegation in Formula

(0) Share

Report

Posted on by ruihaolrh

473

My formula below filters Desks that are available for by looking up to an associated entity 'Reservation Details' , if it has already been booked for a selected date range.

Filter(
 Filter(
 'Bookable Desk',
 Area.'Area (hsl_areaid)' = Area_Ddl.Selected.'Area (hsl_areaid)' && Facility.Facility = Building_Ddl.Selected.Facility && !IsBlank(XCoor) && !IsBlank(YCoor)
 ) As AllDesks,
 !(AllDesks.Name in ForAll(
 Filter(
 'Reservation Details',
 Date >= FromDate_Dp.SelectedDate,
 Date <= ToDate_Dp.SelectedDate && ('Booking Status' = 'Booking Status (Reservation Details)'.Blocked || 'Booking Status' = 'Booking Status (Reservation Details)'.Booked)
 ),
 'Reserved Desk'.Name
 ).Value) && !(AllDesks.Name in SelectedSeatBookedBySomeone.ItemName)
)

I encountered a delegation issue today as my 'Reservation Details' records exceeded 2000.

As my formula contains multiple conditions across 2 entities, would there be suggestions on how I can optimize my formula to prevent data loss due to delegation record limit? I read of querying via 'Views', but it seems unlikely due to my conditions.

1) Desk entity

- Area (associated lookup)

- Facility (associated lookup)

2) Reservation Details entity

- Start Date

- End Date

- Selected Desk (lookup to (1) Desk)

- Booking Status

Thank you for any help given.

Categories:

Building Power Apps

All responses (9)

Answers (0)

Sort by

Shaheer Ahmad 2,194 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

Hello,
Hope you're doing well. Can you kindly specify the which Database are you using in your application?
Can you please add screenshot of the code from Formula bar and detail delegation warning.
Delegation can be a really tricky part especially when filtering the dataset. Make sure to use formulas that can handle delegation and sometimes delegation can be due to a simple issue for example comparison of different data types etc
ruihaolrh 473 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @ShaheerAhmad , I am using Microsoft Dataverse as my data source. No external connections such as SQL or SP is used.
The screenshot of my code is as follow:

The delegation warning message is shown as follow:

Previously, the "!" highlighted in yellow did show a delegation warning, but it is not showing now.
Edit: This was the previous formula that indicates a delegation error for the "!":
Shaheer Ahmad 2,194 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Instead of using !() try using Not() function
Shaheer Ahmad 2,194 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Also Excel is not a suitable database for large set of data. I suggest you to use Dataverse.
ruihaolrh 473 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @ShaheerAhmad , I am using Dataverse instead of Excel as my dataset.

Also, I will try Not() instead of !()
Shaheer Ahmad 2,194 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Sorry my bad. Maybe I misread. Yeah in this scenario also make sure that the ForAll function you're using is retuning an array and not a JSON array. Because you're using "IN" operator
["asd", "sdsd", "sds"]
and not
[{name:"vdv",value:"sdsd"},{name:"vdv",value:"sdsd"}]

You can verify this by highlighting the ForAll Code and the preview of the returned Table should contain not more than one column
Shaheer Ahmad 2,194 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Make sure to mark the post as solution if it solves your issue
ruihaolrh 473 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @ShaheerAhmad , there are several places that the delegation warning appears. I replaced "!" with "Not", but encountered the following error:
Shaheer Ahmad 2,194 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

I guess we should revert to "!" then. Have you validated the output of the forall loop?