Getting a column value from a ref Table

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:

However OP didn’t post what worked for her.

Any idea what the expression might be missing?

Thanks

Update: Report config view

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?

1 Like

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:


[New Expression]

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

This worked for me and learned something new today.

3 Likes

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.

3 Likes