Update Items for One Item on Second SharePoint List Using Power Automate

[y2kardell]

Hi All-

I have a situation that someone may be able to assist with hopefully. I have two SharePoint lists (List A and List B).

List A has - for example six columns (A,B,C,D,E,F) that I want copied to List B. Columns E is based on user input on List B,

but I would like to copy the "value" that the user inputs for Column E to Column F so that they both have identical values which

is a currency value and have both columns display the total of each which I can do by creating a calculated column for Column F but calculated columns do not allow sums.

Therefore I was wanting to find a way to use "Update Item" to copy the value from Column E to Column F but List B is not a "when an item is triggered or modified column" so that I can use  the dynamic content from Column E since its from the user input and dump it into Column F in "Update Item" - if all of this makes sense. Normally it would be no issue for me if List B were a triggered "when an item is created or modified" connector, but it isn't but I need to find a way to copy the value from Column E to Column F so that the currency values are a match for both.

I know this is a lot but again can anyone offer any suggestions?

y2kardell

[Mister_Shaik]

Hi @y2kardell ,

I'm assuming that you want the input from the users in a column E to be copied over to the column F.

In that case, you can use a simple power automate and copy over the data from one column to another. It would be much simpler.

I'm not sure if it's a strict requirement for you to not use Power Automate, but in case you want to give it a try, you can refer the simple flow snip below

Hope this helps 🙂

Kind Regards,

Shaik Sha
________________________________________________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

[y2kardell]

Hi @Mister_Shaik - thanks for the reply, that is exactly what I am trying to accomplish, just one column in the list to copy over to another. I can do it in Sharepoint with a calculated field, but I need the calculated field to give a sum/total and you cant do that. I will give this a try and let you know if it solves the issues.

[y2kardell]

Hi @Mister_Shaik  - how do you have the Get changes for an item or a file (properties only) setup?

In other words what do you have for  ID, Since, and Until? I'm not too familiar with how those are used.

Can you assist?

Thanks again for your help,

y2kardell

[Mister_Shaik]

Hi @y2kardell ,

The role of get changes is to compare and get all the changes made since a particular version. SharePoint list items be default maintain version number for all the changes we make. In our case, we want to get the changes since last version. which is current version-1.

The inputs of the get changes action would be,

1. Id field - this is the item ID from the trigger. (The trigger should be set to your List B)
   
2. Since field - this is the nothing but current version -1. below is the expression
   @{if(lessOrEquals(triggerOutputs()?['body/{VersionNumber}'],1),1,sub(triggerOutputs()?['body/{VersionNumber}'],1))}
   
3. For the if condition, you can get Boolean values to check if a particular column value has changed, in your case column E
   
4. You need to pass the comparison Boolean, which is true from the expression as well.
   

Now in your Yes branch of your condition, just add an update Item action and in the input for F column, give E column value from trigger as input.

Kind Regards,

Shaik Sha
________________________________________________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

[y2kardell]

@Mister_Shaik - thanks once again, I truly and sincerely appreciate you taking the time to help me out with this issue. If I get it to work or not I will let you know.

Kind Regards,

y2kardell

[y2kardell]

@Mister_Shaik - I have applied your recommendation to my workflow but am having a few issues.

To give some background on what I am trying to accomplish, there are 2 approval requirements throughout the entire process. The shipper submits a ticket request with relevant shipping information. There are 2 lists being used in the process. The trigger being Ship Ticket Request (from the shipper), and it is copying  to the other list Ship Tickets. Approver #1 clicks the link and enters the Tracking # and Est Cost in the two columns setup in the Ship Ticket Request list. Approver #2 then clicks a link that takes them back into the initial form (Ship Ticket Request) and enters the Actual Amt in the designated column on the form. In theory, all modifications should then be transferred to the second list (Ship Tickets), but the only column that is not being transferred (Update item) to the second list (Ship Tickets) is the Actual Amt.

#1) If you look at the entire workflow that I have uploaded, everything works as should until it gets to the second approval "Start and wait for an approval (Routed to AP)." After that it appears that the workflow seems to go back through a loop to where its starting over at the "Send an email (V2) #1 (Back to Requesting Employee)" and "Start and wait for an approval (Routed to Mailroom)." 

#2) The workflow never seems to make it past "Start and wait for an approval (Routed to AP)" to where it never recognizes the "Update item" connector and the "Send an email (V2) (Back to Shipper with Actual Amt Deducted from Payroll)" part of the flow. Can you take a look at what I have uploaded and shed some light on why these 2 things are happening/not happening? If more information is needed please let me know.

Thanks,

y2kardell

[Mister_Shaik]

Hi @y2kardell 

From the looks of it, it seems that one of the field inputs for either update item action or send email action is from the "Get items" or a value that's part of an array, which is causing this issue and your flow didn't execute the part for "apply to each" because the input you passed to the array might've been empty.

So, delete the "Apply to each" action and add the "Update Item" and Send "Email" again, but this time when you're populating from the dynamic content popup, make sure that all inputs you're giving are from the trigger. If you notice that the "Apply to each" is still being added automatically, then notice on which field's entry it's getting added so that you can check if it's an array and debug it.

Also for the fields that are not required and if you don't want to fill them, you can leave them blank and the old values will be retained. As for dropdowns, if you want to retain old values, you can select the dropdown and select the "Enter custom value" and leave it blank. That way it will retain old dropdown value.

Let me know how it goes (y)

Kind Regards,

Shaik Sha

[y2kardell]

@Mister_Shaik - thanks for the reply. I made the changes as shown in the screenshots but it still didn't trigger the "Send an email (V2) (Back to Shipper with Actual Amt Deducted from Payroll" and it went back through the loop again to where it "(Send an email (V2) #1 (Back to Requesting Employee) and Start and wait for an approval (Routed to Mailroom) on the other side of the flow (left hand corner).

Any idea why both of these things are still happening? BTW the has in Condition 2 is "Has column changed Actual Amt" if that helps.

Thanks again,

y2kardell

[Mister_Shaik]

Hi @y2kardell ,

Can I Know when you're modifying the Actual Amt column?

Are you modifying the Actual Amt column, then it send's an approval and then once you approve, your calling the Get changes action?

or

Are you modifying the the data, the flow gets triggered, then the approval is sent, then you modify the Actual Amt, next you're getting the changes?

If it's the second way, then the get changes action won't return you the changes with how it's configured right now.

To debug and check where things are not working as expected, you can add a compose action to check the output of Get Changes action.

Right before the "Condition 2", add a compose and from dynamic contents select the body property of "Get Changes" action and add it to the compose. Now when you run the flow next time, you can check what fields have actually changed - exploring the output can help you to understand which output you're getting and if the Actual Amt field has changed.

Kind Regards,

Shaik Sha

[y2kardell]

@Mister_Shaik - the Actual Amt column is being modified by the second approver here: Start and wait for an approval (Routed to AP)  then I am getting changes for an item or a file (properties only). The first SharePoint list aka Shipping Request Form at the initial trigger "When an item is created or modified" is where the Actual Amt Column value is being input, then supposed to be updated and copied to the second list aka Ship Tickets.

y2kardell

[Mister_Shaik]

@y2kardell  Now we know why the get changes is not returning anything.

To resolve this simply, we can use replace the formula with Version number from the trigger directly in the Since field input of "Get Changes" action, as the second approver is modifying the item before the get changes.

[y2kardell]

@Mister_Shaik - well that didn't fix it either, still looping back to the beginning and the email after update item 2 still not being sent.

[Mister_Shaik]

@y2kardell , have you set any trigger conditions? If you haven't then the flow will more than once, as the second approver is going back to the list and updating it - which will trigger another flow and a second approval notification will be sent out.

To stop this, you need to have a flag field to check whether the flow really needs to trigger. This will stop the loop from going into loop.

[y2kardell]

@Mister_Shaik - Ive got this one set in the screenshot so that the flow doesn't go back to the first approver once they make modifications to the "Tracking #" and "Est Amt Columns": 

equals(length(body('Get_items')?['value']),0)

Is there a way to add another to the same condition in the screenshot so that when the second approver makes modifications it doesn't go back to the "Yes" column in the screenshot? Would it be and AND or an  OR condition and what function that you recommend that would stop the loop?

Thanks,

y2kardell

[Mister_Shaik · Accepted solution]

It should be an OR, the two flags should be enough to stop the second flow instance - but I'd suggest to add those to the trigger as a triggerCondition rather than inside the If condition.