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)
Trainer (Table with the info of our trainers)
[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?
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:
Source:
[New Expression]
<<Start:SELECT(Trainer[trainerID], ([trainerID] = [_THISROW-1].[trainerID]), TRUE)>><<[trainerName]>><<End>>
This worked for me and learned something new today.
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.
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
User | Count |
---|---|
41 | |
25 | |
23 | |
19 | |
15 |