Answered

Storing formula result into a variable and referencing it

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi,

I have two formulas, one checks if there's a Sharepoint list record which meets some conditions and if it does it patches that record. The formula for finding the record appears two times in the code and I suppose that slows down the run of the application. Is it possible to run the code which finds the record just once and reference it so it runs just once?

I've tried with Set() and then doing Patch(Employees_dump; record) but that doesn't work.

Set(record;Last(
 Filter(Employees_dump; PracovnikJmeno = User().FullName && Title = ThisItem.Title)).LongitudEnd);;

If(
 IsBlank(
 Last(
 Filter(Employees_dump; PracovnikJmeno = User().FullName && Title = ThisItem.Title)).LongitudEnd); 

Patch(Employees_dump; Last(Filter(Employees_dump; PracovnikJmeno = User().FullName && Title = ThisItem.Title)); {Konec: Now(); LatitudEnd:Location.Latitude;LongitudEnd:Location.Longitude} );;

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
@Anonymous

I'm nt entirely sure what logic you are trying to achieve on the formula.

You are checking if the record is blank, and if it is, then you are patching it?? That's not going to work.

Maybe give something like this a try:

Set(record; Lookup(Employees_dump; PracovnikJmeno = User().FullName && Title = ThisItem.Title) );; If(!IsBlank(record.Title); Patch(Employees_dump; // Record exists...update it
record;
{Konec: Now();
LatitudEnd:Location.Latitude;
LongitudEnd:Location.Longitude}
);
Patch(Employees_dump; // Record does not exist...create it
Defaults(Employees_dump);
{Konec: Now();
LatitudEnd:Location.Latitude;
LongitudEnd:Location.Longitude}
)
)

In the above, you are setting a variable to the record you want.

You can use the IsBlank on any column in that record that you know should exist to determine if it is blank. In this case, we're looking at the Title.

But, to "correct" the logic, in this case we are first setting the record, then checking if it is NOT blank, and then patching it. And if it is blank, then creating it.

I hope this is helpful for you.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Thank you for your reply.
Yes, I'm checking if the record is blank, and if it is, then I'm patching it. It's an "attendance" system for construction workers. They come to work, press a button and that creates a record with their name, time and GPS. Then when they leave they press a button and that patches the record created on arrival with departure time and GPS.

With this code I'm trying to avoid data being overwritten or not stored at all. In case they press the "leave" button without having created the "arrival" record first the app tells them that there's no arrival record.

The code I showed in the first post works but it takes pretty long with many records in the list. I think it could be made faster by creating the variable though I certainly may be wrong, I'm pretty new to coding.

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like (2)

Report
Copy link

Link copied!
@Anonymous

Well, you have the right idea about grabbing the record once if you can, but there was a problem in your set statement that I corrected in my reply.

In your original formula, you were only assigning the LogitudEnd value in your variable. I believe you really wanted the record, thus to avoid all the further lookups lasts and filters.

You had:

Set(record;
Last( Filter(Employees_dump;
PracovnikJmeno = User().FullName
&& Title = ThisItem.Title)
).LongitudEnd
);;

This will just assign the field value to the record variable.

If you want the whole record, then the adjustment I made in my reply will do the trick. And here it would be based on your reply about the record you are trying to get based on the attendance scenario you described:

Set(record; Last(
Filter(Employees_dump; PracovnikJmeno = User().FullName && Title = ThisItem.Title)
) );;

Was this reply helpful? Yes No