cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Update SP List Item Conditionally from Second SP List

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?

getitem.JPG

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!

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

@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

annajhaveri_0-1610021983236.png

 

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

 

annajhaveri_1-1610022198101.png

 

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

Screenshot 2021-01-07 181206.png

Regards,
Anna Jhaveri

If I have answered your question, please mark the post as Solved to help other users to identify the correct answer
If you like my response, please give it a Thumbs Up.

View solution in original post

Regular Visitor

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

View solution in original post

4 REPLIES 4
Super User
Super User

@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

annajhaveri_0-1610021983236.png

 

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

 

annajhaveri_1-1610022198101.png

 

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

Screenshot 2021-01-07 181206.png

Regards,
Anna Jhaveri

If I have answered your question, please mark the post as Solved to help other users to identify the correct answer
If you like my response, please give it a Thumbs Up.

View solution in original post

Regular Visitor

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

View solution in original post

Microsoft
Microsoft

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

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

Regards,
Anna Jhaveri

If I have answered your question, please mark the post as Solved to help other users to identify the correct answer
If you like my response, please give it a Thumbs Up.

Helpful resources

Announcements
Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Microsoft Ignite

Microsoft Power Platform: 2021 Release Wave 1 Plan

Power Platform release plan for the 2021 release wave 1 describes all new features releasing from April through September 2021.

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (12,172)