Add or delete Rows from Excel based on Sharepoint Value

[VincentGiovanni]

Hello Great Community,

I've a warehouse where we manage over 2 thousand unique articles, I've created a PowerApp where our collegues (over 100 workers enter our warehouse daily to retrieve items) can scan out items through QR-Codes that are physically on each storage box.

My Inventory Sharepoint List consists of these Columns

• ArticleNumber (Single Line of Text)
• ProductName (Single Line of Text)
• Quantity (Number)
• Location (Choice)
• Qr-code (Attachment)
• Price ( Currency)
• Total Value ( Defined Column)
• StringQrKod (Single Line Of Text)

Now I want to create a flow whenever the Quantity column cells are below 20 for example, Export that Row to an Excel File That has the same columns as the Sharepoint List except the QR-code and Total Value columns But the excel file has an extra column called Date which is the timestamp when the flow was executed so that we dont re-order the same item twice due to an extra scan when it was below 20.

Thereafter when we refill those Items so that the quantity exceeds 20 in the SP List, I want the rows to automatically be removed.

Is there an Efficient way to approach this, I'm no expert on flow and are trying to learn more whenever I've time.

Best Regards

Vincent

[ScottShearer]

@VincentGiovanni 

Here is one approach:

1. Add a column to the Excel table for the list item ID
2. Use a When an item or file is modified trigger
3. When the item is modified, check the quantity
4. If below 20
   1. Use a Get rows from a table action with an OData filter to retrieve the row (if any) in the Excel table with that contains the list item ID
   2. If no rows exist, add one
   3. If a row already exists, do nothing

Let me know if you need additional help.

[VincentGiovanni]

Hello Scott,

And thank you for the quick response, I'll try this straight away!

Just to be clear, in step 3 do you mean that's where I'll insert a condition control for the IF statement in Step 4 ?

Best Regards

Vincent

[ScottShearer]

Correct!

If you have issues, please post a screen shot of your Flow and I will try to assist.

[VincentGiovanni]

Something like this? 

[ScottShearer]

@VincentGiovanni 

Almost there!

The filter query in the list rows action should probably look like this:

ID eq [Insert reference to ID from Dynamic properties here]

Don't include the brackets

If you get an error of the query, put single quotes around the ID from Dynamic properties.

[VincentGiovanni]

Hello Scott!

Now the Flow ran successfully but nothing was populated in the Excel File.

I tried with and without the single quotes and both ran successfully but neither one inserted data in the Excel file.