Issue with Email Template for Table A generated from Table B

Hello, I have a Workflow designed to email a Quote when an entry is made into the Activity table and so the workflow is based on the Activity table.The workflow is failing on the email template - seems its not recognizing the data:

Is the Start Expression correct here (Note: Reference = the Quote ID in the Activity table) I’ve tried [_THISROW].[Reference] but no luck - same error:

Which table is the [quote date] column in?

If it is in the activity table, I think you’ll need to use <<[_THISROW-1].[Quote Date]>>

1 Like

[Quote Date] and all fields on the template are coming from the Quote Header Table.

1 Like

I think you should be using [_THISROW].[Reference] in the SELECT.

[quote_id] is the key column for the Quote Header table?

If you remove the <<[quote date]>> portion of the workflow, do you then get the same error on a different part?

1 Like

Hi Marc,

I’ve tried using [_THISROW].[Reference] but no luck.
Yes, [quote_id] is the key of the Quote Header table.
On removing quote_date from the workflow, unfortunately the Audit Trail lists every field in the template.

Thanks for your help.

1 Like

Ok. Unfortunately I’m not seeing the issue.

At this point what I would do is create a copy of the workflow and template, then strip down the template to just its basic parts and test small parts at a time.

You could also create a column in the Activity table for the SELECT expression to live in. Then you can simply use <<Start:[new column]>>

1 Like

ok thanks. I thought the Start expression had to reside in the email template in order to generate the fields needed for the quote.

Yes it does, but the list of row keys (generated by the SELECT) can be moved somewhere else, where it can more easily be inspected to ensure it is working.

1 Like

ok thanks, I’ll give it a try.

Try replacing:

SELECT(Quote Header[quote_id],[quote_id]=[_THISROW].[Reference])

with:

FILTER("Quote Header",[quote_id]=[_THISROW].[Reference])

This is a good idea. You might try with:

<<Start: FILTER("Quote Header", ([_THISROW].[Reference] = [quote_id]))>>
<<[Prospect].[Name]>>
Quote Date: <<[Quote Date]>>
<<End>>

Thanks Marc and Steve. I created a VC field in the Activity table called vir_quote with the SELECT statement SELECT(Quote Header[quote_id],([_THISROW].[Reference] = [quote_id])) and then created a brand new template from the ground up. I used <<Start: [vir_quote]>> and it works great.

I’ll keep the ones you outlined above handy for next time.

Thanks a million!

1 Like

Good to hear. Did you copy paste the select expressions, or did you re-type it?

In my initial troubleshooting, I copy/pasted and also retyped the Select Expressions. When I created the brand new template, I used the [vir_quote] field (containing the SELECT statement in Activity table) as the <<Start: Expression :

I meant did you copy and paste from the template to the app formula (just now when you got it to work).
I only ask because if you copy-pasted, then it wasn’t the SELECT expression itself that was the problem, but if you re-wrote it, it might have been (like a small typo or something).
Its always good to figure out what exactly the issue was, even though you’ve got it to work a different way. No worries if you just want to be done though :smiley:

1 Like