Solved: How can I sum by multiple columns in Grandchild ta... - Page 2

Griff · 03-03-2021 08:13 PM

My tables are structured as follows: Order (Parent) > Daysheet (child) > Materials Used (Grandchild)
A Daysheet may have related Materials Used of the same product entered multiple times and the same Materials Used may also be linked to numerous Day Sheets.
When an Order is closed I need to send an email that can be used for data entry into an accounting system so it needs to show a sum of Materials Used from all the related Day Sheets and I am not sure how to do that.
At present I am using the following expression but it only gives me a list of all the related Materials Used, often with duplicates, but I don’t know how to group and Sum them by Materials Used. Any suggestions?

<<Start: SELECT(MaterialsUsed[MaterialUsedKey],IN([MaterialUsedKey],[_THISROW].[MaterialsUsed]))>><<[Qty]>>

Marc_Dillon

Good. You should be able to do this then:

First, we’ll enter a START expression that will iterate across all unique Materials in the entire Order.

<<START: 
  UNIQUE( 
    SELECT( 
      MaterialsUsed[Material*] , 
      [Daysheet*].[Order*] = [_THISROW-1]
    )
  )
>>

Replace the starred columns if they are different in your app. The “[_THISROW-1]” might be wrong.

From there, we can get sums for each particular Material:

SUM(
  SELECT(
    MaterialsUsed[quantity*] ,
    AND(
      [Daysheet*].[Order*] = [_THISROW-2] ,
      [Material] = [_THISROW-1].[Material-key*]
    )
  )
)

Again, replace starred column names where appropriate, and the "[_THISROW-n]"s may need adjusted.

View solution in original post

How can I sum by multiple columns in Grandchild table is a workflow rule email?