Construct Select Statement to itemise and summarise Grandchild records

Griff
Participant V

I need a little help constructing the start statement for a workflow rule when an order is closed. The basic structure of my tables is:

Parent table = Orders with Key = OrderId

Child Table = DaySheets with Key = DaysheetId linked to Order table by an IsPartOf REF field
(Each order can have several linked DaySheets)

Grandchild tables

  1. Labours with Key = LabourId linked to DaySheets table by an IsPartOf REF field
  2. Expenses with Key = ExpenseId linked to DaySheets table by an IsPartOf REF field
    (Each DaySheet can have several linked Labours and Expenses)

However my report only needs to display the headings from order along with:
SUM of Hours from Labours table for all associated DaySheets
Individual line items from expenses table linked to any of the DaySheets

Thereโ€™s no need to display ANY data from DaySheets. I need to summarise data for an Order by Grandchildren, effectively bypassing Children

Eg. the final report should look something like the following. The first line needs to be the sum of Hours for Labours from all the associated DaySheets linked to the order; and the following lines need to be the individual expenses that link back to the order via all the associated DaySheets

ORDER SUMMARY:
Customer: <<[Who For]>>

Amount Detail
<<Sum([Hours])>> Labour
<<[Amount]>> <<[What For]>>
<<[Amount]>> <<[What For]>>
<<[Amount]>> <<[What For]>>

Any suggestions appreciated.

Solved Solved
0 1 169
1 ACCEPTED SOLUTION

Griff
Participant V

Iโ€™d like to withdraw the question as I have found a solution

View solution in original post

1 REPLY 1

Griff
Participant V

Iโ€™d like to withdraw the question as I have found a solution

Top Labels in this Space