Update a secondary list which has a common value.

[darogael]

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.

[v-yamao-msft]

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.

If you need more help, please feel free reply.

Best regards,
Mabel Mao

[darogael]

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.

[v-yamao-msft]

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.

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”.

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

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

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

[darogael]

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

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

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

Any thoughts? Ideas?

[darogael]

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

Here is a screenshot of what I get in my email

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

[darogael]

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

[darogael]

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?

[v-yamao-msft]

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:

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

[darogael]

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.

[darogael]

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.

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

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

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

Please advice next steps.

[v-yamao-msft]

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

[darogael]

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

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

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

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

[darogael]

Mabel,

Any updates?

[Audrie-MSFT]

@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

[Audrie-MSFT]

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 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

[Audrie-MSFT]

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 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

[audrieg · Accepted solution]

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

[v-yamao-msft]

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:

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”.

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.

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.

Best regards,
Mabel Mao

[Audrie-MSFT]

@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