Update SP List Item Conditionally from Second SP List

[chasquad2]

Hi Everyone!

I have 2 SharePoint Online custom lists:

1. Sales
2. Profit & Loss Statements

 In the Sales list, each sale is a list item and there are a couple of columns to note:

• Sales Channel (choice field with pre-set selectable values)
• Gross Sales (calculated field from other columns in this list presented as a number with 2 decimals)

In the Profit & Loss Statements list, there is one list item for each fiscal year (FY21 for instance) and then there is a separate column for each sales channel.  These are the same sales channel values used in the Sales list above.

I’m wanting to use Power Automate to create a flow that would run each time a new item is created in the Sales list that would then add the value for the given Sales Channel to the existing value in the Profit & Loss Statements list for that year:

For example.  If the current value of Profit & Loss Statements sales channels for FY21 were as follows:  Amazon $25.00 and Ebay $25.  Then a new sale was to be added to the Sales list for a $50 sale from Amazon, then the item for this year in the Profit & Loss Statements list would then be: Amazon $75.00 and Ebay $25.

I’m struggling with the Update Item section.  I need to add an add() expression to each field (Ebay, Amazon, etc.) that would add the Gross field from the Sales table to the existing value in the appropriate Sales Channel field in the Profit & Loss Statements list for the correct item (year).

In other words, If the Sales Channel value in the Sales list = eBay then add the Gross value for that Sales list item to the eBay column in the Profit & Loss Statements list.  What would the syntax for this be?

Thanks so much for looking at this.  I think that, with your help, figuring this out will help me learn more about the capabilities of Power Automate!

[annajhaveri · Accepted solution]

@chasquad2 , for your case, i would suggest that you use REST API (Send HTTP request to SharePoint action) to update the SharePoint item instead using Update Item action.

One thing you need to make sure that you are using internal name of the column instead of display name of the column. To get internal name of the column, you can go to your list settings, click on the column, then look at the URL in the browser, you will find field=<columnname>, the columnname which you see in URL will be the internal name of the column.

So now first add Initialize Variable action to start of your flow, and name the variable as 'SetFieldValue' of type string

So once you have internal name of all your columns such as Amazon.com, eBay.com. Add a switch action to your flow, and select the Sales Channel value from Sales List in the "On"  parameter of Switch action

Then add case for each Sales Channel in the switch action as shown below, to each case, you need two actions, one is compose action where you will use expression add('value from current profit and loss list for that sales channel of case', 'value from sales list - current item'). Then add Set Variable action where you will assign value to 'SetFieldValue', the value will be the 'internalcolumnnameforsaleschannel':'output from compose action' as shown below

Now after the switch action, add "Send an HTTP request to SharePoint" action and configure it as follows:

[chasquad2 · Accepted solution]

Nope.  Not what I was looking for.  I figured it out myself.  Thanks.

[askondo]

@annajhaveri Thank you for always participating in the forum and for your valid answers.

[annajhaveri]

@askondo , thanks for recognizing my efforts, always happy to help community.