Curious if anyone else is running into this issue with a similar arrangement:
I have the following data relationships relevant to my bot:
employees (key: employee_id)
├──< employee_monthly_bonus_review (employee_id)
├──< employee_pay_history (employee_id)
│ └─── job_titles (job_title)
└─── job_titles (job_order = heirarchy_order — derived via current employment record)
job_titles (key: id)
└──< employee_pay_history (job_title)
Legend: `──<` = one-to-many (has many) · `───` = ref / lookup (belongs to) · `(Column)` = the
ref column.
Any Employee’s Job Title for example is derived from the most recent employee_pay_history record related to that employee. To expedite lookups on this record, I have a virtual column in the employee table that indexes the most recent ref record on that table for each employee called current_pay_record
The bot is triggered on creation of a employee_monthly_bonus_review record.
I am using HTML pasted into the Email Body input of the bot. In my email template i have tried to employ the following expression (which renders fine if it were rendered in the app) <<[employee_id].[current_pay_record].[job_title].[jbo_title]>> however, in the email, nothing is rendered for this value. I could try to do one very long SELECT() expression with multiple nested sub-queries, but that would feel like a major AppSheet anti-pattern.
Upon further testing, no virtual column seems to render when dereferenced in a template expression. I have tested on different applications with different data-relationships, but any time I try to do anything of this nature <<[ref_column].[virtual_column]>> nothing renders in the resulting email.
Anyone noticed a similar bug?