- 19 Dec 2023
- 1 Minute to read
- Print
- DarkLight
- PDF
Auto-Calculations (ADVANCED USERS)
- Updated on 19 Dec 2023
- 1 Minute to read
- Print
- DarkLight
- PDF
At times, you may wish to have a merge automatically calculate a number for you.
One example would be if you wish to use invoices to ask people to up their donations. Say, you create an invoice merge template reading: Hi, you gave us {Plg_Amount} last year. This year, give us {DOUBLE THAT NUMBER}.
Here’s how to do it: Check the list of available fields on the top right of the template editor. Find the field that meets your needs… in numeric or money form! These calculations WILL NOT work on text fields, and if you cannot find a numeric/money field, you would either need to scrap this template idea, or put in a customization request. You can tell if a field is numeric/money if you see that it displays as a simple number or number with a decimal, etc., on the Recipients tab of the Mail Merge Wizard, in the spreadsheet shown. Fields whose values contain dollar signs and the like ARE TEXT FIELDS and WILL NOT work.
To use our example of doubling the Pledge Amount in an invoice template, you’d need to find the field called Plg_AmountAsMoney (since Plg_Amount is, ironically, a TEXT FIELD, due to its diabolical DOLLAR SIGN).
Note: Plg_Amount is not ALWAYS a TEXT FIELD. For instance, on Pledge List merge templates, as you will see if you check, Plg_Amount simply displays the numbers without a dollar sign! So always check each particular template's fields' values on the spreadsheet in the Recipients tab.
Once you have your field, click Insert on the toolbar and select Data Field. Type the code into “Field Name” (use the field name you choose, of course, instead of Plg_AmountAsMoney).
If you want the amount with a dollar sign, then use:
{FORMAT|{0:C2}|=|money|Plg_AmountAsMoney*2}
If you did that, and the pledge amount was ten dollars, the merge would display $20.00.
If you want the amount without a dollar sign, etc., then use:
{FORMAT|{0:C2}|=|Plg_AmountAsMoney*2}
If you did that, the merge would display 20.0000.
Note that you can use this for all manner of crazy calculations, and you can even include several fields in the calculations! For instance, the following would work perfectly:
{FORMAT|{0:C2}|=|act_id/2+4*plg_amountasmoney/5-0.3333}
Just make sure you remember to Please Excuse My Dear Aunt Sureleh/Sally (PEMDAS – Parentheses, Exponents, Multiplication/Divison, Addition/Subtraction). We follow the RULES of math round here parts!
Note that both a hyphen and a minus symbol work, both a multiplication symbol on your keypad and a regular asterisk work, etc.