Advanced Email Mail Merge
- Silas Canning (Unlicensed)
Analytics
Email templates can contain ‘advanced’ mail merge which allows for formulas to return multiple records, or specifically defined records.
Operators
Operator | Description |
|---|---|
Eq | Equal To |
ne | Not Equal To |
sw | Starts With |
nsw | Not Starts With |
ew | Ends With |
new | Not Ends With |
co | Contains |
nco | Not Contains |
lu | In List (comma separated list of values) |
nlu | Not In List |
gt | Greater Than |
gte | Greater Than or Equal To |
lt | Less Than |
lte | Less Than or Equal To |
For Each
“For Each” works for:
Costs (Cost Option Costs)
Costsh (Cost Option Headers)
Diary
Removals
Waybills
Imquote (Rate Requests)
Customers
Creditors/Suppliers
Reminvent (Removal Inventory)
Oagents (Agents).
It must begin with <for each; then all of the tables in the query in the order they appear and then the query itself. They need to be separated by ‘;’ so there are three sections.
Example
Dear <Client>,
We would like to inform our plan of actions as following:
<for each;diary;for each diary where diary.rpid = |rpid| no-lock>
<Diary Desc> <Diary Date> </for each;diary; for each diary where diary.rpid = |rpid| no-lock>
Kind Regards,
Value
Use |value| to check value i.e. |rpid|, |waybillid
Example
Dear <Client>,
Example of showing only diaries description ‘X’
<for each;diary;for each diary where diary.dd-desc = ‘X’ and diary.rpid = |rpid| no-lock>
<Diary Desc> <Diary Date> </for each;diary;for each diary where diary.dd-desc = ‘X’ and diary.rpid = |rpid| no-lock>
Kind Regards,
Find first
<Find first> also works in this fashion, but contains a for each loop in the query.
Example
Dear <Client>,
Example of showing only 1st diary with description ‘X’
<find first;diary;for each diary where diary.dd-desc = ‘X’ and diary.rpid = |rpid| no-lock>
<Diary Desc> <Diary Date> </find first;diary;for each diary where diary.dd-desc = ‘X’ and diary.rpid = |rpid| no-lock>
Kind Regards,
No Results
You can use the <No Resuts> </No Results> tag to show custom text when no records are found. Just put this tag inside the <for each;> tags.
Note you cannot use mail merge fields inside this ‘No Results’ tag, simply standard text.
Example
Dear <Client>,
Is there any diary with description ‘B’?
<find first;diary;for each diary where diary.dd-desc = ‘B’ and diary.rpid = |rpid| no-lock>
<Diary Desc> <Diary Date>
<No Results> NO, there is no diary with description B found </No Results>
</find first;diary;for each diary where diary.dd-desc = ‘B’ and diary.rpid = |rpid| no-lock>
Kind Regards,
Note that using <> in a query will cause issues. Progress does support ne (not equal) eq (equal) lt (less than) gt (greater than) for the operators to be used.