Delegation Warning on LookUp()

[EmilyWinonaIT]

Hello Community!

I am working to build a Time Clock with some Patch() formulas that require LookUp() functions to clock the user out of the same line item. I have tried to minimize the amount of delegation issues by creating variables OnStart of the Home screen.

I'm having some issues with the "&&" and "=" in my whittled down formula and I would love for some assistance.

HOUR LOG = SharePoint List/Database

EMPLOYEE NAME = Text field

ROLE = Choice Field

ItemCreatedDate = Calculated Date field (Text(Created,"[$-en-US]mm/dd/yyyy"))

Variables:

Set(
    currentuseremail,
    Text(User().Email)
);
Set(
    currentusername,
    Text(User().FullName)
);
Set(
    currentdate,
        Text(Now(),
        "[$-en-US]mm/dd/yyyy")   
);
Set(
    currenttime,
    Now()
    
)

Formula:

Refresh('HOUR LOG');
If(
    !IsBlank(LookUp('HOUR LOG', 'EMPLOYEE NAME'=currentusername && ItemCreatedDate = currentdate && Dropdown1.SelectedText.Value = ROLE.Value )),
    Patch(
      'HOUR LOG',
       LookUp('HOUR LOG', 'EMPLOYEE NAME'=currentusername && ItemCreatedDate = currentdate && Dropdown1.SelectedText.Value = ROLE.Value),
       {
        'CLOCK OUT': Now()
       }
     )
)

 (Image with Warning locations attached)

Thanks guys!

[EmilyWinonaIT]

UPDATE: I read on similar forum posts to change your data source to a variable through Collect(variable, [DATA SOURCE]). This actually fixed the delegation warnings but it caused the Patch() functions to fail for the formula above. I adjusted every other area of the formula and determined that this change was the root cause. Changing this back to 'HOUR LOG' fixed the app but resulted in the delegation warnings again.

[DanielHoyt]

LookUp can be a bit finicky. Do these lookups work on their own outside of the Patch/If statements?

With how you've formatted your date column I foresee an issue if people clock out for breaks. There could potentially be multiple clock-ins on a single date that could result in the LookUp returning the wrong item. It should return the first value it finds though so it shouldn't prevent the formula from working it just might not update the record(s) you were expecting.

You might try breaking the formula out into separate statements so you can better troubleshoot the issues. Below is an example of what that might look like. 

If(!IsBlank(LookUp('HOUR LOG', 'EMPLOYEE NAME'=currentusername && ItemCreatedDate = currentdate && Dropdown1.SelectedText.Value = ROLE.Value ), 
    Set(ItemFound, True), 
    Set(ItemFound, False));
Set(CurrentItemID, LookUp('HOUR LOG', 'EMPLOYEE NAME'=currentusername && ItemCreatedDate = currentdate && Dropdown1.SelectedText.Value = ROLE.Value, ID)));
If(ItemFound, 
    Patch('HOUR LOG', 
          First(Filter('HOUR LOG', ID=CurrentItemID)), 
        {
             'CLOCK OUT': Now()
        }
    )
);
Reset(ItemFound);
Reset(CurrentItemID);

 

Alternatively while using Collect() I would recommend first ensuring you're using ClearCollect() so you don't end up with duplicate values in your data source. You can actually filter the collection within that command. Then you can use If(!IsEmpty()) to check if any values were returned to your collection. I'm not very familiar with the Patch() function but for the purposes of this application I believe you still need to patch to the data source directly not to the collection. Of course I'm not certain what your collect/patch formulas looked like in your reply example. Here's what I'm thinking might work. I'm not certain about the Patch() portion though:

Refresh('HOUR LOG');
ClearCollect(HOURLOGCollection, 
    Filter('HOUR LOG', ItemCreatedDate=currentdate && 'EMPLOYEE NAME'=currentusername && Dropdown1.SelectedText.Value=ROLE.Value)
);
If(!IsEmpty(HOURLOGCollection), 
    Patch('HOUR LOG', First(HOURLOGCollection), 
        {'CLOCK OUT':Now()}
    )
)

It's worth noting you can check the contents of your collection under File>Collections to ensure multiple items aren't returned by your filter and lookup functions and to inspect the data returned.

[EmilyWinonaIT]

Hey Daniel!

Thanks for your reply! That was a good call on the ClearCollect(). That was a silly mistake.

Also the break/lunch issues, it's not best practice unfortunately. I would have loved to have a different solve for collecting user information and running flows instead of using Patch() functions. The variable functions gave no errors but just wouldn't process in time for the Clock Out to run successfully.

However, after looking at the code with fresh eyes I changed my text dates to date value and everything seemed to work. Originally I converted everything to text to get the short date. Theoretically this would have worked better with Lookup() except that I was using a calculated field to convert an existing date to text. This ensured identical formatting. But that broke everything. Converting everything back to date and using DateValue() and Text() together fixed the formatting issue.

Pretty sure I tried this several times yesterday. I probably didn't take the time for the variables to reset fully though.

It's always the simple things 🙂

Thanks again!!

Updated Code:

Refresh('HOUR LOG');
If(
    !IsBlank(LookUp('HOUR LOG', 'EMPLOYEE NAME'= currentusername && 'LOG DATE' = currentdate  && Dropdown1.SelectedText.Value = ROLE.Value )),
    Patch(
      'HOUR LOG',
       LookUp('HOUR LOG', 'EMPLOYEE NAME'=currentusername && 'LOG DATE' = currentdate && Dropdown1.SelectedText.Value = ROLE.Value),
       {
        'CLOCK OUT': Now()
       }
     )
);
Navigate([@SucessClockOut])

Updated Variables:

Set(
    currentuseremail,
    Text(User().Email));
Set(
    currentusername,
    Text(User().FullName));
Set(
    currentdate,
        DateValue(Text(Now(), "[$-en-US]mm/dd/yyyy")));
Set(
    currenttime, Text(Now(), "[$-en-US]hh:mm"))