Create a Word report template
To create a Report Template on Word, you need to have a form in order to know the system names of the fields. The allowed format is .docx and the system names of your fields must be contained between <% and %>.
Here is an example of a report template:
We can see that it includes all the system names with a particular syntax. Indeed, they must be surrounded by <%…%> to notify Daxium-Air that it must match the field data entered by the user in the form. It is important to note that it is possible to apply any font changes to what will be displayed. Like <%EmployeeName%> which is in bold so the customer name will be displayed in bold in the report.
Text or List fields do not require any special syntax. However, Boolean fields return “True” or “False”, so if you want to obtain “Yes” or “No”, it is possible to apply a conditional If function (see green box).
This is composed as follows: <%If(SystemName == 'Condition', 'Then', 'Else')%>
All the formatting you applied on the Word will be applied in the report.
Add a report template in Daxium-Air
Go to Settings > Report > New:
Next, select the form to which you wish to link this report template, then select the format of your template using the button provided for this purpose.
The “Listing mode” box will allow you to generate a report for several records at the same time, particularly relationships.
Relationships in a Word report
The report model must be based on the main form and it may contain all the directly related files.
To display the relationship in a Word document, you must create a table. We thus use the "Relationship" function, then, we include the label of the related field present in the main form:< /p>
As you can see, we call <%Relation(‘label')%> so that the relationship is initiated in a table. All system names that will be called will come from the records of this relationship.
The table row will be repeated as many times as there are records in the relationship.
The fields <%systemname1%> and <%systemname2%> are fields of the related form having the system name systemname1 and systemname2.
Relationships in an Excel report
When creating a new report template, a Listing mode option is available. Its role is to allow the integration of several sheets of the same form into a single mailing document.
We use Excel more often than Word to use listing mode (much more flexible for a report of several sheets).
You must use MS Excel 2007 or higher (.xlsx format). MS Excel 2013 recommended.
For an Excel report, you must always select the Aspose library for Excel.
You can start from a blank Excel file or from an existing model.
Properties & personalization made on the file are respected during mailing (font / colors / etc.).
Rules, formulas, data reprocessing are also respected during direct mailing.
How Excel mail merge works in Daxium-Air is similar to Word mail merge (Generation / Automation).
We limit the number of files that can be generated for a report in listing mode to 30,000.
Step-by-step mode
Insert a sheet named “PPActions”: this sheet will contain the tags indicating the fields to insert in the report.
Insert a sheet named according to your choice
In this new sheet, create a table with just one line (1 header + 1 line) which will be filled with one line per record and one piece of data per column
Give a “zone name” to your table: select the table (upper left corner) and name the zone
In the PPActions sheet, add a row with as many columns as fields in your table as follows:
Details:
Type:table = Variable syntax for declaring an array
TableName:table name = Name of the table given in the area
Value:system name of the field = System name of the field in the form
6. Insert a table with relation (relational structure) with one row as follows:<%Type:subtable;TableName:table name; Relationship:relationship name%> | <%Value:field name%>
Details :
Type:subtable = variable syntax for declaring a relation table
TableName:table name = Name of the table given in the area
Relationship:relationship name = Label of the relationship field in the main form
Special fields
Image : <%Value:system name of the field; Type:Image%> (Unlike Word Mailing, we do not define the size of the photo here. It is the size of the chosen cell which is authentic (you must therefore enlarge the size of this cell which will receive the photo if necessary)
Quantity relationship : <%Value:[Relation.Count]%>
PDF Fonts
Word offers a multitude of fonts, but for generating reports in PDF format, the available fonts are the following ones:
Courier, Courier Bold, Courier Oblique, Courier Bold-Oblique
Helvetica, Helvetica Bold, Helvetica Oblique, Helvetica Bold-Oblique
Times Roman, Times Bold, Times Italic, Times Bold-Italic
Symbol
Zapf DingbatsNB
Lora, Regular, Bold, Italic, Bold Italic, Medium, Medium Italic
Poppins, Black, Black Italic, Bold, Bold Italic, Extrabold, Extrabold Italic, Medium, Medium Italic, Thin, Thin Italic, Light, Light Italic, SemiBold, SemiBold Italic
If the font chosen in the Word template is not available in PDF format, an automatic conversion of the font will be carried out to one available in PDF. It is possible to make a request to Daxium support to add a new font to the list above, provided that the font in question is available for download and royalty-free.
Scheduled report
It is possible to trigger the generation and sending of reports at a specific frequency. To set up a scheduled report, select the desired report template, check "Listing mode" then click "Add Schedule Report" :
Special case: It is not possible to display the current user function in scheduled reports.
Step-by-step mode
Rename the file name
Select a format
Define a TimeZone
Choose a condition for the generation to trigger. By default, generation is triggered over the period defined below. The conditions are those of the form.
If you want to send the generated report by email, complete the "Email" and "Recipient"
The </> Edit allows you to access the form fields to add data entered in the form.
6. Define the content of the report, that is to say, the sheets to include in the file that will be generated
7. Determine the period using the options, you can choose between system fields (created at, updated at, ...) or a specific date field
NB Scheduled reports have a maximum limit of 30,000 submissions
Automatic report
It is possible to trigger the generation and sending of reports automatically. The creation process is similar to that of a planned report.
Step-by-step mode
Rename the file name
Select a format
Define a TimeZone
Choose a condition for the generation to trigger. By default, generation is triggered over the period defined below. The conditions are those of the form.
Select a trigger event (creation and/or modification of a record)
6. If you want to send the generated report by email, complete the "Email" and "Recipient"
By default, the email will always be sent by mailjet@daxium.com or noreply@mg.daxium-air.com.
The </> Edit allows you to access the form fields to add data entered in the form.
Special case: It is not possible to display the current user function in automatic reports.
Use of system data from the form
A. Identification of the sheet
File Number: <%SubmissionId%> or <%Id%> (Result: 584152)
Uid number:
B. Creation of the file
Date of first web recording of the file: <%[Created_At]%>
Date creation of the file: <%[Created_At]%>
Creator of the file:
First name: <%[CreateUser.FirstName]%>
Last Name: <%[CreateUser.LastName]%>
Mail: <%[CreateUser.Email]%>
C. Last update of the sheet
Date of last web recording: <%[Updated_At]%>
Date of update of the file: <%[Updated_At]%>
User who last modified the record: <%[UpdateUser.Email]%>
D. Form
Form name:
Form identifier:
E. Related task
Planned start date and time:
Expected end date and time:
Deadline:
Actual start date and time:
Effective end date and time:
State (done, to be done):
Time status (on time, late...):
F. Other information
Company name:
Native position geolocated when creating the file on the mobile:
Advanced functions
Inserting a simple field
A form field is inserted by surrounding its name with <% and %>. These markers tell the system where the data on the record should be inserted. It is possible to apply to a syntax the formats supported by Word/Excel such as Bold, Italic, underlined.
Syntax: <%System_Name%>
If your Text field is in "Rich Text", use <%MarkDown(SystemName)%> to display the layout on your Word report (not functional in Excel).
Important : System names are case sensitive >.
Inserting a formula
It is possible to apply a formula to the fields used in the report.
Syntax : <%FieldName1 <operator> FieldName2 | ConstantExpression%>
<operator> can be a mathematical operation, such as +, -, *, /, %
ConstantExpression is a number with or without decimal place
Parentheses are also supported.
% is also an operator module.
Examples :
TOTAL: €<%Price * 1.196%>
With Price = 10 it comes out: TOTAL: €11.96
Discount: $<%(Price Quantity) – (15 / 100) (Price * Quantity)%>
With Price=18 and Quantity=2, it comes out: Discount: $30.6
Here is the complete list of functions that are supported. These mathematical functions are also case sensitive.
Repeating a section of a document – Repeat()
When the report template is in Listing Mode, it repeats on several pages the same syntax elements that are contained in the data sheets. To specify a specific section to repeat in a part of the model, it is necessary to create a table and insert a Repeat() instruction on the first line.
For the system to identify the line to repeat, the Repeat() instruction is used.
Listing Mode Example :
With 3 lines of data, the result is as follows:
The Repeat() instruction must always be the first element of the line. It must be placed on the leftmost cell of the table row.
The entire model can only contain one Repeat() instruction. In case there are several repeat instructions in the document, only the first one will be detected.
3.1. Filtering source data based on a criterion
At the top of a document or in the first cell of a table, it is possible to insert an instruction to repeat the section and filter the basic data: %Repeat(Date Field > #12/ 01/2023#)%> where #12/01/2023# is a date expressed in: Day / Month / Year.
It is possible to use the #date# field instead of a hard date. This variable will be replaced by today's date.
Example: <%Repeat(DateField <#date#)%>
It is also possible to subtract a period of time from today's date.
Example: <%Repeat(DateField < #date-5d#)%>
This expression means 5 days before today. The format is not case sensitive, so #Date-5D# also works.
It is possible to define this interval using one of the time units present in the table below:
Information : You can also use a + sign instead of the – to refer to past or future dates. Example: DateField <#date+10d#>, i.e. today + 10 days.
Important Notes : This repeat statement does not support functions with Field System Names. It is possible to combine filter conditions with the AND or OR operators.
Example : <%Repeat(DateField<#date-5d# AND Id >= 3) %>
3.2. Avoiding data redundancy in a Repeat()
When using the Repeat() function, you may want to avoid redundancy of some information. To do this, you can use the DISTINCT() function.
Example: In a list of orders, we would like to know all the customers while avoiding the repetition of the name in the case where a customer has made several orders. We can then use <%DISTINCT('Client')%> instead of <%Customer%> in a Repeat().
In the case where the field has several separate texts such as dates (with days, months, years, etc.) or lists (with one text per depth level in the list), we can concatenate these texts using a second argument in the DISTINCT() function.
Example : <%DISTINCT('F(\'MM yyyy\',CreatedAt)', ',')%> allows you to have all the months of creation of a set of files.
Image manipulation
4.1. Insert an image – Image()
Photos can be inserted into the Document Template using an Image() statement, shorthand syntaxImg(),as well as the Image() statement. insert into a one-column table.
Syntax: <%Image(NameField, Width, Length)%>
If the height is not specified, the system assumes that the image fits into a square of width x pixel width.
If width and height are specified, the resulting image fits that rectangle without distorting
Examples :
Large Photo: <%Image(ImageFieldName, 200)%>
Small Photo: <%Img(ImageFieldName, 70, 50)%>
The image is automatically resized according to the size of your Image() function. If the original image is larger than the specified dimension, then it will be reduced accordingly, to save space in the generated mail merge document.
4.2. Insert a sequence of images – Images()
Concerning an image field with a maximum number greater than 1, you must use another function which is Images(), as well as insert it into a one-column table.< /p>
Syntax: <%Images(NameField, start, end)%>
To define a size on your images you need to add <%Image()%>.
Example: <%Images(Photo)%><%Image(400)%>
It is possible to obtain more data from your images, however you will need to enter them in a table with the following elements:
4.3. Resizing images
It is possible to resize images before integrating them into the report. This reduces the total weight of the final report and optimizes its generation time.
To do this, add the following tag (replace ImageSize with the attribute shown in the table below: <%UseImagesPreview('ImageSize')%>
All images following this tag will be resized according to the following rules:
NB <%UseImagesPreview('ImageSize')%> does not allow extraction of Exif data from images.
There is no Exif information on preview images.
4.4. Convert an image to JPG
It is also possible to reduce the size of images by converting them to jpg. To do this, you can use the SetPdfJpegQuality function which takes an image quality index (between 1 and 100) as a parameter.
Just add the function in the word document or in a box in the PPActions sheet.
Syntax: <%SetPdfJpegQuality(10)%>
4.5. Insert an image in Excel from a relationship field
To insert into an Excel report not using listing mode, you can use the following syntax to retrieve the photo field from a related record.
<%Img([RelationshipSystemName.ImageSystemName], 60, 60)%>
Replace snake_case with CamelCase (https://fr.wikipedia.org/wiki/Snake_case, https://fr.wikipedia.org/wiki/Camel_case), that is, replace the first letters of each word of the system name with a capital letter and remove the symbol "_" if existing, the same for the photo field of the relationship
Syntax: <%SetPdfJpegQuality(10)%>
4.6. Insert a map – Map()
As we have the longitude as well as the latitude in the files if a location field is present, it is possible to display a map centered on the point which locates the file. The map is generated via the Google Maps API. This function is dependent on the configuration of the form's location parameter.
Syntax : <%Map(latitude, longitude, zoom, width, length, MapType)%>
or <%Map(address, zoom, width, length, MapType)%>
latitude is a field where a value contains the latitude coordinate.
longitude is a field where a value contains the longitude coordinate.
address matches your location field.
zoom can range from 1 to 22, where 22 is the most detailed precision on the map.
width is the size width in pixels of the map.
height is the size height in pixels of the map.
MapType is the map type where 0: Street View, 1: Satellite View, 2: Mixed View.
Example with default location: <%Map(SubmissionLatitude, SubmissionLongitude, 17, 200, 200)%>
Result :
Example with a location field: <%Map(LocalizationIntervention_latitude, LocationIntervention_longitude, 17, 200, 200)%>
Result :
4.7. Exif data of an image – ImageExif()
Photos may contain data named Exif. You can therefore retrieve the stored text data, but also locate it on a map if they have GPS coordinates.
Syntax: <%ImageExif()%>
Examples:
Image date: <%ImageExif(Photo, 'DateTime')%> (Result: 08/25/2004)
Position of the photo taken: <%Map(ImageExif(LocationField, 'GpsLatitude'), ImageExif(LocationField, 'GpsLongitude'), 17, 150, 150)%>
NB For an Excel reporting in Listing mode, the instruction to get Exif data of an image is, for example: <%Value:ImageExif([Photo],'DateTime')%> (Résult : 25/08/2004 09:32)
4.8. Barcodes
Inserting a barcode into a document is possible using the following syntax:
<%BarCode(data, 'BarCodeType', width, length)%>
Available barcode types are:
Digital barcodes
Alphanumeric barcodes
Code11: Used primarily for telecommunications equipment for labeling (more information)
Codabar: Old code often used in library systems, sometimes in blood banks (more information)
EAN13: European article numbering product code detail internationally. Data must be 12 digits (more information )
EAN8: Version of the compressed EAN Code for use on small products. Data must be 7, 9, 12, 13 digits (more information)
Industrial2of5: Old code, not in common use (more information)
Interleaved2of5: Compact digital code, widely used in industry, air cargo (more information)
MSI: Variation of the Plessey code commonly used in the United States (more information)
Postnet: Used by US Postal, an automated mail sorting service (more information)
UPCA: Universal product code seen on almost all retail products in the United States and Canada. Data must be 12 digits (more information< /a>)
UPCE: Version of the compressed UPC code for use on small products. Data must be 6.7 digits (more information)
Matrix2of5: Data can be any number of digits.
IntelligentMail: Data must be 20 digits
Example: <%BarCode('123456789012', 'EAN13', 120, 120)%> (EAN13 digital barcode)
Result:
4.9. QR Codes
Inserting a 2-dimensional code into a Model is possible using the following syntax:
Syntax: <%QrCode(data, width, height)%>
Example: <%QrCode('A String To Encode 123456', 150, 150)%>
Result:
Example: <%QrCode('A123456', 150, 150)%>
Result:
4.10. Display text on an image (watermark)
It is possible to add text to an image. Here is the syntax used:
<%ImageWatermark(ImageFieldName, ImageWidth, ImageHeight, WatermarkText, HorzAlign, VertAlign, ForegroundColor, BackgroundColor, BackGroundOpacity, FontSize)%>
Here is the definition of the different parameters:
ImageFieldName: System name of image field used
ImageWidth: width of the image to insert in pixels
ImageHeight: height of the image to insert in pixels
WatermarkText: text to display on the image
HorzAlign: horizontal alignment of the text (Left, Center, Right). Default: Right
VertAlign: vertical alignment of the text (Top, Middle, Bottom) Default value: Bottom
ForegroundColor: color for the text (default: red). Format: #RRGGBB or constant
BackgroundColor: color for the text background (default: white). Format: #RRGGBB or constant
BackGroundOpacity: background opacity value between 0 and 1 (Default value: 0 = invisible)
FontSize: font size in pixels. Default: 12
Example:
<%ImageWatermark([Support], 300, 0, 'Welcome')%><%ImageWatermark([Support], 300, 0, 'Welcome', 'Left', 'Top', 'Red', 'White', 0.9, 14)%>
To insert a linefeed in your Watermark text, use the instruction \u000A <%ImageWatermark(ImageSystemName, 300, 0, 'Hello\u000Aworld\u000A!', 'Left', 'Top', 'Red', 'White', 0.9, 14)%>
One can add several data with Watermark, using the List() function. First indicate the separator, then list the data expected, separated by the separator defined. For example, you can extract both Latitude and Longitude from Exif data of an image
<%ImageWatermark([ImageSystemName], 300, 0, List(',', ImageExif(ImageSystemName, 'GpsLatitudeDM'), ImageExif(ImageSystemName, 'GpsLongitudeDM')), 'Left', 'Top', 'Red', 'White', 0.9, 14)%>
To extract the content of a field, use the Field System Name like below: <%ImageWatermark(ImageSystemName, 300, 0, FieldSystemName , 'Center', 'Middle', 'Red', 'White', 0.9, 14)%> 4.11. Display a text on each image of a multiple image field (Watermark) When an image field includes several images, it remains possible to apply Watermark text on each image of this field. To do so, you need to combine both the instructions Images() and ImageWatermark(), as below: <%Images(SystemNameImagefield)%><%ImageWatermark(200, 0, List(',', ImageExif( 'GpsLatitude'), ImageExif( 'GpsLongitude')), 'Center', 'Middle', 'Red', 'White', 0.9, 14)%>
NB The combined instructions must be inserted into a table in the template. The system name of the image field is to mention only once, in the initial Images() instruction. This system name should not be repeated in following instructions ImageWatermark() nor ImageExif()
Functions
Functions are not case sensitive, they can be written in either uppercase or lowercase and can also be combined. However, we advise you to use the “CamelCase” model for function names in order to standardize your tags.
The field names remain case sensitive in all cases.
5.1. Index()
This function returns a sequence of increment numbers in an array.
Listing Mode Example :
With 3 lines of data, the result is as follows:
5.2. MapLetter()
This function displays the letter of the pastille generated by the Google Maps API of a localization.If you use a Map() function for this specific point, the letter displayed on the map will be returned by MapLetter().
Syntax: <%MapLetter()%>
Example:
Result:
5.3. Formatting field values – Format()
The Date and Number fields can be converted using the formatting function to a specific data format.
Syntax : <%Format('Formats', FieldName)%> Or <%F('Formats', FieldName)%>
F() is also a shortcut for the same function of Format()< /em>.
The Format() variable can be:
Example : with Price = 19.3 and Date = #02/14/2012 3:14:20 PM #
Number field: <%Format('0.00', Price)%> (Result: 19.30)
Date field: <%F('yyyy-MM-dd', Date)%> (Result: 2023-02-16)
Current date and time – Date()
Syntax : <%Date()%>
Example: <%F('dd/MM/yyyy', Date())%> (Result: 02/14/2023)
Add or remove hours – AddHours()
Syntax : <%AddHours(SystemName,NumberHours)%>
Example : <%AddHours(DateTime, -6)%> (Result: 02/14/2023 06:00:00 instead of 02/14/2023 12:00:00)
Note: For adding a time the format is <%AddHours(DateTime, 6)%>.
Add days – AddDays()
Syntax : <%AddDays(Date, NumberOfDays)%> (NumberOfDays can be positive or negative)
Example : <%AddDays(Date(), -5)%>
If Date() returns #12/02/2023 12:14:34#, result: 07/02/2023 12:14:34
Add months – AddMonths()
Syntax : <%AddMonths(Date,NumberOfMonths)%>
Example : <%AddMonths(Created, -1)%>
Created = #12/02/2023 12:14:34#, result: 12/01/2023 12:14:34
Add years – AddYears()
Syntax : <%AddYears(Date,NumberYear)%>
Example: Warranty: <%AddYears(Created, +2)%>
Created = #02/12/2023 12:14:34#, result: 02/12/2023 12:14:34
5.4. Add()
This function allows the addition of numerical data and returns the sum. If some values are null or empty, they will be considered equal to 0.
Syntax: <%Add(Value1, Value2, Value3, …)%>
Example: Size: <%Add(Size1, Size2, Size3)%>
If Size1 = 5.3, Size2 = 2.3 and Size3= <null>, this function returns the result: 7.6
5.5. Manipulation of character strings
It is possible to apply a conversion to your file data in order to modify them into character strings or a conversion to text. A character string is expressed by the function <%String()%> or via the shortcut <%S()%>.
Concatenation
It is sometimes useful to concatenate character strings. To do this, we use the ‘+‘ operator. The given Price being a numerical value to be able to use our function we must therefore put them at the same level via the function <%S()%>.
Example : Price = <%S(Price) + 'Euros'%> i.e. Price = 14.32
Result: Price = 14.32 Euros
Apply a Uppercase or Lowercase conversion – ToUpper (), ToLower ()
Making a character string in uppercase is possible using the ToUpper() function.
Syntax : <%ToUpper(FieldName)%>
Example : Field1 = Here is an Example
<%ToUpper(Field1)%>
Result: HERE IS AN EXAMPLE
To make a character string in lowercase you must use the ToLower() function.
Syntax: <%ToLower(FieldName)%>
Example : Field1 = Here is an Example
<%ToLower(Field1)%>
Result: here is an example
Creating a list of elements – List()
If you want to create a separate list with characters with multiple arguments, use the List() function.
Syntax : <%List(separator, value1, value2 [,value n])%>
Example : Address: <%List(', ', Address, Postal Code, City, Country)%>
Address: 12 rue des églantiers, 17400, Lacanau, France
If any of the given elements is empty, the separator is omitted.
Note that this function can be used in combination with the Map() function to produce a valid address with different elements.
Split a string and put an index – Split()
You can extract a value from a character string containing a separator. The index starts from 0, i.e. the value 12 (Street number) in the example below.
Syntax : <%Split(string, separator, IndextoExtract)%>
Character string: Source string to split.
Separator: String that separates elements.
IndextoExtract: Index of the value to retrieve.
Example : Data: 12, rue des mines, Puteaux, 92800, France
Address: <%Split(Address, ',', 2)%>
Result: Address: Puteaux
5.6. Types of conversion
ToNumber()
In some cases it is necessary to convert a string to a number. For example if your data field is of type Text then the value it contains will be a text string. To convert it into a number to apply a calculation a function will be necessary.
Syntax : The function name is ToNumber(). There is a shortcut for this function which is N().
Example : Price = 127.4202
<%Format('0.00', N(Price))%>
Result: Price = 127.42
Note: The converted number from the original text format may contain a comma (,) or a decimal separator (.) as a separator. This will work in both cases.
ToDate()
Converting a text date to a real date using the ToDate() function. The shortcut for this function is D().
Syntax : <%ToDate(FieldName)%> or <%D(FieldName)%>. p>
Examples : Date = ‘02/14/2012 3:24:10 PM’
Date: <%F('dd/MM/yyyy', ToDate(Date))%>
Result Date: 02/14/2012
Date: <%F('MMM yyyy', D(Date))%>
Result Date: Feb. 2016
ToString()
In some cases, it is necessary to convert the format of the content of a field.
Syntax : The function name is <%ToString()%>. There is a shortcut for this function which is S().
Example : Price = 127.42
Price: <%S(Price)%>
Result Price: 127.42
Conditional statement
6.1. Displaying a value based on an evaluation – If()
When a condition is true, you can display text or another value. To do this, we use the If function.
Syntax : <%If(Field='Value', 'True', 'False')%>
The condition must return True or False. The condition can be made from a combination of and, or using '&&' for and, '||' for or.
Example : <%If(Booleen=='True', 'Yes', '')%>
If a Boolean field is equal to True then Boolean = Yes if other values then Nothing/Empty. So if my Boolean is equal to False then no values will be displayed. We can also make a sequence of several times the same conditions to encompass all the cases of a Boolean like: True, False and Nothing.
<%If(Booleen=='True', 'Yes', '')%><%If(Booleen=='False', 'No', '')%><%If(Booleen=='', '', '')%> It is also possible to use a short instruction to test and deal with the 3 possible values of the boolean: <%If(MyBoolean, 'valueifTrue', 'valueifFalse', 'valueifNot set')%>
Note: Between Word and Excel the apostrophe is not identical. So in the case of a copy and paste between your two document templates (Word to Excel) you will have to replace this one.
6.2. Determine if a field exists – Exists()
You can test for the existence of a specific domain name using the Exists() function.
Syntax : <%Exists('FieldName')%>
Example : <%If(Exists('SubmissionLatitude'), 'The data exists', 'The data does not exist')%>< /strong>
If SubmissionLatitude is not returned because it is not contained in the form, this gives: The data does not exist (n\' exists: syntax allowing the apostrophe to be integrated into the report) .
This function is especially useful for checking the presence of images.
Example: <%If(Exists('Photos#1'), Image([Photos#1], 180), '')%>
Here we check the presence of the first image for a photo field with a limit of 5 images maximum.
From then on if my photo exists, I would only have my first photo displayed in the mailing result.
6.3. Hide or show a section with a condition
The If() function allows you to test whether a condition is true, then display text based on the response. It is therefore necessary to provide for both solutions: the one where the condition is verified and the one where it is not. However, the Hide() function allows you to hide a text when the condition is verified and conversely Display() to display it.
You will always need to add a <%End%> tag at the end of the section (which will be hidden or displayed) to define a limit.
Please note that it will not be possible to display a current user in the reports.
Hide()
Hide a section where the condition is true.
Syntax : <%Hide(Condition)%>Text Section<%End%>
A Hide type instruction must always end with a <%End%> instruction. If the end statement is omitted the entire rest of the document is considered within a block.
Example :
It is possible to use a complex expression with parentheses and Boolean operators:
|| Is the OR
&& Is the AND
<> Is the operator does not match
== Is the equals
!= Is the operator Is not equal
Example : <%Hide((Field1 == 'value' ||Field1 =='value2') && Field3!= '') %> Some content is hidden <%End%>
You can also convert your variable into a character string with S().
Example : <%Hide(S(Champ1)=='')%> Result: <%Field1%> <%End%>
Display()
The reverse function is also available. Show a section where the condition is true.
Syntax : <%Display(Condition)%>Text Section<%End%>
Important: It is not possible to include both a Hide() and a Display() in the same condition.
DisplayRow()
This function is only available in a table. It displays a row of the table where the condition is true. The function must be placed in the first cell of the row concerned (or in other words, in the leftmost column of the row), before any other content such as text. In the example below, the entire row in yellow is displayed if the condition is true.
Syntax : <%DisplayRow(Condition)%>
Example:
Important: It is not possible to include both a Hide() and a DisplayRow() in the same condition.
6.4. Contains()
It is sometimes useful to know if one character string is contained within another. For this, the Contains() function is used.
Syntax : <%Contains(String, pattern)%>
The character string is the domain where the value is to be tested.
The pattern is the expression to find in the string.
This function returns True or False depending on the evaluation.
Example : <%If(Contains(Field, 'measurement'), 'MEASURE', 'NOTHING') %>
Result: Field = ‘The default measurement is taken’ gives the result: MEASUREMENT
Note: The comparison is case sensitive. To avoid sensitive comparisons, use the ToUpper() or the ToLower() function in combination with Contains().
Example: <%If(Contains(ToUpper(Field), 'MEASURE' ), 'MEASURE', 'NOTHING')%>
6.5. StartsWith()
A condition that allows you to test whether a character string begins with the specified value.
Syntax : <%StartsWith(String, Pattern)%>
The character string is the domain where the value is to be tested.
The pattern is the expression to find in the string.
This function returns True or False depending on the evaluation.
Example : <%If(StartsWith(Field, 'default'), 'Default value', '')% >
Result: Field = ‘Default measurement is taken’ gives result: Default value
6.6. EndsWith()
A condition that allows you to test whether a character string ends with the specified value.
Syntax : <%EndsWith(String, Pattern)%>
The character string is the domain where the value is to be tested.
The pattern is the expression to find in the string.
This function returns True or False depending on the evaluation.
Example : <%If(EndsWith(Field, 'END'), 'end', '')%>
Result: Field = ‘1234-END’ returns: end
Relationship
To be able to use the data from a form via a relationship, you need to create a table and insert your detailed relationship using the specific Relation() instruction.
Syntax: <%Relation('Relationship field label')%>
Example:
Date of intervention: <%DateIntervention%>
Under Articles:
Result:
Date of intervention: 05/09/2016
Under Articles:
In this case, the Anomaly Observed relationship is used to retrieve the sub-files.
If no sub-records are linked to your main record, then the entire table will be empty.
If the positioning support option is checked here is the syntax in the files below:
7.1. Sort a single relationship via a field
It may be useful when using a relationship to draw a sub-form based on a field. You can define its ordering via a second parameter.
Syntax ascending order: <%Relation('Relationship field label', 'SystemNameOfField')%>
Syntax descending order: <%Relation('Relationship field label', 'SystemNameOfDESCField')%>
Example:
Under Articles in ascending order:
Result:
Under Articles in ascending order:
Example:
Under Articles in descending order:
Result:
Under Articles in descending order:
7.2. Filter a multiple relationship via a subform
A relationship field can have several subforms. If you want to filter the relationship records based on the type of form, you must use the following syntax:
<%Relation('Relationship field label', OrderBy, StructureName='Form name')%>
OrderBy corresponds to sorting as explained in paragraph 7.1
Form Name matches the name of the form you want to keep
For example, we have a relationship Anomalies noted which has two subforms: Anomaly and Miscellaneous. To keep only the records from the Anomaly form, here is the expected syntax:
<%Relation('Anomalies observed', '', StructureName = 'Anomaly')%>
7.3. Quantifiable relationship
This option can only be used with or without the Multiple option, because it is not a data creation field but a linking one. This function goes hand in hand with the use of sheet diffusion, it allows you to display a quantity on a related sub-sheet.
To obtain the quantity you must include the syntax <%[Relation.Count]%>
Be careful with capital letters which are required to function.
Example:
7.4. Relationship relationship
It is possible to insert the relationship of a relationship into your report. To do this, you must create a first table and insert your first relationship there, then insert a second table inside the first table:
in the following example we will see how to insert a relationship from the sub-form into a first table, named "relationship label" then how to insert a relationship from the sub-form into a second nested table. another form itself being related to this sub-form. This last relationship is called "relationship relationship wording".
It is possible to go down to 3 levels in relationship, relationships (in short, the relationship of the relationship of the relationship) but not beyond. You will therefore need to modify your structures if necessary.
In other words, we insert a relationship relationship in a second step.
Then in the table, we insert following the relationship the fields of the relationship that we want to see present in the report. Then we repeat the operation for the relationship fields of the relationship that we want to see displayed in the report:
N.B. : The "Relationship element" is not persistent and cannot be used in a report.
Aggregation functions
Be aware of the specific syntax for using the following functions. Whenever there is an argument, it must be indicated with single quotation marks. For example: <%Sum('ToNumber(Price)')%>
8.1. Count()
Counts the total number of records for the same field using Count().
Syntax : <%Count()%>
Example : Total: <%Count()%> Registration
This statement can be used anywhere in the document.
8.2. Sum()
It is possible to add the field values.
Syntax: <%Sum('FieldName')%>
Note the apostrophes surrounding NameField. These are mandatory to identify the data.
When you are not sure if a field is numeric, you can convert the data to a number using the ToNumber() or N().
Example :Total: <%Sum('N(Price)')%> €
Result: Total: €134,321
8.3. Average – Avg()
To get the average of the field values, use the Avg() function.
Syntax : <%Avg('FieldName')%>
Same as Sum(), you can use the ToNumber() or N() function to convert text to number .
Example : Average: <%Avg('N(Review)')%> €
Result: Average: €14.32
8.4. Minimum – Min()
To get the minimum value of a series, you can use the Min() function.
Syntax : <%Min('FieldName')%>
You can convert the field to a number using the ToNumber() or N() function.
Example : Minimum: <%Min('N(Price)')%> €
Result: Minimum: €9.9
8.5. Maximum – Max()
To get the maximum value of a series, you can use the Max() function.
Syntax : <%Max('FieldName')%>
You can convert the field to a number using the ToNumber() or N() function.
Example : Maximum: <%Max('N(Price)')%> €
Result: Maximum: €86.65
8.6. Global Mapping – GlobalMap()
Allows you to display all the points in a location field of several records on the same map. This requires a Report Template in ListingMode.
Syntax: <%GlobalMap('LatitudeField','LongitudeField', ConnectPoints, Width, Height, mapType)%>
Explanation of fields:
LatitudeField and LongitudeField are the names of fields that contain latitude and longitude values. Note: they need to be decorated '' with simple quotes as the expression is an aggregation function
ConnectPoints: If true, it connects the dots, if false nothing.
Width : The width of pixels.
Height : The height of pixels.
MapType : 0 for road, 1 for air, 2 for mixed
Example : <%GlobalMap('latitude', 'longitude', true, 300, 300, 0)%>
Result:
In the case of a Relationship with a GlobalMap:
8.7. Graphical Displays – Chart()
You can use aggregation cards to display a bar chart or pie chart.
Pie chart and bar chart
Circular charts are made with values and labels.
Syntax: Chart(GraphType, AggregationFunction, LabelField, DisplayValues, width, height)
GraphicType: Maybe ‘Pie’, ‘Pie3D’, ‘Bar’.
AggregationFunction: Is a function like Count() or Sum(field).
LabelField: Is the name of a field that contains labels to be grouped by. These values are grouped using the Count() or Sum() aggregation functions.
DisplayValues: True indicates the display of the value above each element, False does not display the value.
Width : The width of pixels.
Height : The height of pixels.
Example :
<%Chart('Bar', 'Count()', 'CustomerName', true, 500, 300)%>
Result :
Example 2 :
<%Chart('Pie3d', 'Sum(Price)', 'ReparationType', false, 600, 300)%>
Result:
Example 3 :
<%Chart('Pie', 'Count()', 'TypeReparation', true, 600, 300)%>
Result:
Special cases
Certain features have specific syntaxes. Here is the lexicon of syntaxes used to exploit the data.
9.1 List
Isolate one or more values in a list
To isolate a particular level on a single tree list, simply define it:
By default to obtain the value we use: <%ListSystemName%>
My list result is A,B,C:
Get A: <%[ListSystemName.FirstLevel]%> or <%[ListSystemName.Level1]%>
Obtain B: <%[ListSystemName.Level2]%>
Get C: <%[ListSystemName.LastLevel]%> or <%[ListSystemName.LevelN]%>
For a multiple choice list without tree structure:
My list result is worth A | B | C:
Get A: <%[ListSystemName.Item1]%>
Obtain B: <%[ListSystemName.Item2]%>
Get C: <%[ListSystemName.ItemN]%>
For a multiple choice list with tree structure:
My list result is A,A1 | B,B1 | C,C1
<%[ListSystemName.Item1]%> = A,A1
<%[ListSystemName.Item1.Level1]%> =A
<%[ListSystemName.Item2.LastLevel]%> or <%[ListSystemName.Item2.Level2]%> = B1
NB: To obtain both the list element of the form and its parent, it is necessary to use a formula.
List side values
A list value can have other subvalues, such as an External Id, an Url or an image. It is possible to obtain the following items via direct mail:
By default to obtain the value we use: <%ListSystemName%>
However in our case we must use another method to extract the subdata via the syntax:
External Id linked to the list value: <%[ListSystemName.ExternalId]%>
Url linked to the list value: <%[ListSystemName.Url]%>
Image linked to the list value: <%Img([ListSystemName.Image], 50)%> (50 corresponds to the size of the image)
Color code of the list value: <%[ListSystemName.Color]%>
Value of the list element colored by its color: <%TextColor([ListSystemName], [ListSystemName.Color])%>
It is possible to use the item syntax to isolate a specific element on an additional value, such as:
External Id of the first result in the value: <%[ListSystemName.Item1.ExternalId]%>
Url of the first result in the list value: <%[ListSystemName.Item1.Url]%>
Image of the first result in the list value: <%[ListSystemName.Item1.Image]%>
Color of the first result in the list value: <%[ListSystemName.Item1.Color]%>
N.B. : The "List element" is not persistent, it cannot be used in a report.
An external id must not contain a special character in its value (example: the underscore "_").
9.2. User
Display information for all users in a field
It is possible to use information from a user field using the following syntaxes (in these examples, the system name of the field is UserFieldName):
Display the list of first names of selected users separated by commas
Show a comma-separated list of selected users' last names
Syntax: <%[UserFieldName.LastName]%>
Result: Johnson, Jordan, Bird, Chamberlain, Abdul-Jabbar
Show a list of selected users' full names separated by commas
Syntax: <%[UserFieldName.FullName]%>
Result: Earvin Johnson, Mickael Jordan, Larry Bird, Wilt Chamberlain, Kareem Abdul-Jabbar
Display the list of emails of selected users separated by commas
Show either the full name or email depending on the option chosen in the form
Syntax: <%[UserFieldName]%>
Show information for a specific user in a field
It is also possible to display information for only one user of the field. Here is the syntax to follow (in these examples, the system name of the field is UserFieldName):
Show the email of the first user in the list
Syntax: <%[UserFieldName.User1.Email]%>
Result: e.johnson@lakers.com
Show 4th user's last name in list
Syntax: <%[UserFieldName.User4.LastName]%>
Result: Chamberlain
Display the first name of the 2nd user in the list: <%UserFieldName.User2.FirstName%>
9.3. Workflow Status
To display the workflow status you can use <%[Submission.CurrentState]%>
Here is the list of accessible information:
State of the form in the default language: <%[Submission.CurrentState]%>
Is this a final state? : <%[Submission.CurrentState.End]%>
Color of the form status: <%[Submission.CurrentState.Color]%>
State name in French: <%[Submission.CurrentState.Name.FR]%>
State name in English: <%[Submission.CurrentState.Name.EN]%>
Syntax to display the current state with the associated color:
<%Display(Exists('Submission.CurrentState'))%><%TextColor('█',[Submission.CurrentState.Color])%><%[ Submission.CurrentState]%><%End%>
9.4. Duration field
It is not possible to retrieve the value of a duration field directly in a Word report. To overcome this constraint, it is possible to use a JavaSript formula field to retrieve the value of the duration field and integrate this formula field into a word report.
Example of JavaScript formula syntax to retrieve the value of the Duration field in the form:
var given_seconds = items['NOM_SYSTEME_DU_CHAMP_DUREE'] / 1000 ;
var dateObj = new Date(given_seconds * 1000);
var hours = dateObj.getUTCHours();
var minutes = dateObj.getUTCMinutes() ;
var seconds = dateObj.getSeconds() ;
var timeString = hours.toString().padStart(2, '0') + 'h' + minutes.toString().padStart(2, '0') + 'min' +seconds.toString().padStart(2, '0') ;
return timeString ;