Extracting data from a Child of a Child table

[RichardHulbert]

Hi All,

I could do with a bit of help please.

So, I have a Report table (Parent) that can have many records from a related Assessment table (Child). This "Child" table can then have many images from a related Images table which is a "child" to the "child" table or in effect a "grandchild" to the Parent table.

My challenge is to create an output report (PDF) from the Parent table that includes all the related Assessments (from the Child table) displayed separately in the report with their specific related images (from the Grandchild table).

I can do the mechanics of creating the PDF but it's how I manage the related lists that I'm struggling with. Do I need additional references or slices maybe? Or am I just over thinking it?😂

Any help would be very much appreciated.

Many thanks

Richard

@AleksiAlkio 

[AleksiAlkio]

No slices etc. are needed. You just need to use nested Start: & End formulas.. twice. 

[RichardHulbert]

Yes I guessed that was what I needed but I can't work out those formulas required to get the relevant data from the grandchild table to the Parent table. Am I making sense? I'm sure this is very simple but could you give me some clues please?

[RichardHulbert]

e.g. do I need a ref between the Parent table to the "grandchild" table, bypassing the "child" table?

[AleksiAlkio]

No Ref is needed. It needs to be something like if it's triggered from the parent record..

<<[Parent]>>
<<START: SELECT(Child[ID],[ParentID]=[_THISROW].[ID])>>
<<[ID]>> <<[Child]>> <<[ParentID]>>
<<START: SELECT(GrandChild[ID],[ChildID]=[_THISROW-1].[ID])>>
<<[ID]>> <<[GrandChild]>> <<[ChildID]>>
<<End>>

<<End>>

[RichardHulbert]

what does the -1 (in.....[ChildID]=[_THISROW-1].....) do?

[AleksiAlkio]

It reads the ID from the Child table. If it would be just [_THISROW], it would read it then from the Parent record.

[RichardHulbert]

Oh thank you I didn't know that.

However, your advice not to create slices or more references prompted me to create a test attachment template (to work on your suggested expression above) using the "create" button in the Automation Task editor and it basically did all the work for me! I had no idea how powerful it was as I've always created my own templates. It understood the relationships between the tables and and created all the expressions I needed. I just need to tailor the template to the way I want it to look.

Once again i was overthinking it!😂

Thanks again for your help

[AleksiAlkio]

If you have Refs, then the "Create" understands how to build it. But you don't need to use Refs if they are not needed.. for example inline view is not needed or any kind of virtual calculation. Then you need to create this functionality manually.