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

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]>>

Does the Materials Used Table have a Ref column to a Materials Table (or “Products” or similar)?

1 Like

Yes, it does so that the user can add a material by selecting from a drop down that is referenced to my Materials table

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.

2 Likes

Thanks Marc. Once I had my field names correct it worked perfectly. Appreciate your help!

3 Likes