I’ve set up several email templates using the Gmail pack and the Format formula to refernce values in the row within the email. I noticed that if one of the referenced columns is blank, or missing a value, the series of text values references moves forward a number.
Here is an example:
Format([DB Outreach Templates].Filter(Name=“Screening Confirmation 1”).Body, thisRow.[First Name], thisRow.Date, thisRow.Time, totext(thisRow.[Location Name]), thisRow.[Full Address],thisRow.[Contact Title Email], thisRow.[School District], thisRow.[To Be Screened Estimate])
So the {1} item in the email template is the First Name for the saluation, but if there is no First Name entered in the row, then the Date moves into the {1} position and all other values shift forward in the template.
Is this supposed to work this way? I would expect if one of the refernced values is blank or missing, that it would just return a space or blank. Is there a trick to getting this result instead of all of the merged values ending up out of place?
I can’t reproduce the behavior you’re describing in the very very quickly made and simplified sample here …
I mean, if there’s no value in thisRow for a placeholder, Format() doesn’t see to ignore it…
Would you mind sharing a sample doc featuring the issue you’re encountering ?
(Screenshots of the formula, templates … might works too )
Here is a screenshot of multiple rows where you can see if the first name is complete, the email body starts correctly, but if the name is blank, it starts with the date.
The first name is being pulled in with a formula that I don’t have quite right yet. The location can have more than one contact associated with it but I want it to only provide the name of the contact with a Title that contains “screening contact”. I could also try setting up the formula to use the text “Screening Contact” if no value is identified.
I wonder if using FORMAT with a value that is being calculated by a formula could be causing some of the problem? Its a mystery - but I’m sure its not supposed to work this way!
… I can tell you that, from the screenshots you shared (Thanks for sharing ), it’s not that the value you use for some placeholders is calculated that could be the problem here but it’s more likely coming from what the formula you use is actually returning …
For your contact info (the first name), for example, you don’t have a text value for the placeholder but a list of text values (and I’m assuming here, a list of one text value) and as Format() can “auto-interpret” items of a list it’s fed as placeholders, I can only guess this is why some of your placeholders are shifted if the formula doesn’t return anything.
As this specific placeholder is the placeholder {1}, while returning a list of text values, it might somehow and invisibly take more space than you might think in the output which only becomes visible when something’s missing (as illustrated by the [ ] your formula returns when there’s nothing to return, it doesn’t actually return a Blank value but an empty list (i.e.: a list of blank values))…
But to be honest, this is still just a wild guess …
All of this to say (sorry ) that the problem probably comes from how you get your contact(s) info in the table … which lead me to this question :
What formula do you use to get the [First Name] ?
Depending on your use case, there might be an easy fix for this …
!
…
)
), it’s not that the value you use for some placeholders is calculated that could be the problem here but it’s more likely coming from what the formula you use is actually returning 
…
) that the problem probably comes from how you get your contact(s) info in the table