Sum if two fields in separate SharePoint lists match using Sum(ShowColumns(Filter()

[MitchC3]

New to PowerApps-

I have two SharePoint lists that have a Master-Detail relationship. The master has an ID column that SharePoint generates. The detail has a lookup column to select that ID if the line item is a member of that order.

On the detail screen of the master in my app, I want to show the sum of all extended price fields (in the detail list) for that master item. As far as I know, I am just missing a functioning logical test-

Sum(ShowColumns(Filter('Name of detail list here', true = true), "Extended_x0020_Price"),Extended_x0020_Price)

This returns a sum of all extended prices currently in my detail list. I have tried every variant of if ID = lookupID for the logical test that I can think of, but get invalid argument type or cannot evaluate remotely errors. I need only the sum of extended prices for the items that have a lookup ID that match the ID of currently viewed master detail screen item.

I also need to write this value back to the master list in SharePoint, but I will come across that bridge when I get there.

[v-xida-msft · Accepted solution]

Hi @MitchC3,

Could you please share more details about your two SharePoint lists?

Further, could you please show a bit more about your app's configuration?

I have made a test on my side, please take a try with the following workaround:

Within Display Form of the Detail Screen of the master in my app, I add a Custom DataCard control within the Display Form as above.

Within the Custom DataCard control, add two Label controls. The Text property of the first Label control set to following formula:

"Sum of Price"

The Text property of the second Label control set to following formula:

Sum(Filter('20180618_case1_Details',Product_x0020_Category.Value=Text(ThisItem.ID)),Extended_x0020_Price)

or 

Sum(ShowColumns(Filter('20180618_case1_Details',Product_x0020_Category.Value=Text(ThisItem.ID)),"Extended_x0020_Price"),Extended_x0020_Price)

Note: The '20180618_case1_Details' represents the name of detail list on my side, the Product Category is a Lookup type column in my Detail list, the Extended Price is a Number type column in my Detail list.

On your side, you should type the following formula:

Sum(ShowColumns(Filter('Name of detail list here',LookUpColumn.Value=Text(ThisItem.ID)), "Extended_x0020_Price"),Extended_x0020_Price)

Note: I assume the LookUpColumn is the Lookup type column in your Detail list.

More details about adding a Custom DataCard in Display Form (Data Form), please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/working-with-cards#customize-a-card

Best regards,

Kris

[MitchC3]

Thank you, I changed my lookup column to a number field and found a different workaround. I've realized a lookup column for my purposes would become to lengthy anyway.