Iterate through records
Duplicate the complete template
By default, the PDF template is completely processed separately for each data record. If you have around 10 data records, the template will be called up 10 times, the placeholders will be replaced with the current data record and these final replaced texts will be appended one after the other.
Duplicate a part of the template
This is not always the desired behavior. Sometimes, for example, you may want only one new additional table row or one additional list entry to be inserted within the document for each record.
This is even very easy to do. All you have to do is insert a small HTML attribute in the right place.
See also the “Orders” example template in the “Fruit” demo form. You just need to append the class="loop" attribute to the HTML element to be multiplied. Toggle the editor to HTML Code view to do this.
Please note the following small restriction. These loop elements cannot be nested.
Note: Within an HTML element with the class="loop" attribute, you must not have another HTML child element with a class="loop"< /span> attribute.
Secondary SQL statements
A fairly advanced feature
We want to provide you with a really far-reaching tool. That is the purpose of the many possible SQL statements that you can create on the “SQL statements” tab and reference in the PDF template by name.
Integrate any additional data into the PDF document
In addition to the primary data selection, you can use it to integrate any additional data into the PDF document.
This secondary data can come from:
- additional data of the same form,
- data from another form,
- any data from the Joomla database.
Wildcards refer to the current primary record
These secondary SQL statements enable a second, possibly dependent, data selection. Because in the secondary SQL statements, you can use placeholders to refer to the fields of the current primary data set. The secondary SQL statements are applied both when you create a PDF document in the administration and when you create it in the frontend.
Two types of secondary SQL statements
Process type “Form”
If you want to include more data from the same form, select the form process type for the secondary SQL statement. You store an SQL WHERE partial statement for the “Form” process type. These SQL WHERE sub-statements only refer to the part of a complete SQL statement that refers to the query conditions behind the so-called ‘WHERE’ keyword.
Process type “free”
If you want to include any other data from the database, select the process type “free” for the secondary SQL statement. For the “free” process type, you store a completely normal, complete SQL SELECT statement.
The 1:n relationship
Both process types of secondary SQL statements (“form” and “free”) are applied once for each record of the primary selection. For their part, the secondary SQL statements can also return multiple data sets.
By this simple fact, you can create a 1:n relationship from the current primary record (1 record) to the secondary records (n records) selected by the secondary SQL statement.
Bind secondary SQL statements to an HTML element
You can use the data selected by the secondary SQL statements in your PDF document. You must bind the secondary SQL statement to the desired location in the document.
To do this, simply place the following two attributes in the desired HTML element (e.g. a tr element):
- a class="sql" attribute to identify the repeat area
- a id="SQL statement name" attribute specifying the secondary SQL statement to apply.
Placeholder as usual also for secondary SQL statements
Then insert placeholders in the usual syntax within the HTML element as usual. The placeholders refer to the names of the secondary SQL statements used, i.e. to the secondary SQL statement selected with id="SQL statement name". See also “Placeholders for data from SQL statements” below.
The nested HTML element is duplicated
Suppose you have a 1:n relationship between the primary record selection and the secondary record selection. The relationship is practically created by executing the secondary SQL statement with the current data of the primary data set.
Now the correspondingly marked or bound HTML element is automatically copied for each secondary data record:
- used as a template,
- its parameters replaced and
- continuously appended to the existing text.
Use the “HTML code” view of the editor
It is best to switch to the “HTML code” view in the editor with the “Toggle Editor” button in order to insert the attributes. Take a closer look at the “invoice” example PDF template of the “register” form. Pay particular attention to the “SQL Statements” and “Document” tabs and the PDF preview.
Note: You may only embed an SQL statement once in your document template.
We remember the last data record
During the replacement of placeholders, the data values currently being used for replacement are automatically noted as the “last values”. The last replaced values therefore remain until the end of the document creation. Of course, this applies to each SQL statement used.
You can therefore continue to use these last values at a later point in the HTML template. For example, at the very end of the PDF template or in the areas of the page header and page footer.
Note: Please note that only the last data from the last run per SQL statement is saved.
This reuse of the saved data is particularly interesting for SQL statements that only supply exactly one data record. The last data set is also the only data set here. Its data can be replaced anywhere, anytime and across the entire PDF template.
These ‘one off’ records with only one record can be very useful. This allows you to use any necessary sums about all imaginable things or special one-time information from the database directly in the HTML template.
Merge data from different forms
You can use SQL statements to combine data submitted using different forms into one PDF document.
An example
You have an initial form that you use to collect general user data. And you have a second form that users regularly use to place their orders.
This data could now be combined into a single PDF document. For example, to create an overview of all orders from a customer including the stored customer data.
Take a closer look at the “invoice” example PDF template of the “register” form for a concrete example. Pay particular attention to the “SQL Statements” and “Document” tabs and the PDF preview.
Note: It is not possible to nest secondary SQL statements. An HTML element with class="sql" attributes must not have HTML child elements with a class="sql" attribute. Also no HTML grandchild elements.
Placeholder for data from SQL statements
The format for wildcards is the following: ${SQL-statement-name:SQL-field-name}.
An example
You have created an SQL statement named “sum” on the “SQL statement” tab. This statement reads:
select round(sum(F2179 * F2180), 2) as sum, round(sum(F2179 * F2180) * 0.19, 2) as tax from vf38_visforms_143 where id < 10;
The placeholders for using the data from this statement are: ${sum:sum} and ${sum:tax}.
Placeholders in the SQL statements
SQL statements can also contain placeholders themselves, which are replaced with the current values. This replacement always takes place immediately before the SQL statement is executed.
These placeholders can reference values for:
- the Joomla Framework object User
- ${user:parameter-name} and
- the Joomla Framework Object Input
- ${input:parameter-name}.
- Data from the primary data selection that is currently being processed
- ${item:field-name}, such as ${item:created_by}.
In the SQL statements for data selection, you can alternatively use instead of
- the concrete ID field names F111 like ‘%value 1%‘
- also use Visform’s placeholder ${maiden} like ‘%value 1%‘.