Answered

Sum column by unique id and send to another sharepoint list

(0) Share

Report

Posted on by jarkins

I have two separate sharepoint lists. List "A" shows all invoices received to date and references a TacticID that is unique to a specific deliverable. This list has multiple invoice "Amounts" per deliverable ("TacticID").

Sharepoint list "B" has more detail related to each TacticID (i.e. deliverables and timelines) including a column for total amount of invoices received.

I am trying to sum all of the invoice "Amount" values for a given "TacticID" from "List A" to the "Total Invoice" column of the matching "TacticID" on "List B". Ideally each deliverable in "List B" would have the corresponding invoice totals showing up in the "Total Invoice" column of "List B".

I have been trying to make this happen for a couple of days now, but I have not found any good solution to it. Any help would be greatly appreciated

Categories:

Building flows

I have the same question (0)

All responses (9)

Answers (1)

Verified answer

v-yujincui-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @jarkins ,

I have made simple test for your reference.

1. Here are my lists.
2. Here are my Flow.
1. Get all TacticID values and de-duplicate them.
2. Sum the Amount column for all records corresponding to each TacticID.
3. Find the corresponding Item ID for each TacticID in another list.
4. Update the value of the Total Invoice column in another list.

union(variables('TacticIDArray'),variables('TacticIDArray'))
TacticID eq '@{item()}'
first(outputs('Get_items_3')?['body/value'])?['ID']

Result Screenshots:

Best Regards,
Charlie Choi

Was this reply helpful? Yes No
jarkins 10 on at

Like (0)

Report
Copy link

Link copied!

thank you @v-yujincui-msft. I have tried the flow you suggested, but keep running into the error below. I ended up changing a few column names from the original request (ie TacticID in invoices to Tactic), but I am still running into the error.

Here is the flow I used:

Any help would be greatly appreciated. Thanks

Was this reply helpful? Yes No
v-yujincui-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @jarkins ,

Based on the error details, it seems that you are using a wrong internal column name.
You could find the internal column names by clicking on the list settings.

Best Regards,
Charlie Choi

Was this reply helpful? Yes No
jarkins 10 on at

Like (0)

Report
Copy link

Link copied!

Thanks @v-yujincui-msft . That was my initial assumption as well and what led me to change the column name from TacticID to Tactic. The error references a failure in Get_items_2 which is related to "InvoiceTracker".

However, when I go into list details as you suggested, I see a column labeled "Tactic".
the Filter query used in Get_items_2 is Tactic eq '@{item()}'

Was this reply helpful? Yes No
v-yujincui-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @jarkins ,

You could click on it and see the address bar at the top of the page.

Best Regards,
Charlie Choi

Was this reply helpful? Yes No
jarkins 10 on at

Like (0)

Report
Copy link

Link copied!

Yes, the column name is "Tactic", so I do not understand the error being generated that says the column name cannot be found.

The address bar has:

Was this reply helpful? Yes No
v-yujincui-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @jarkins ,

Based on the address bar your provided, field_2 is the internal column name of your Tactic column.

Best Regards,
Charlie Choi

Was this reply helpful? Yes No
v-yujincui-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @jarkins ,

Is your problem solved?

Best Regards,
Charlie Choi

Was this reply helpful? Yes No
jarkins 10 on at

Like (0)

Report
Copy link

Link copied!

All set and works now. Thank you for all your help

Was this reply helpful? Yes No