Refs in report causing error

[Patrick_Paul]

I have the following expression in a report template. It’s causing an error, but I don’t know what it is because it gets cut off in the log and I can’t see the whole message. Is it because I’m trying to reference a reference? If so, how do I get to that data? I basically need to look at a field (date) within a referenced table (Level “X” Assessment) of a referenced table (trainees, whose key value is “RandomID”) . Here is the expression:

Last Assessment: <<ifs([RandomID].[Current Level]=1,[RandomID].[Level 1 Assessment].[date],[RandomID].[Current Level]=2,[RandomID].[Level 2 Assessment].[date],[RandomID].[Current Level]=3,[RandomID].[Level 3 Assessment].[date])>>

It is also worth mentioning that not every trainee is going to have an assessment in existence for each Level. This doesn’t present a problem within the app, because it just doesn’t show us a link to the “bad” ref (the table is ready-only so the detail view only shows the “active” refs)… Just in case this presents a problem in this situation?

[Steve]

Patrick_Paul:

It’s causing an error, but I don’t know what it is because it gets cut off in the log and I can’t see the whole message.

Please post a screenshot of the error.

Patrick_Paul:

Is it because I’m trying to reference a reference?

Quite possible. Dereference chains (e.g., [RandomID].[Level 1 Assessment].[date]) are not (yet?) supported.

[Patrick_Paul]

Urgh. I really need to be able to “reference” two levels deep… This is going to be a big issue with my app.

At any rate, here’s the error verbatim (I figured out if I highlight it will scroll even though there’s no scrollbar)… Seems it may be the “reference to a reference”, but the message is clear as mud.

“Errors”: “Error: Report ‘TT report -Weekly’ action ‘Action 1’ Body template. Expression ‘ifs([RandomID].[Current Level]=1,[RandomID].[Level 1 Assessment].[date],[RandomID].[Current Level]=2,[RandomID].[Level 2 Assessment].[date],[RandomID].[Current Level]=3,[RandomID].[Level 3 Assessment].[date])’ is invalid due to: Error in expression ‘[RandomID].[Level 1 Assessment].[date]’ : Unable to find column ‘RandomID].[Level 1 Assessment’.”,

[Steve · Accepted solution]

Yep, the problem is the dereference chain.

[Patrick_Paul]

I actually just figured out a way to “code around the problem”! Thanks for your help.

[Steve]

Patrick_Paul:

I actually just figured out a way to “code around the problem”!

Care to share?

[Patrick_Paul]

Sure. I ran the portion through a select() so that I didn’t have to ref two levels “deep”.