Updating stock quantity

[OneSky]

Hi there - I'm fairly new to Power Apps and Power Automate but have searched the forums and haven't been able to piece together a solution for the issue I'm having.

I have built a model driven inventory management app using Dataverse. I have two tables: Inventory and Orders. In the Inventory table, I have a column called "StockQuantity". In the Orders table, I have several columns that indicate the quantity of an item to be ordered - they are named "Quantity1", "Quantity2", Quantity3", etc. I have established a Many-to-one relationship between the Order table and the Inventory table via an "Item" lookup column in the Order table.

When the user places an order for a certain quantity of an item by adding a value in the "Quantity" column, I would like to subtract that value from the total number in stock for that item ("StockQuantity") using a Power Automate flow.

I have tried to create a flow by using the "When a row is added, modified or deleted" trigger and assigning the corresponding "Quantity" column from the Orders table. However, when I go to add an action to "Get a row by ID" and select the "Inventory" table, the only dynamic content it allows me to choose from are only columns on the Order table, not the inventory?

I'm also struggling with how to use the Compose action to write the equation that would make this function work.  😞

Any help or ideas would be greatly appreciated! Many thanks in advance!

[Jmanriquerios]

Hi @OneSky 

Hello, here in this forum they asked almost the same thing as you, there you have the flow and how they solved it

Solved: How update Stock with power Automate? - Power Platform Community (microsoft.com)

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
You can accept more than one post as a solution

[OneSky]

  

 

 

Hi @Jmanriquerios - thank you for your reply and posting that link!

I have updated the flow to match that of the link you sent, but I'm still getting an error in the final "Update a row" step?

Thank you again for your help!

[OneSky]

 

[Jmanriquerios]

Hi @OneSky 

Nothing happens, as long as the columns are not mandatory, I tell you the same thing, you should point to the correct option set, which we patch an option set normally after writing the name of the option set we must place period (.) and select the value

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
You can accept more than one post as a solution

[OneSky]

Hi @Jmanriquerios - I'm not sure that I can use the 'Patch' function as this is a model driven app?

[Jmanriquerios]

Hi @OneSky 

You can use patch as long as you use the modern command bar.

Regarding the operation of your flow, could you show me not the error but the construction to see where the error may be?

There is a perhaps simpler way but there are many who do not like it, which is to use rollup type columns.

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
You can accept more than one post as a solution

[OneSky · Accepted solution]

Hi @Jmanriquerios  - thank you so much for your reply and your suggestions!

I was able to actually find a fairly simple flow solution for my issue - it was way more straightforward than what I was originally making it to be.

Posting screenshots below in case it helps folks in the future.

Equation is: