Power Platform Community Forum Thread Details

Hi,

I have two Lists, one shows the allocated funding amount to a particular person and the second list shows funding requests for that allocation and goes through an approval workflow. There can be multiple funding requests from a person as well.

I am trying to figure out a way to show how much of a persons allocated funding remains after each requested item has been approved?

From what I have read, you can't reference look up columns in Lists so I am looking into Power Platform options instead, does anyone know if this could be done with a flow? I have looked into a Power BI report with a DAX calculated column but it would be better if the $$ remaining could be shown within either of the lists rather than a separate report.

TIA

Categories:

General topics

Question: Where is the remaining stored? In List 1, or List 2?

So let's say in your List 1, where you have the allocation. You have a second column for whats left.

Now, let's say in List 2, you have a Column that makes it clear what Request(s) go together, so when a new record is created, you can easily "group by" that column or set of columns

1) Create an Automate Flow

2) use the When an Item is Created or modified

--if you do not expect updates to these requests then you can create a Trigger condition that ONLY does anything when its a create. If however, you need it to fire when an item is modified, then you can use the Has Changed.Along with Get Changes, to see if the items "request" number has changed and if not exit the flow.

3) assuming you got past steps 1/2, now you will do a Get Items from Sharepoint, where you will filter by the ID or whatever column is used to group the items together, You would only need to pull back the request amount (assuming approved)

4) do a loop to add the individual requests into a single number

5) Update the single line item in List 1, where the ID/Request/whatever you have in THIS (flow 2) to match it in List 1.

-use an update time, to update the Allocation Remaining Column

And done

You could even make it so that before you even save the Request, you could get the one row from the List 1, then take your number column number and add the new ask.. and if its over the allocation just deny it.. (you'd want that to exist in the one where the Approval is NOT this flow)