cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
darogael
Most Valuable Professional
Most Valuable Professional

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

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
v-yamao-msft
Community Support
Community Support

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.
darogael
Most Valuable Professional
Most Valuable Professional

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.

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.
darogael
Most Valuable Professional
Most Valuable Professional

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?

darogael
Most Valuable Professional
Most Valuable Professional

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.

darogael
Most Valuable Professional
Most Valuable Professional

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

darogael
Most Valuable Professional
Most Valuable Professional

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?

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.
darogael
Most Valuable Professional
Most Valuable Professional

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
Most Valuable Professional
Most Valuable Professional

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.

 

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.
darogael
Most Valuable Professional
Most Valuable Professional

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

 

darogael
Most Valuable Professional
Most Valuable Professional

Mabel,

Any updates?

Audrie-MSFT
Copilot Studio
Copilot Studio

@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

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

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

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

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.

@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

Exclusive LIVE Community Event: Power Apps Copilot Coffee Chat with Copilot Studio Product Team

It's time for the SECOND Power Apps Copilot Coffee Chat featuring the Copilot Studio product team, which will be held LIVE on April 3, 2024 at 9:30 AM Pacific Daylight Time (PDT).     This is an incredible opportunity to connect with members of the Copilot Studio product team and ask them anything about Copilot Studio. We'll share our special guests with you shortly--but we want to encourage to mark your calendars now because you will not want to miss the conversation.   This live event will give you the unique opportunity to learn more about Copilot Studio plans, where we’ll focus, and get insight into upcoming features. We’re looking forward to hearing from the community, so bring your questions!   TO GET ACCESS TO THIS EXCLUSIVE AMA: Kudo this post to reserve your spot! Reserve your spot now by kudoing this post.  Reservations will be prioritized on when your kudo for the post comes through, so don't wait! Click that "kudo button" today.   Invitations will be sent on April 2nd.Users posting Kudos after April 2nd at 9AM PDT may not receive an invitation but will be able to view the session online after conclusion of the event. Give your "kudo" today and mark your calendars for April 3, 2024 at 9:30 AM PDT and join us for an engaging and informative session!

Tuesday Tip: Unlocking Community Achievements and Earning Badges

TUESDAY TIPS are our way of communicating helpful things we've learned or shared that have helped members of the Community. Whether you're just getting started or you're a seasoned pro, Tuesday Tips will help you know where to go, what to look for, and navigate your way through the ever-growing--and ever-changing--world of the Power Platform Community! We cover basics about the Community, provide a few "insider tips" to make your experience even better, and share best practices gleaned from our most active community members and Super Users.   With so many new Community members joining us each week, we'll also review a few of our "best practices" so you know just "how" the Community works, so make sure to watch the News & Announcements each week for the latest and greatest Tuesday Tips!     THIS WEEK'S TIP: Unlocking Achievements and Earning BadgesAcross the Communities, you'll see badges on users profile that recognize and reward their engagement and contributions. These badges each signify a different achievement--and all of those achievements are available to any Community member! If you're a seasoned pro or just getting started, you too can earn badges for the great work you do. Check out some details on Community badges below--and find out more in the detailed link at the end of the article!       A Diverse Range of Badges to Collect The badges you can earn in the Community cover a wide array of activities, including: Kudos Received: Acknowledges the number of times a user’s post has been appreciated with a “Kudo.”Kudos Given: Highlights the user’s generosity in recognizing others’ contributions.Topics Created: Tracks the number of discussions initiated by a user.Solutions Provided: Celebrates the instances where a user’s response is marked as the correct solution.Reply: Counts the number of times a user has engaged with community discussions.Blog Contributor: Honors those who contribute valuable content and are invited to write for the community blog.       A Community Evolving Together Badges are not only a great way to recognize outstanding contributions of our amazing Community members--they are also a way to continue fostering a collaborative and supportive environment. As you continue to share your knowledge and assist each other these badges serve as a visual representation of your valuable contributions.   Find out more about badges in these Community Support pages in each Community: All About Community Badges - Power Apps CommunityAll About Community Badges - Power Automate CommunityAll About Community Badges - Copilot Studio CommunityAll About Community Badges - Power Pages Community

Tuesday Tips: Powering Up Your Community Profile

TUESDAY TIPS are our way of communicating helpful things we've learned or shared that have helped members of the Community. Whether you're just getting started or you're a seasoned pro, Tuesday Tips will help you know where to go, what to look for, and navigate your way through the ever-growing--and ever-changing--world of the Power Platform Community! We cover basics about the Community, provide a few "insider tips" to make your experience even better, and share best practices gleaned from our most active community members and Super Users.   With so many new Community members joining us each week, we'll also review a few of our "best practices" so you know just "how" the Community works, so make sure to watch the News & Announcements each week for the latest and greatest Tuesday Tips!   This Week's Tip: Power Up Your Profile!  🚀 It's where every Community member gets their start, and it's essential that you keep it updated! Your Community User Profile is how you're able to get messages, post solutions, ask questions--and as you rank up, it's where your badges will appear and how you'll be known when you start blogging in the Community Blog. Your Community User Profile is how the Community knows you--so it's essential that it works the way you need it to! From changing your username to updating contact information, this Knowledge Base Article is your best resource for powering up your profile.     Password Puzzles? No Problem! Find out how to sync your Azure AD password with your community account, ensuring a seamless sign-in. No separate passwords to remember! Job Jumps & Email Swaps Changed jobs? Got a new email? Fear not! You'll find out how to link your shiny new email to your existing community account, keeping your contributions and connections intact. Username Uncertainties Unraveled Picking the perfect username is crucial--and sometimes the original choice you signed up with doesn't fit as well as you may have thought. There's a quick way to request an update here--but remember, your username is your community identity, so choose wisely. "Need Admin Approval" Warning Window? If you see this error message while using the community, don't worry. A simple process will help you get where you need to go. If you still need assistance, find out how to contact your Community Support team. Whatever you're looking for, when it comes to your profile, the Community Account Support Knowledge Base article is your treasure trove of tips as you navigate the nuances of your Community Profile. It’s the ultimate resource for keeping your digital identity in tip-top shape while engaging with the Power Platform Community. So, dive in and power up your profile today!  💪🚀   Community Account Support | Power Apps Community Account Support | Power AutomateCommunity Account Support | Copilot Studio  Community Account Support | Power Pages

Super User of the Month | Chris Piasecki

In our 2nd installment of this new ongoing feature in the Community, we're thrilled to announce that Chris Piasecki is our Super User of the Month for March 2024. If you've been in the Community for a while, we're sure you've seen a comment or marked one of Chris' helpful tips as a solution--he's been a Super User for SEVEN consecutive seasons!   Since authoring his first reply in April 2020 to his most recent achievement organizing the Canadian Power Platform Summit this month, Chris has helped countless Community members with his insights and expertise. In addition to being a Super User, Chris is also a User Group leader, Microsoft MVP, and a featured speaker at the Microsoft Power Platform Conference. His contributions to the new SUIT program, along with his joyous personality and willingness to jump in and help so many members has made Chris a fixture in the Power Platform Community.   When Chris isn't authoring solutions or organizing events, he's actively leading Piasecki Consulting, specializing in solution architecture, integration, DevOps, and more--helping clients discover how to strategize and implement Microsoft's technology platforms. We are grateful for Chris' insightful help in the Community and look forward to even more amazing milestones as he continues to assist so many with his great tips, solutions--always with a smile and a great sense of humor.You can find Chris in the Community and on LinkedIn. Thanks for being such a SUPER user, Chris! 💪 🌠  

Find Out What Makes Super Users So Super

We know many of you visit the Power Platform Communities to ask questions and receive answers. But do you know that many of our best answers and solutions come from Community members who are super active, helping anyone who needs a little help getting unstuck with Business Applications products? We call these dedicated Community members Super Users because they are the real heroes in the Community, willing to jump in whenever they can to help! Maybe you've encountered them yourself and they've solved some of your biggest questions. Have you ever wondered, "Why?"We interviewed several of our Super Users to understand what drives them to help in the Community--and discover the difference it has made in their lives as well! Take a look in our gallery today: What Motivates a Super User? - Power Platform Community (microsoft.com)

March User Group Update: New Groups and Upcoming Events!

  Welcome to this month’s celebration of our Community User Groups and exciting User Group events. We’re thrilled to introduce some brand-new user groups that have recently joined our vibrant community. Plus, we’ve got a lineup of engaging events you won’t want to miss. Let’s jump right in: New User Groups   Sacramento Power Platform GroupANZ Power Platform COE User GroupPower Platform MongoliaPower Platform User Group OmanPower Platform User Group Delta StateMid Michigan Power Platform Upcoming Events  DUG4MFG - Quarterly Meetup - Microsoft Demand PlanningDate: 19 Mar 2024 | 10:30 AM to 12:30 PM Central America Standard TimeDescription: Dive into the world of manufacturing with a focus on Demand Planning. Learn from industry experts and share your insights. Dynamics User Group HoustonDate: 07 Mar 2024 | 11:00 AM to 01:00 PM Central America Standard TimeDescription: Houston, get ready for an immersive session on Dynamics 365 and the Power Platform. Connect with fellow professionals and expand your knowledge. Reading Dynamics 365 & Power Platform User Group (Q1)Date: 05 Mar 2024 | 06:00 PM to 09:00 PM GMT Standard TimeDescription: Join our virtual meetup for insightful discussions, demos, and community updates. Let’s kick off Q1 with a bang! Leaders, Create Your Events!  Leaders of existing User Groups, don’t forget to create your events within the Community platform. By doing so, you’ll enable us to share them in future posts and newsletters. Let’s spread the word and make these gatherings even more impactful! Stay tuned for more updates, inspiring stories, and collaborative opportunities from and for our Community User Groups.   P.S. Have an event or success story to share? Reach out to us – we’d love to feature you!

Users online (5,194)