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

Griff
Participant V

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

Solved Solved
0 4 359
1 ACCEPTED SOLUTION

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

4 REPLIES 4

Does the Materials Used Table have a Ref column to a Materials Table (or โ€œProductsโ€ or similar)?

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.

Griff
Participant V

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

Top Labels in this Space