Database fields in calculations

<< Click to Display Table of Contents >>

Database fields in calculations

Fields from a database can also be used in calculations. The prerequisite for this is that you have assigned a database to the document via the ribbon command Mailings | Select database | Use existing database.

You do not even have to enter the field names manually. If you look at the dialog box of the ribbon command Mailings | Field dropdown_arrow | Calculation, you will notice that the names of all database fields appear in the list Variable/field as soon as you assign a database to the document. They can be entered in the formula via a double-click.

As an example, we want to create an invoice form that can be easily printed as a mail merge. All you need is a database that contains fields for customers' personal data (name, street, city, etc.). In addition, the database should contain a QUANTITY field with the order quantity, an ITEM field with the item name and a PRICE field with the unit price. Proceed as follows:

1.Use the command File | New file_new_icon to create a new document.
2.With the ribbon command Mailings | Select database | Use existing database db_use_existing_icon, you assign a suitable database to this document (example: see below).
3.Next, create a normal mail merge, as described in Database. Insert the database fields with the customer data (address, etc.) at the appropriate places in the letterhead.
4.Design the invoice itself according to your needs. Enter fields from your database with the ribbon command Mailings | Merge field – for example, the QUANTITY field for the quantity ordered.
5.We now come to the highlight: You can calculate with the database fields. For example, the formula "QUANTITY*PRICE" calculates the sum for the current record. Thus, use the ribbon command Mailings | Field dropdown_arrow | Calculation to insert suitable calculations in the desired places.
6.If everything has been entered, you can save the document. If necessary, select the desired records in the database and print your invoices using the ribbon command File | Print merge.

Example

You will find an application example in the folder with the sample documents for SoftMaker Office: the Invoice.tmdx file with its associated Invoice.sqlite database.

Depending on the operating system, you will find these files as follows:

Windows: In the SoftMaker\Samples folder in your Documents folder

macOS: In the SoftMaker/Examples folder in your Documents folder

Linux: In the SoftMaker/Samples folder in your Home folder

Android: In the SoftMaker/Samples folder on your SD card

iOS: In the SoftMaker/Samples folder in your App folder

Tip: After opening Invoice.tmdx, enable the option Display field names on the "View" tab in the dialog box of the ribbon command File | Properties.

Alternatively: Use the ribbon command Mailings | group Fields | Field names or simply Alt+F9

You can now see all the formulas in plain text and can understand more fully how this self-calculating mail merge works.