Power Automate and two SharePoint online lists

(0) Share

Report

Posted on by Community Power Pla...

I am fairly new to SharePoint online and Power Automate and looking for a solution to do the following:

I have two lists

List 1: Budget name, Expense Code, Allowance and Total Budget available.
List 2: Item Name, Amount, Expense Code

I'm looking at a way for list one to be updated, with the value from amount on List 2 subtracted based on the expense code used.

For example:

If I input one laptop at £200 using expense code EA4 then List one will update to have that amount removed from the budget name & amount and figure populated in total budget available.

Any suggestions or guidance as I've been looking but can't seem to figure it out or find a way.

Categories:

Using flows

I have the same question (0)

All responses (13)

Answers (1)

v-LilyW-msft on at

Like (1)

Report

Hi @Anonymous
Do you want to remove the value of Budget name and fill Total Budget available with the value that Budget name subtract Amount in list2 when list2 update?
If any misunderstanding, please kindly let me know.
I simply created a Flow, you can refer to as below:
This is my list1
This is my list2
Flow in details:
Expression in Budget name: null
Expression in Total Budget available: sub(items('Apply_to_each')?['Budgetname'],triggerOutputs()?['body/Amount'])
When the Expense Code EA4 be modified as below:
List1 will update as below:
Hope the content above may help you.
Best Regards
If my solution helps, then please consider Accept it as the solution to help the other members find it more quickly.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Thank you that worked wonders! 🙂

However i do have a small issue
In List2 if i have two items using expense code 111 it will only deduct one of those amounts from the balance on List1.
How would you recommend i edit it so any / all items using expense code 111 are taken from the balance?

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

HI,

So i've been working on this and while it's close to what i'm after it's not quite there so wondered if you could please assist a little further.

I would like the flow to update list 2, Total remaining, to show the balance after every item with expense code EA4 is removed from that balance, for example:

List 1:
EA4 Laptop £200
EA4 TV £200

List2:
EA4 Electronic (Budget Name) Allowance: £1000 Total Remaining: £600

Hope that makes sense

Was this reply helpful? Yes No
v-LilyW-msft on at

Like (0)

Report

Hi @Anonymous
Do you want to calculate the sum of all EA4 in list2, then subtract the sum from the name in list1 and fill it in Total Budget available when a new item is created?
If yes, please refer to as below:
Expression: sub(items('Apply_to_each_2')?['Budgetname'],variables('amount'))
Hope the content above may help you.
Best Regards
If my solution helps, then please consider Accept it as the solution to help the other members find it more quickly.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Hey - Thank you for providing the above, are you able to share a screenshot of the full flow so i can make sure mine alligns up

Was this reply helpful? Yes No
Verified answer

v-LilyW-msft on at

Like (1)

Report

Hi @Anonymous
The full flow screenshot as below:
Best Regards

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Hiya,

That's brilliant, i think it's almost there just one final thing - Budget name needs to be a word i.e Home Budget. I changed the Sub query from BudgetName to Allowance and tried to change the column of budget name to a single line of text but get the following:

Was this reply helpful? Yes No
v-LilyW-msft on at

Like (0)

Report

Hi @Anonymous
Please try delete Get Items and create the action again, Then save the flow and run again.
Best Regards

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Thanks that's worked great and is everything i needed, i really appreciate your help and support with this.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Hi,

For some reason i now have a slight issue.

When i add the following entries to List 2, it's causing a problem
As you can see it's taken £100 off all rows in list 1 not just the row with BN01

Any advice

Was this reply helpful? Yes No