Getting a column value from a ref Table

[Luis_DfG]

I’m trying to get a ref value from another table for my report template.

[Data Structure]

Slice (used to build the report - I’m slicing only the values that we need: weekly classes given)

• sessionID
• trainerID
• sessionName,
• etc

Trainer (Table with the info of our trainers)

• trainerID
• trainerName
• etc

[Expression]

My first expressions get all the records from the slice:

**<<Start:SELECT(Recent Sessions[sessionID], TRUE)>>**

The second expression tries to get the list of trainer records that match the current trainer ID

**<<Start:SELECT([Trainer][trainerID], ([trainerID] = [_THISROW].[trainerID]), TRUE)>>**

However I’m getting the following error:

Expression ‘SELECT([Trainer][trainerID], ([trainerID] = [_THISROW].[trainerID]), TRUE)’ is invalid due to: Unable to find column ‘Trainer’, did you mean ‘trainerID’?. Error: Report ‘Last Week Session Report’ action ‘Email Managers’ Body template. Start expression ‘SELECT([Trainer][trainerID], ([trainerID] = [_THISROW].[trainerID]), TRUE)’ should generate a List of Ref values.

I followed this topic:

Workflow errors - expression refers to undefined field Questions

What exactly does this error mean? Im using a google doc template and copied the column names exactly and have them inside << >>. Not sure how to troubleshoot these error messages…the documentation online is not helpful. [15786706053237692750452123316234]

However OP didn’t post what worked for her.

Any idea what the expression might be missing?

Thanks

Update: Report config view

[Suvrutt_Gurjar]

Is Trainer table child table of Sessions table?

If so, could you please update what is the reverse reference column name in the Sessions table related to Trainer table?

[Luis_DfG]

Hi Suvrutt,

Sessions takes trainerID as a foreign key.
In both tables the column name is the same, trainerID

However after going around I found 2 errors in my expression:

[Old Expression]

<<Start:SELECT([Trainer][trainerID], ([trainerID] = [_THISROW].[trainerID]), TRUE)>>

Problems:

• SELECT([Trainer][trainerID] //Tables must be referenced without [ & ].
• [_THISROW] //Since I have a expression inside a expression and I’m using [_THISROW] to refer to the trainerID from the First Expression this must be updated to [_THISROW-1]

Source:

[_thisrow] in <<start: is not working? Questions

Sorry, my lack of familiarity with report templates confused me. _THISROW isn’t available where you’re trying you use it. If this <<Start>> block is inside another one, you can use _THISROW-1 instead. See Accessing Columns in Parent and Grandparent Records in Template Start Expressions.

help.appsheet.com

Template Start Expressions

Using Start expressions in your workflow templates.

[New Expression]

<<Start:SELECT(Trainer[trainerID], ([trainerID] = [_THISROW-1].[trainerID]), TRUE)>><<[trainerName]>><<End>>

This worked for me and learned something new today.

[Suvrutt_Gurjar]

Hi @Luis_DfG,

Excellent. Glad you figured it out and thank you for posting back your solution as well. I am sure your explanation will help anyone who refers it in future.

[Cyrine_Mae_Mend]

Luis_DfG:

<<Start:SELECT(Trainer[trainerID], ([trainerID] = [_THISROW-1].[trainerID]), TRUE)>><<[trainerName]>><>

Tried this expression and I got an error.

‘SELECT(P3DemandRelease[P3 Sub Demand ID],([Initiator] = [_THISROW-1].[Initiator]), TRUE)’ is invalid due to: Unable to find column ‘_THISROW-1’.

any suggestion please