Update a SharePoint list with sum retrieved from another SharePoint list

[mthiru_0023]

I have a flow built to get the sum of column for each FirmFileNo on a Sharepoint list. I need to some help to complete this flow to get the sum from this sharepoint list and update the row on a sharepoint list for the firmfileno. 

This is the current flow setup:

I have a variable to keep track of the FirmFileNo.

I have a variable to store sum of total fees incurred to date for the current firm file no.

Get items  to retrieve all items from the sharepoint list.

condition to check if the FirmFileNo in the current item is different from the CurrentFirmfileNo.

If yes,

   Set variable to set the 'FileTotal' variable to 0. 

   Another set variable, to update the 'CurrentFirmFileNo' variable with the current Firmfileno.

Outside the condition, an action to increment the 'FileTotal' vriable with the total fees incurred t date.

please help me to update another SharePoint "FileSummary" list column for each FirmFileNo row with the Sum.

[Matthy79]

Hello @mthiru_0023 

Please provide sample JSONs (anonymize values and please text and not screenshots) of both lists and how they are connected.

Apply to each ist not a good way to get or transform data. Just use it for the update part.

[mthiru_0023]

@Matthy79 

Here is an output from the Invoice master (Get items action). The FileSummaryID is equal to the ID from Invoice master.

"body": {
"value": [
{
"@odata.etag": "\"4\"",
"ItemInternalId": "67",
"ID": 67,
"Title": "Invoice",
"FileSummaryID": "24",
"field_1": "HP123",
"field_2": "Hepner & Myers LLP",
"field_3": "SFR-Lit-0",
"field_5": "SFR",
"field_6": "123 Florida ave",
"field_7": "detailsxx",
"field_8": "invmt",
"field_9": "2023-03-31",
"field_10": "invmt",
"field_11": "USD",
"field_12": 1500,
"field_13": 1520,

I've been trying different tests and variations to get this work but above I don't have "Get Items action for File Summary list. I have it another test so I can provide some of the json. 

Invoice Master: FirmFileRefNo": "AB150069" should match with field_1 in the invoice list.

"body": {
"value": [
{
"@odata.etag": "\"1\"",
"ItemInternalId": "1",
"ID": 1,
"Title": "150069",
"FirmFileRefNo": "AB150069",
"FirmName": "Aird & Berlis",
"V2Text": "CMF",
"MatterTitle": "Property_Operations",
"Submatter": "Other",
"MatterDesc": "General Matters",
"BillingEntity": "592 Sher",
"AssignedAPDept": {
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
"Id": 1,
"Value": "Ttin"
},

[Matthy79]

Hello @mthiru_0023,

the provided JSONs are invalid and I just need the fields that are needed for this topic.

Which field needs to be summarized?

Is it possible that there are IDs in one list and not in the other? What should happen for both cases?

If you provide data I can work with, I will provide a working solution that you can easily integrate. If that is not possible, I will provide a solution with dummy fields and you will have to do some changes to make it work on your side.

[mthiru_0023]

@Matthy79 , sorry about that.. yes, any example will help me thank you!.  I've been testing with various other lists and am now super confused.

If I remember correctly, we create an invoice against a FileNo. A file is a unique record in the FileSummary list, so something like "AG00123".

We could have multiple Invoices with the same File no. "AG00123" in the Invoice Master.  Field 12 is gross before tax and field 13 is Total with Tax. We want to Total amount including tax. We want to summarize Field_13.

("field_12": 1500, "field_13": 1520)

Initially, I thought I had the ID field from FileSummary in the Invoice master as (FileSummaryID). I may have not gotten that record appended in the production version. I may try to use the patch function in PowerApps if I can remember how I created this business process a while back.  However, a File reference number is referenced in the Invoice Master that always ties up to the FileNo in the File Summary List.

[Matthy79 · Accepted solution]

Hello @mthiru_0023,

this is how I would do it.

1.) Create an xml of Invoice Master

2.) Select FileSummaryList and use xpath to sum the values.

Now you have a new array with sum values and you can do whatever you want with it. (Apply to each and update the new value for example).

Here is a sample flow you can past in a dummy flow to see how it works:

{"id":"6d756e21-4f36-4c99-92e0-85cd0dd45dfe","brandColor":"#8C3900","connectionReferences":{"shared_commondataserviceforapps":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_commondataserviceforapps/connections/62947705b1a746319ca1e59aae985afc"}},"shared_office365":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_office365/connections/1597557c9920416e9a930edfee990646"}},"shared_flowmanagement":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_flowmanagement/connections/shared-flowmanagemen-276a16fd-e558-4db2-8c1d-8f153142d8f4"}},"shared_powerappsforappmakers":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_powerappsforappmakers/connections/shared-powerappsfora-72ad6d4c-b73e-4db7-a2ac-66bed864efe8"}},"shared_converterbypower2apps":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_converterbypower2apps/connections/shared-converterbypo-168dd966-d321-48d6-ae65-2bb3363fc29f"}},"shared_sharepointonline":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/shared-sharepointonl-b959cb53-5ecb-4b41-805f-06e7795a0bd2"}}},"connectorDisplayName":"Control","icon":"data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"Scope_Sum","operationDefinition":{"type":"Scope","actions":{"Compose_FileSummaryList":{"type":"Compose","inputs":[{"ID":"AG00123","Sum":5},{"ID":"AG00124","Sum":5},{"ID":"AG00125","Sum":5}],"runAfter":{}},"Compose_Invoice_Master":{"type":"Compose","inputs":[{"LinkID":"AG00123","Amount":123},{"LinkID":"AG00123","Amount":14},{"LinkID":"AG00123","Amount":28},{"LinkID":"AG00124","Amount":7},{"LinkID":"AG00124","Amount":18},{"LinkID":"AG00125","Amount":30}],"runAfter":{"Compose_FileSummaryList":["Succeeded"]}},"Compose_Invoice_Master_xml":{"type":"Compose","inputs":"@xml(json(concat('{ \"root\": { \"rows\": ', outputs('Compose_Invoice_Master'), ' }}')))","runAfter":{"Compose_Invoice_Master":["Succeeded"]}},"Select_Sums":{"type":"Select","inputs":{"from":"@outputs('Compose_FileSummaryList')","select":"@addProperty(item(), 'NewSum', xpath(outputs('Compose_Invoice_Master_xml'), concat('sum(root/rows[LinkID=\"', item()['ID'], '\"]/Amount)')))"},"runAfter":{"Compose_Invoice_Master_xml":["Succeeded"]}}},"runAfter":{}}}

[mthiru_0023]

@Matthy79 , How do I import this flow? Would you mind sharing some screenshot or steps and I can follow it and play with it,

[Matthy79]

In this sample you have all the steps.

Just copy and paste it in a dummy flow by using “Add action” (clipboard) and hit “Ctr+v”.

[mthiru_0023]

@Matthy79 I understand now, I was able to do that. A lot of videos online reference the xpath and I was able to recreate those steps and get the Sum of values from the Invoice master. My challenge is to get this Invoice Master sum and updated the list in File Summary. Instead of the compose, how would you do it with a Get items action or whatever for the two lists?

[mthiru_0023]

@Matthy79 , should I use the Get Items action from the two lists and use the outputs in a compose or variable array? This is where I get lost.

[Matthy79]

You don’t have to put anything in a variable or compose. Just use the one file to create the xml (you will only have to change the part where I used the compose statement to your items array).

And in the select action you use your array (values) of the list where you have your sums. I am not at the computer right now but try to do the modifications and post the flow and maybe an error message.

[mthiru_0023]

@Matthy79  can you help. I am testing this one sample test versions. The flow ran but It updated only one row correctly - GM191647 for 926 for this one file had only one invoice. Other files with multiple records did not or have Nan value. 

Filesummary:

Invoice:

Here is the construction of the flow:

For the compose FileSummary List and Invoice Master - inputs are Value from the Get items file summary and Invoices respectively.

Compose Invoice Master xml: 

xml(json(concat('{ "root": { "rows": ', outputs('Compose_Invoice_Master'), ' }}')))

Selects sums:

Map equals: 

addProperty(item(), 'NewSum', xpath(outputs('Compose_Invoice_Master_xml'), concat('sum(root/rows[FileSummaryID="', item()['ID'], '"]/InvoiceAmtTotal)')))

Afterwards, I parse json and update item on sp list.

[Matthy79]

Have you checked all outputs using the flowrun history?

Are there always number values in the fields to sum?

How does the select output look like?

Is it possible to share the output of the list where the numbers are that have to be summarized?

[mthiru_0023]

@Matthy79 Sorry, I forget to mention in the Parse Json I had update the schema for NewSum from string to number as it was erroring out.

},

            "NewSum": {

                "type": "number"

            },

Yes, it looks like it is reading as a number value for the sum field.

Yes, here the sample from the outputs from Compose Invoice master:

[
{
"@odata.etag": "\"3\"",
"ItemInternalId": "92",
"ID": 92,
"Title": "AB141258",
"InvoiceNo": "1332916",
"InvocieAmount_x0028_pre_x002d_ta": "3,175.37",
"FileSummaryID": "13",
"Total": 3588.17,
"InvoiceAmtTotal": "3,588.17",
"Modified": "2023-09-26T13:30:34Z",
"Created": "2023-09-23T16:43:58Z",
"Author": {
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
"Claims": "i:0#.f|",
"DisplayName": "Thiru",
"Email": "mthiru@",
"Picture": .com/sites/sitetest/_layouts/15/UserPhoto.aspx?Size=L&AccountName=",
"Department": "Innovation & Technology",
"JobTitle": "IT Analyst"
},
"Author#Claims": "i:0#.f|membership|",
"Editor": {
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
"Claims": "i:0#.f|membership|",
"DisplayName": "",
"Email": "mthiru@.com",
"Picture": "/sites/sitetest/_layouts/15/UserPhoto.aspx?Size=L&AccountName=",
"Department": "Innovation & Technology",
"JobTitle": "IT Analyst"
},
"Editor#Claims": "i:0#.f|membership|.com",
"{Identifier}": "Lists%252fInvoiceCopy2%252f92_.000",
"{IsFolder}": false,
"{Thumbnail}": {
"Large": null,
"Medium": null,
"Small": null
},
"{Link}": "/sites/sitetest/_layouts/15/listform.aspx?PageType=4&ListId=a277f61e%2De789%2D4ba2%2Daac6%2Db6e2e97d39da&ID=92&ContentTypeID=0x0100528BF8F8BF94044B9BE7961309C2226E00715D424BF7AB2F408FC3A5DCD697FB70",
"{Name}": "AB141258",
"{FilenameWithExtension}": "AB141258",
"{Path}": "Lists/InvoiceCopy2/",
"{FullPath}": "Lists/InvoiceCopy2/92_.000",
"{HasAttachments}": false,
"{VersionNumber}": "3.0"
},

[Matthy79]

You are trying to sum a string. I think that could be the problem because there are commas in the numbers (format for 1000). During summary this is a problem. I can’t test it right now but why aren’t you using „Total“? This seems to be a number with the same value. Of course you could convert the string to number.

[mthiru_0023]

@Matthy79 , I may have been referencing the wrong column to sum. I think I got it. thanks

[mthiru_0023]

@Matthy79 , I am having some problems withe parse json in the prod version. There is alot of missing values so it doesn't like it. Do you know if there is any way around that? I just want to update take the Sum and push to File Summary list as a (one time catch up) update.

{
"message": "Invalid type. Expected String but got Null.",
"lineNumber": 0,
"linePosition": 0,
"path": "[1].Author.Department",
"schemaId": "#/items/properties/Author/properties/Department",
"errorType": "type",
"childErrors": []
},
{
"message": "Required properties are missing from object: Partners, Partners#Id.",
"lineNumber": 0,
"linePosition": 0,
"path": "[1]",
"value": [
"Partners",
"Partners#Id"
],
"schemaId": "#/items",
"errorType": "required",
"childErrors": []
},
{
"message": "Invalid type. Expected String but got Null.",
"lineNumber": 0,
"linePosition": 0,
"path": "[2].Author.Department",
"schemaId": "#/items/properties/Author/properties/Department",
"errorType": "type",
"childErrors": []
},
{
"message": "Invalid type. Expected String but got Null.",
"lineNumber": 0,
"linePosition": 0,
"path": "[3].Author.Department",
"schemaId": "#/items/properties/Author/properties/Department",
"errorType": "type",
"childErrors": []
},
{
"message": "Invalid type. Expected String but got Null.",
"lineNumber": 0,
"linePosition": 0,
"path": "[4].Author.Department",
"schemaId": "#/items/properties/Author/properties/Department",
"errorType": "type",
"childErrors": []

[Matthy79]

Why do you even use parse json? Because it is easier to use dynamic content? I wouldn’t use it for that case but if you want you can delete all the fields from the schema you don’t use.