Multiply the entire PDF template or just a part
It is possible to receive the entire PDF template or just a selected area within the PDF template, for each selected record of the form, in an edited copy in the finished PDF document.
Multiply the entire PDF template
By default, the PDF template is processed completely once for each selected data record in the form. If you have selected 10 data records, the PDF template will be copied 10 times and the placeholders in the copy will be replaced with the data from the current form data record. These replaced final texts are added, one by one, to the back of the PDF document.
Note: Depending on the setting in the PDF template on the "PDF Settings" tab, "Single page per data record" parameter, a finished PDF template is added for each selected data record of the form, with or without page switching.
Multiply only part of the PDF template
Copying the entire PDF template once for each selected record of the form is not always the desired behavior. Sometimes you want, for example, only one new additional table row or a single additional list entry to be inserted within the PDF template for each record in the form.
This is also possible and very easy to implement. All you have to do is insert the small class="loop" HTML attribute in the right place.
Please also take a look at the example template “Orders” in the “Fruit” demo form. You just need to write the class="loop" HTML attribute in the HTML element that you want to multiply. To do this, toggle the editor to the HTML Code view.
Please note the following small restriction: These loop elements must not be nested.
Note: Within an HTML element with the class="loop" attribute, you may 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 truly comprehensive tool. This is the purpose of the many possible SQL statements that you create on the “SQL Statements” tab and can reference in the PDF template by name.
Integrate any additional data into the PDF document
In addition to the primary data record selection of the form, you can integrate any other data from other tables in the database into the PDF document.
This secondary data can come from:
- additional data of the same form,
- data of another form,
- any data from the Joomla database.
Placeholders in the secondary SQL statement refer to the current primary data set
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 record (${item:field-name}). See details below.
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%‘.
The “Test” button
With the ‘Test’ button, you can check before actually generating the PDF document whether the SQL statement is at least error-free and how many results it delivers. However, the ‘Test’ button can only be used if there are no placeholders in the SQL statement. The placeholders in the SQL statement are only replaced later at runtime. When you click the “Test” button, a placeholder is not replaced, causing an SQL format error. A corresponding error warning appears.
This SQL does not produce an error:
select name, username, email from #__users where id = 234;
Where the random value ‘234’ is the ID of an existing Joomla user.
This SQL produces an error:
select name, username, email from #__users where id = ${item:created_by};
We therefore recommend developing the SQL statement with fixed values first. Only towards the end do you insert the desired placeholders in the necessary places, when everything else is already working correctly.
For somewhat larger and unwieldy SQL statements, we recommend developing and testing in an environment explicitly designed for this purpose. These include the “MySQL Admin” or a dedicated development environment (IDE). In this case, too, only insert the desired placeholders at the end, in the necessary places, if everything else is already working correctly.