cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Dual Super User
Dual Super User

Update a secondary list which has a common value.

Hello,

Here is something I am attempting to try and hope that this community can point me in the right direction.

I have two lists in SharePoint online. Here are the columns in Parent list and Child list

Parent list has the following

-ID (Which is automatically populated). This list keeps an inventory of makes and models

-Title

- Make

-Model

Child List has the following. This list keeps an inventory of accessories for each model

-ID (which is automatically populated)

- Model (Which matches what is in the Parent list)

- Quantity of Keyboard (This is a numerical column to keep count of keyboards left in an inventory)

- Quantity of Mouse (This is a numerical column to keep count of mouse left in an inventory)

My requirement is to subtract the quantity of keyboards and mouse by 1 in the Child list when the same model is added in the Parent list.

 

When I was researching to find a solution, I came across these two posts (Post 1 and Post 2) on this  forum and saw that the solution built there is very similar to what I am looking for. Hence my question is-

Can I do the addition, subtraction and multiplication in Flow itself to update the above requirement? Or do I have to use Logic apps? And if this is possible can someone provide me some guidance?

I do appreciate all the helps in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Power Automate Staff audrieg
Power Automate Staff

Re: Update a secondary list which has a common value.

I was able to work through this with Daniel over Skype. A couple of things to note:
 
We should update the documentation (link you provided) so that it alerts customers to this fix - adding Jon L. to see if he can make that happen @JonL-MSFT If you try and subtract a number from a numeric column in SharePoint using the instructions on that documentation, you'll see what I mean. You'll need Mabel's instructions to make it work. 

  • Once they put in the compose statement it locks them from adding double quotes around the syntax for some reason (I was watching Daniel over skype, and he could not wrap double quotes around the syntax once it had resolved itself)
  • We resolved the problem by first removing the "output" from the update action, then we could retype our compose (I also don't think we were clear on whether ['Keyboard'] should be typed rather than using the dynamic tags - so we tried both and realized that it needed to be typed.)
  • We copied your compose syntax and surrounded it with double quotes...Note: once we resubmit the workflow Microsoft Flow removed the double quotes again from the end user interface (they were visually gone after the first successful run, so this feels like a work-around to me...)
  • After we got the subtraction to work - then we added the Output back into the Update action - and all was well again.
    YAY! He's up and running again.
     
    Questions for Mabel:
     
    A. Can you explain why you added "item()" to the syntax?
    B. Can you explain why you added the ? mark to the syntax?
     
    Thank you again Mabel,
    Audrie

View solution in original post

19 REPLIES 19
Community Support Team
Community Support Team

Re: Update a secondary list which has a common value.

Hi Darogael,

 

I create two SharePoint list according to your description.
Then create a flow looks like below to update the quanlity of mouse/keyboard in the ChildList.
In the condition, “Model1 Value” is a Dynamic content from the action “Get items”. If it is equal to “mouse”, then update the item in ChildList.

Hope it could be a reference for you. You could add another Condition to filter “keyboard” with the similar actions.

1.png

3.PNG

If you need more help, please feel free reply.

 

Best regards,
Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Dual Super User
Dual Super User

Re: Update a secondary list which has a common value.

Thanks Mabel! In the Compose action whose screenshot you provided, what is 'bq0e'? Does it stand for Keyboard but the actual column name is 'bq0e'?

Also, How do I get the 'Model1 Value' in the condition? The only dynamic content I have available is Value and I can't add Model and Value.

Community Support Team
Community Support Team

Re: Update a secondary list which has a common value.

Hi Darogael,

 

In the Compose action whose screenshot you provided, what is 'bq0e'? Does it stand for Keyboard but the actual column name is 'bq0e'?


”bq0e” is the Number column in the ChildList. Please see the screenshot of one of my list.

 

5.PNG
When creating the flow, I add a Compose action, and I configure the Compose action likes below. The Number is selected from the dynamic content of the action “Get items”.

 

1.png

Then add the other actions, save the flow. You will get the error message looks like below:


3.PNG


Then modify the Compose function with the Highlighted part, update the flow, it will be saved without error.

 

4.PNG

 

How do I get the 'Model1 Value' in the condition? The only dynamic content I have available is Value and I can't add Model and Value.


It’s also based on your data source. What’s the column name and column type on your list?


On my side, I configure it as a Lookup column type which lookups to the Parent list, and the column name of it is Model1.


So on the Condition, I select the “Model1 Value” from the dynamic content of “Get items” action from the ChildList.


Please check the first screenshot I provided in this post.

 

Hope this could answer your questions.


Best regards,
Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Dual Super User
Dual Super User

Re: Update a secondary list which has a common value.

Hi Mabel,

Yes, it did answer my questions. Thanks to your detailed screen shots I have the conditions all figured out and now am getting an error at the final step when the Keyboard column getting updated with the new calculated OutPut. I see that the correct item in the Child List is being found.  I did check and confirm that the Keyboard column is a number type.

Here are some screenshots.

For me the actual column name is Keyboard hence I didn't have to modify the function

 

Capture.PNG

 

I'm getting the error only when the model matches which tells me at the condition is working.

Capture2.PNG

 

Capture3.PNG

 

Also, in my case I am not using any lookup column but manually entering the model. Hence I did try using 'Contains' in the condition but that didn't help

Capture4.PNG

 

Any thoughts? Ideas?

Dual Super User
Dual Super User

Re: Update a secondary list which has a common value.

I did some more testing and narrowed it down to the last update action. The Output variable doesn't have a numerical value but instead is providing the entire formula. That is why I am getting the error.

 

Based on the syntaxs I see on the Workflow Definition Language page, @sub(COLUMNNAME, 1) should have sufficed but it is erroring out.

 

I've even added a send email action prior to the update item and the email does go out for the correct item, but the Output value in the email body is not a numerical value. Here's some screenshots

 

Capture.PNG

 

 

Here is a screenshot of what I get in my email

 

Capture2.PNG

 

I'm not sure how yours worked because I tried to replicate it and wasn't successful.

Dual Super User
Dual Super User

Re: Update a secondary list which has a common value.

Can I get some insight from the Flow community support team please?

Dual Super User
Dual Super User

Re: Update a secondary list which has a common value.

Mabel,

Haven't heard back from you or your team members regarding my updates on this question. Can I expect some feedback by the end of this week?

Community Support Team
Community Support Team

Re: Update a secondary list which has a common value.

Hi Darogael,

 

Sorry for the late response.

 

Thanks for the screenshot you provided. But it seems that there is something wrong with the Compose function.


The one you are using now is:
3.PNG


Please try to configure the function for the Compose action as “@sub(item()?['Keyboard'],1)” to see if it works.

 

Please refer to my steps and if you are still having issues with this flow, please feel free reply. I will keep an eye on this case.


Best regards,
Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Dual Super User
Dual Super User

Re: Update a secondary list which has a common value.

Thanks for responding back Mabel.

Let me try the new compose action you suggested once again and see if that works. As a reminder, I am not using any lookup columns here.

Dual Super User
Dual Super User

Re: Update a secondary list which has a common value.

I tried again and it did not work. Here are some screenshots.  Again, I am not using a lookup column but Keyboard is a number column.

As you can see, I added the compose function as text i.e. I did not use any dynamic content.

 

Capture4.JPG

 

I've been sending an email to myself to see what value I get and again, I received the compose formula only and not a numerical value. Here's a screenshot of the the email I got with the above compose input

Capture3.JPG

Now, consider some testing I did before where I did not use the '@' and use the dynamic content

 

Capture.JPG

 

I still didn't get the subtracted value, however, atleast I was seeing numerical values in the subtract function.

 

Capture2.JPG

Please advice next steps.

 

Community Support Team
Community Support Team

Re: Update a secondary list which has a common value.

Hi Darogael,

 

Please add a double quotation mark on the function to see if it works.

 

Please enter this function “@sub(item()?['Keyboard'],1)” with the double quotation mark in the Compose formula bar.

 

Or you could use the Dynamic content, please add @ and double quotation mark on the function. Then please follow my steps I provided in the second post to customize the function.


Best regards,
Mabel Mao

 

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Dual Super User
Dual Super User

Re: Update a secondary list which has a common value.

Hi Mabel,

Adding single quotes did not work as well. I tried with adding as text and using the Dynamic content. Below are screenshots of all the functions I added and the emails I got.

 

Using text i.e. manually writing ['Keyboard'] in double quotes

 

Capture1.JPGCapture2.JPG

This one is using the dynamic content with double quotes. Atleast here I got to see the numerical value of 100

Capture3.JPGCapture4.JPG

I even attempted the below i.e. without adding any quotes.

Capture7.JPGCapture8.JPG

 

Just to be sure, I

-  Add an action
- search for Compose
- Select 'Data Operations - Compose' and then added the function there. Is that the same steps you followed to get it to work?

 

If yes then it looks like we have exhausted all the options. I would love to see a short video that includes the two lists, each of it's columns and a walk-through of building the flow. If that is asking to much then perhaps a trouble-shooting meeting? Let me know

 

Dual Super User
Dual Super User

Re: Update a secondary list which has a common value.

Mabel,

Any updates?

Power Automate Staff Audrie-MSFT
Power Automate Staff

Re: Update a secondary list which has a common value.

@Mabel Could you share the flow where you did this with me so I can try to figure out what you did differently please? This customer is still stuck on this.

Thank you
Audrie
Power Automate Staff Audrie-MSFT
Power Automate Staff

Re: Update a secondary list which has a common value.

I was able to work through this with Daniel over Skype. A couple of things to note:

 

  1. We should update the documentation (link you provided) so that it alerts customers to this fix - adding Jon L. to see if he can make that happen @JonL-MSFT If you try and subtract a number from a numeric column in SharePoint using the instructions on that documentation, you'll see what I mean. You'll need Mabel's instructions to make it work. 
  2. Once they put in the compose statement it locks them from adding double quotes around the syntax for some reason (I was watching Daniel over skype, and he could not wrap double quotes around the syntax once it had resolved itself)
  3. We resolved the problem by first removing the "output" from the update action, then we could retype our compose (I also don't think we were clear on whether ['Keyboard'] should be typed rather than using the dynamic tags - so we tried both and realized that it needed to be typed.)
  4. We copied your compose syntax and surrounded it with double quotes...Note: once we resubmit the workflow Microsoft Flow removed the double quotes again from the end user interface (they were visually gone after the first successful run, so this feels like a work-around to me...)
  5. After we got the subtraction to work - then we added the Output back into the Update action - and all was well again.

YAY! He's up and running again.

 

Questions for you:

 

A. Can you explain why you added "item()" to the syntax?

B. Can you explain why you added the ? mark to the syntax?

 

Thank you again Mabel,

Audrie

Power Automate Staff Audrie-MSFT
Power Automate Staff

Re: Update a secondary list which has a common value.

I was able to work through this with Daniel over Skype. A couple of things to note:

 

  1. We should update the documentation (link you provided) so that it alerts customers to this fix - adding Jon L. to see if he can make that happen @JonL-MSFT If you try and subtract a number from a numeric column in SharePoint using the instructions on that documentation, you'll see what I mean. You'll need Mabel's instructions to make it work. 
  2. Once they put in the compose statement it locks them from adding double quotes around the syntax for some reason (I was watching Daniel over skype, and he could not wrap double quotes around the syntax once it had resolved itself)
  3. We resolved the problem by first removing the "output" from the update action, then we could retype our compose (I also don't think we were clear on whether ['Keyboard'] should be typed rather than using the dynamic tags - so we tried both and realized that it needed to be typed.)
  4. We copied your compose syntax and surrounded it with double quotes...Note: once we resubmit the workflow Microsoft Flow removed the double quotes again from the end user interface (they were visually gone after the first successful run, so this feels like a work-around to me...)
  5. After we got the subtraction to work - then we added the Output back into the Update action - and all was well again.

YAY! He's up and running again.

 

Questions for you:

 

A. Can you explain why you added "item()" to the syntax?

B. Can you explain why you added the ? mark to the syntax?

 

Thank you again Mabel,

Audrie

Power Automate Staff audrieg
Power Automate Staff

Re: Update a secondary list which has a common value.

I was able to work through this with Daniel over Skype. A couple of things to note:
 
We should update the documentation (link you provided) so that it alerts customers to this fix - adding Jon L. to see if he can make that happen @JonL-MSFT If you try and subtract a number from a numeric column in SharePoint using the instructions on that documentation, you'll see what I mean. You'll need Mabel's instructions to make it work. 

  • Once they put in the compose statement it locks them from adding double quotes around the syntax for some reason (I was watching Daniel over skype, and he could not wrap double quotes around the syntax once it had resolved itself)
  • We resolved the problem by first removing the "output" from the update action, then we could retype our compose (I also don't think we were clear on whether ['Keyboard'] should be typed rather than using the dynamic tags - so we tried both and realized that it needed to be typed.)
  • We copied your compose syntax and surrounded it with double quotes...Note: once we resubmit the workflow Microsoft Flow removed the double quotes again from the end user interface (they were visually gone after the first successful run, so this feels like a work-around to me...)
  • After we got the subtraction to work - then we added the Output back into the Update action - and all was well again.
    YAY! He's up and running again.
     
    Questions for Mabel:
     
    A. Can you explain why you added "item()" to the syntax?
    B. Can you explain why you added the ? mark to the syntax?
     
    Thank you again Mabel,
    Audrie

View solution in original post

Community Support Team
Community Support Team

Re: Update a secondary list which has a common value.

Hi audrieg,

 

Thanks for looking into this issue.

 

Here is the Flow Id:
https://flow.microsoft.com/manage/environments/b4e47519-1490-4571-a4fc-eeee30489e5e/flows/2eeb4c00-6...

 

And screenshots for my flow configuration:

3.PNG

4.PNG

 


About your questions:
A. Can you explain why you added "item()" to the syntax?
B. Can you explain why you added the ? mark to the syntax?

 

Detailed steps for how I get them:
1. Add the trigger and actions as following, for the Compose action, I consider to use sub function and I configure the function as below.
Note: I select Number from the dynamic content of action “Get items”.
5.PNG


2. Then click the “create flow” button to save the flow. Pay attention to the highlighted part. I use item()?['OData__x006b_my3'] replace the dynamic content Number, then the Compose action would be "@sub(item()?['OData__x006b_my3'],1)".
Then try to save the flow again, it will be saved successfully.
6.PNG

7.png


3. After step2, add the condition and other actions according to your need.

 

I just tried this flow on my side again, and it is still working fine on my side.

 

@darogael Please try step2 again, I assume that there might be something wrong with your Compose configuration.


About your last post, please don’t manually enter [‘Keyboard’] in double quotes, it wouldn’t be validated.


Select the column from the dynamic content then replace it with the language expression as suggested.


Note: Double quotes is needed, and please pay attention to place comma “,” in the right place.

8.PNG

 


Best regards,
Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Power Automate Staff Audrie-MSFT
Power Automate Staff

Re: Update a secondary list which has a common value.

@v-yamao-msft Thank you Mabel, you're awesome. We did already get it to work. What I was wondering is what is the logic around using ? and Item() with the subtraction function (sub())? I'd like to understand better why those where needed in this case, rather than using the dynamic field and simply using [dynamic tag - 1]. If I understand the logic better ('logic' or 'why' rather than steps). I would love to do a webinar to clarify this topic for others.

 

Thank you again,

Audrie

Helpful resources

Announcements
firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (6,098)