Answered

Update Column by Adding Two Numbers from Two Separate Lists

(0) Share

Report

Posted on by forbudt4u

264

Hello all, I've been at this for over 3 hours and I'm running into failed flow after failed flow, and I'm wondering if someone could help. I'll show mostly with screen caps, but I have two lists which are called list one and two with arrows pointing to the relevant columns.

The gist is that a Manager will at some point update the Request Satus (Choice) column from List 1 from Requested to Completed. When that happens, I need a flow to take the Hours Requested (Number) entered and then Add it to the PTO Used (Number) Column in List 2. Getting the flow to trigger is easy, but I'm having problems getting the flow to filter out the common records between the two lists (Superintendent Email from List 1 to the Employee Email from List 2) so it updates the respective employees Used PTO Number. Below is the flow how I have it set up now.

Basically, the flow runs successfully, but there are not errors. When it gets to the record that triggered the flow, I get the following and it does not update the PTO Used Column in List 2 as I need it to.

Can someone please let me know what I'm doing wrong?

Categories:

Building flows

I have the same question (0)

All responses (9)

Answers (1)

Sort by

Suggested answer

BizAppBee 89 on at

Like (0)

Report
Copy link

Link copied!

Hi there, I did a test on my end and did the following:

1. This is what the top looks like

2. A condition checks to see if the status is complete

3. In your Get employee action, update the Filter Query to be: Employee/EMail eq '[Superintendent Email]'

4. I put the PTO Used in a variable and then added it to the Hours requested:

add(variables('PTO Used'),outputs('Get_Request')?['body/HoursRequested'])

To test

1. Added a PTO Tracker record for myself with PTO Used: 15 hours

2. Created a time off request for 8 hours

3. Flow ran successfully, adding 23 hours to my PTO used

Hope this helps!

Thanks

Was this reply helpful? Yes No
forbudt4u 264 on at

Like (0)

Report
Copy link

Link copied!

BizAppBee

Thank you for your assistance. I built the flow per your recommendation and it's getting stuck on the first Initialize Variable with the below error. I have confirmed the column is set up as a number column.

Was this reply helpful? Yes No
forbudt4u 264 on at

Like (0)

Report
Copy link

Link copied!

Okay, think I worked through the intial error with the variable. The flow proceeded to move forward and I'm now getting the below issues...

Was this reply helpful? Yes No
Suggested answer

BizAppBee 89 on at

Like (0)

Report
Copy link

Link copied!

Hi! Does your Hours Requested and PTO Used fields have actual values in them? It seems like one of the fields are blank. If that is the case you can add a condition to check if its blank, and if yes then set them to be 0.

Was this reply helpful? Yes No
forbudt4u 264 on at

Like (0)

Report
Copy link

Link copied!

So, I thought about that and ran some tests. I placed a hard number in the PTO Used Column on the PTO Tracker List, so both lists would contain hard numbers in them. The Flow would run past where it was getting stuck, but now I get a different error saying that the add function requires the first parameter to be an integer. I've tried converting the output to an integer and everything I can think of with no luck. The only way I can get the flow to run is if I put hard numbers in the Set PTO Variable. If I choose the PTO Used field, it breaks and gives the above error again.

Was this reply helpful? Yes No
forbudt4u 264 on at

Like (0)

Report
Copy link

Link copied!

Sorry, was looking at wrong error... it does this now. BOTH fields being used in the add function are single digit number columns (no decimals) and are located in the output for each action as expected. The error is telling me that it's seeing the output from the Get Request as a non-integer (number)... am I reading into this wrong?

Was this reply helpful? Yes No
Suggested answer

BizAppBee 89 on at

Like (0)

Report
Copy link

Link copied!

Okay so since both fields are integer, I would just change your variable type to Integer and that solve the issue :)

Was this reply helpful? Yes No
forbudt4u 264 on at

Like (0)

Report
Copy link

Link copied!

BizAppBee. I really wish it were that simple. That is the first thing I checked.

Things I've done to confirm.

1. Set the Update Items PTO Used to use both fields directly (PTOUsed and PTORequested)... the flow updates as it should!

2. Changed the Update Items PTO Used Expression to convert one or the other, or both of the columns to integers... it produces the same error with float and integer issues.

3. Plugged direct numbers into the Update Items PTO Used column... flow updates as expected.

4. Placed direct numbers into the PTO Used Variable... flow updates as expected.

5. Completely rebuilt the lists and flows... same errors with float and integer with both versions.

I really feel like I've done everything I can think of. So frustrating. I'm at the point where I'm likely going to throw my hands up and tell my guys we'll just set up a notification that reminds them to update this field manually because we can't get the system to do it autmatically. I hate that, but I've spent near 3 days on this one issue.

Thanks for your assistance.

Was this reply helpful? Yes No
Verified answer

forbudt4u 264 on at

Like (0)

Report
Copy link

Link copied!

Okay, I just got lucky and don't know why I didn't think of this. But, Copilot to the rescue. I accidently pasted the add expression into the copilot bar and it recommended a different expression...

add(variables('PTO Used'), outputs('Get_Request')?['body/value']?[0]?['HoursRequested'])

Not sure why it's seeing this column differently, but it is. I would think it would have the same issue with the PTO Used column from the PTO Tracker list as both columns from both lists are the exact same. Oh well, it's working now, and the above expression is what finally did it.

Was this reply helpful? Yes No