Data Source - Question Type

Data source questions provide a way of including stored data in your generated document. Data source questions retrieve records or rows from an external source such as a database table, view, stored procedure, XML data source, Web Service or other data system or line of business applications.

In some cases, a data source may be called on to retrieve a single data row, while multiple data rows are needed for other situations. For more information about managing data sources, refer to Data Source Selection Types and Display Types.

The data source question allows a wide range of scenarios to connect to, join, filter and sort data in a variety of ways to populate generated documents or to retrieve values for drop-downs or search in your web forms. For more information about managing data sources, refer to Data Filtering and Sorting.

Connections to data sources are handled in Manage. For more information about managing data sources, refer to Data source connection and connection types.

Often data records can be selected and consumed in a document without any user interaction. This is useful both for web form driven documents and for documents that are automatically generated without any user interaction.

Key fields defined in ‘Manage’ are used to filter and sort the data, which enables large data sets to be reduced to a more manageable and focused list. This can be used to join multiple datasets using primary/foreign key relationships.

How to add a data source question

1. From the toolbox, drag and drop a data source question onto a page, section column or tab and set the question’s properties as below.

Property	Description / Examples
Question Text	Type in the question you want to display.
Text Position	The options are: Top Left (None)
Data Source	Choose from: Infiniti Infiniti II LMSDev Repeating Page/Section
Visible	Determines whether the records retrieved from the data source are displayed to the end user in ‘Produce’ for selection, or included in the document without user interaction. Check or uncheck as needed. This option is checked by default
Selection Type	Determines if the question is to retrieve a single or multiple rows from the data source.
Appearance	The options are: Auto Complete: () Provides a list of data which contains the letter or word that has been entered into the text box from which the required entity can be selected Drop-down List: For single row data sources only, this presents the data in a drop-down list. Data Grid: Presents a search interface as above in a separate pop-up window useful for using within smaller question areas such as columns. - This will open up a new field called Grouping Field. When displayed this field will group the rows. Pop-up Window: Presents a search interface as above in a separate pop-up window useful for using within smaller question areas such as columns. Search: Presents a search interface with search fields available based on specially marked key fields, as set up in ‘Manage’.
Display Field	Defines which field to display to the end user when using a drop-down list.
Unique Field	Assign unique values.
Value Field	Defines which field to use to identify the selected record. This should be set to a field containing unique values, such as a primary key, e.g. ‘ClientID’.
Mandatory	This option lets you determine if a question is mandatory or not. This option unchecked by default.
Default Type	The options are (None) Default Index *Default Value
Help Text	Help text can be added to give additional information about what is being requested in the question. This is a rich-text box.
Comments	Check this box to allow comments. It is unchecked by default.
Style	The options are: Alert (error) Alert (info) Alert (success) Alert (warning) Jumbotron Label (default) Label (error) Label (info) Label (primary) Label (success) Label (warning) Well
Find Discrepancies	Clicking this link will open a pop-up window that will show any dependencies that this label has.

(*) When the user entered filter has yielded no results a customisable message can be displayed.

For example, when you search for people you might want to prompt the user with 'No Staff found containing asdfasgasgfdasg in their surname, try another search term'.

To configure the message use the property [userEnteredFIlter] to reference what the user has typed at runtime.

No staff found containing [userEnteredFIlter] in their surname, try another search term.

Repeated Placeholders with Multi-Row Data Sources

Data can be used to drive a repeated placeholder, which copies down a block of the document or set of table rows for each data row returned by the data source. For each data row, the repeated placeholder and all its contents are copied down and populated by each data field answer added to the data source question.

It’s also possible to nest child data within a parent multi-row data source, and have the child also repeat its own placeholder within the parent block. A good example of this is presenting a list of classes each with its own list of students.

Document Preparation and Bookmarking

For single record data sources, insertion or selection placeholders can be used throughout the document to specify where the record’s data fields are to be inserted. For example:

When using a multi-row data source, insertion or selection placeholders are once again used to specify where the individual data fields of each record are to be inserted. Additionally, a repeat placeholder must be created to specify which block of document content to repeat for each data row that is retrieved. The repeat placeholder can surround one or more table rows, one or more paragraphs or even span pages. For example:

📘

Note

Questions that are not visible to the end user in ‘Produce’ generally require a data filter to ensure that only the correct record is included in the document

2. Infiniti automatically adds an answer to the new question. Set the answer’s properties as below:

Property	Description / Examples
Data field	The data field or column of the table, view, query or method to retrieve from the data source. Select a data field, or one of the special types as follows: •[Formula]: A calculation to perform on the row, e.g. ‘[AnnualPremium]/12’. •[Aggregate]: Multiple records selection type only. A calculation to perform on multiple records, e.g. ‘Contains([q1], "Test")’. •[RowCount]: The total number of records selected. •[RowNumber]: The index of the current record.
Data Convert	Allows you to manipulate the data retrieved into a useable format. The following conversion options are available: •Date: converts a field into a date for calculations or formatting, based on the Pattern provided. See Pattern below for more information. e.g. convert ‘20200101’ to ‘Wednesday 1 January 2020’. •Number: converts a field into a number. e.g. convert the text ‘1’ to the number 1. •Image: converts a field into an image, if it is stored as binary data in the field or as a filename or UNC path reference. e.g. convert the filename ‘c:\temp\img.jpg’ into an embedded image. •Text: converts a field into a text format. As all conversions trim any leading or trailing white space characters from the data, this conversion is useful for trimming string data before use. e.g. convert the fixed width ID field ‘9060•••••••••• ••••••••••’ to the trimmed text ‘9060’. •Html/Rich Text: converts a field with html tags into a text with the tag properties applied to it •e.g. convert the text 'Example for Bold' to 'Example for Bold'
Pattern	Date data convert only. The pattern in which a date/time value is retrieved from the data source using date and time components similar to format strings. For example, a date stored as ‘20113101’ can be matched with a pattern of ‘yyyyddMM’. Similarly, a time stored as ‘1801’ can be matched with a pattern of ‘HHmm’. Refer to Format strings for a reference of component syntax to build your patterns.
Max Height/Width	Image data convert only. Specify the maximum height and width for the image.
Units	Image data convert only. Specify the units in which the max height and width are specified.
Stretch to Fit	Image data convert only. Stretches the image to exactly fit the size specified by max height and width. Note that images are always proportional within the boundaries specified.

3. From the Placeholders tab, drag and drop any placeholders onto the answer that are to be populated by the data field.

4. Add additional answers for each data field required, repeating steps 2 and 3.

Data Source Validation

To ensure the quality of retrieved data, Infiniti enables you to apply validation rules to data source questions. If a data field fails validation, Infiniti will display an error message.

📘

Note

Often Data Source validation is used in automatic or bulk generation scenarios where data is passed by a third party system for document generation without user interaction

Data validation rules are applied in the same manner as text field validation; for more information see Text Field - Question Type.

Data source conditions and parents

Data source questions can be used to control if pending questions are displayed or hidden from the user ( i.e. to set conditions). The behavior differs lightly between single and multiple-row selection types.

Single Row Data Sources

If the data source question is retrieving a single record form its source, a condition can be set by selecting the conditions tab of the child question and using the dialog to configure the condition, or with a drag and drop motion.

📘

Note

•Conditions are case sensitive
•If you wish to have the child question on the same page as its parent the data sources display type must be Drop Down.

Multiple Row Data Sources

A multiple-row data source question works much the same as a single-row data source however, when the condition is evaluated it will check all selected rows against the condition. If any selected row meets the rule the condition it will be evaluated as true.

📘

Note

•Conditions are case sensitive.
•Child questions must be on different pages.

Using unique fields for a data source question

When not to use a unique field##

The unique value field defines which field to use to identify the selected record. This should be set to a field containing unique values, such as a primary key, e.g. ‘ClientID’.

Using a scenario where you want to build a repeating section that’s driven by an employee record using a data-driven repeating section.

If you selected the First Name field as a unique value in the data source question. Because this isn’t a unique field as the data in the column can potentially be the same e.g. duplicate first names. You would get an inconsistent result and that’s because Infiniti is matching up the Rows with the First Name data fields.

In Design I have set the unique field to the First Name field (which is an incorrect setup). I’m using that Data Source to drive my employee details section and displaying the fields with labels.

On the first load this loads as expected, the client IDs and first name are displayed correctly

However, If you then change what employee records you want to bring back. The data-driven repeating section will now be incorrect, the order will be wrong and the employee names will match up against the wrong ID’s etc. The reason why this happens is that the data source is updating by a field that isn’t unique. The fix to this issue is changing the unique field to Employee ID as that is truly a unique field and the rows will update accordingly.

When to use a unique field##

Use a unique field if the data source results can change between runs. The default is to save a selected index which assumes the data is the same between runs. It is also the fastest as it doesn’t need to fetch the data while setting up the question. Unique fields save the value of that column to the answer file, and when it is reloaded it will attempt to find the row that has that value again. Where ever it might be.

📘

Note

Remember a unique field must be truly unique there can’t be duplicates of the same value in the data field

Answer as a Display Field

In the Display Field drop down, "[Answer]" is available for selection. If a user selects "[Answers]", an additional drop-down will appear called 'Answer Field'. Answer field lists all the answers which have been added to a data source question.

The advantage of selecting an answer field is that you can use "[Formula]". Within formula you can use any supported formula, the most useful being "=concat( )".

Infiniti Data Source Example

1. Add first name answer
2. Add last name answer
3. Add new answer and name it Concat.
4. In the Concat answer, select formula.
5. In the formula text box, type in =concat and then use the question reference tool to select the answers.

The resulting formula would look something like this:

Formula

=Concat(q1.First_Name, " ", q1.Last_Name)

Auto complete with user entered data filters (query data source for suggestions as user types)

User-entered data filters allow data sources to be called as a user types and displays the resulting records as a list of suggestions for the user to (hopefully) find an appropriate item to select. They work in conjunction with the ‘Auto Complete’ data source display type as depicted in the screenshot below that suggests suburbs based after receiving a partial or full postcode.

A typical use for a user-entered data filters are address searches where a data source is responsible, accepts part of an address, searches against what is typed, and attempts to match it to a real address. For example, a user might type ‘123 Fake Street’ but select ‘123 Fake Street, Hartford, CT 20600’ as their actual address. In all cases, it is the responsibility of the data source to process the user entered filter into a real result.

Notes and Best Practices:
The auto complete question does not need a user filter. Where a user filter is not specified the entire list is pre-loaded and the suggestion list is populated based on a string match. For example, a small data set such as a list of countries the data can be loaded in its entirety and a search term such as ‘United’ might suggest United States of America, United Arab Emirates, etc...

Use this question type sparingly. The existing auto complete data question does a text match of an existing dataset and is sufficient for many scenarios.

When you make a selection from a result set, the search term you typed is saved in the answer file. When the project is saved and resumed at a later date, the call to the data source can be made again to return the same result. However, if a change has been made to the database or if you delete the information and begin a new search, you may get different results.

In the client app, the question is only enabled in online mode.

Not all data sources are designed to return a result set from a partial search term. Only data sources that suit this scenario are appropriate.

How to add a user-entered data filter

1. Open or create a new Project in Web Design.
2. Add a data source and configure its properties, specifically specifying ‘Auto Complete’ as the display type and choosing an appropriate data source.

3. Navigate to the Data Filters tab and specify the data filter for which should be populated as the user types (i.e. a User Entered).

Filter fields, custom fields and display fields explained

Filter Field

A Filter Field is a field that you would filter records by. For example, ID, CustomerNumber, TransactionDate etc. The fields specified as Filter Fields appear in Design within the Data Filters Tab.

Display as Filter Field Check Box

If you're using a data source search question, Filter Fields can appear in the Produce interface allowing end users to search for a particular record(s). Use this check box to configure this field as such.

Custom Fields

Custom fields are generally associated with XML data sources and allow data to be filtered by a parent node or attribute. For example, when accessing the node from the following XML structure, a custom key field can be used to ensure only the address according to a particular is selected.

Text

<data>
 <customer>
    <customerID>1</customerID>
    <addressMail>
      <addressLine1>123 Fake Street</addressLine1>
    </addressMail>
  </customer>
  <customer>
    <customerID>2</customerID>
    <addressMail>
      <addressLine1>321 Long St</addressLine1>
    </addressMail>
  </customer>
</data>

Display Fields

Display fields allow the administrator to specify which fields will be seen by the end user in Produce when the ‘Search’ Display type is used. This feature is useful for hiding unnecessary or sensitive information.

Split and manipulate fields returned from data sources

If your data source returns a data field and you need to remove unneeded characters, Infiniti has three formula functions to help manipulate the field:

• left()
• right()
• substring()

Left() and Right() take the leftmost/rightmost characters of a field, whereas subString() extracts the inner parts.

For example if the field 'AABBCC (OCT 2005)' was returned by a data source:

• left([YOURREF],6) would return 'AABBCC'
• right([YOURREF],10) would return '(OCT 2005)'
• subString([YOURREF],9,8) would return 'OCT 2005'
• use the trim() function to clean up any unneeded white space.

Trim leading or trailing spaces from database fields

Often, fields retrieved from a data source contain leading and trailing spaces. A few options are available for removing such spaces from fields returned from a database.

📘

Note

Options are not limited to the data source question and can be applied to most other question types.

Option 1 – Text Data Convert

For each answer of your data source using the Data Convert drop down to convert it to the ‘Text’ type. This feature removes any spaces before the field is processed so any subsequent references to this answer will not contain extra spaces.

Option 2 – Trim Format String

Select or enter ‘TRIM’ in the format string field in of the answer to remove the extra spaces this will be removed when the field gets inserted into the document.

Option 3 – Trim() Function

The trim() function can be used by itself or nested within another function.

Text

trim([Q1.FamilyName])
concat(“Your Surname is: ”, trim([Q1.FamilyName]))

Using unique fields for a data source question

When not to use a unique field.

The unique value field defines which field to use to identify the selected record. This should be set to a field containing unique values, such as a primary key, e.g. ‘ClientID’.

Using a scenario where you want to build a repeating section that’s driven by an employee record using a data-driven repeating section.

If you selected the First Name field as a unique value in the data source question. Because this isn’t a unique field as the data in the column can potentially be the same e.g. duplicate first names. You would get an inconsistent result and that’s because Infiniti is matching up the Rows with the First Name data fields.

In Design, set the unique field to the First Name field (which is an incorrect set up).The example is using that data source to drive the employee details section and display the fields with labels.

On the first load, it will load as expected, the client IDs and first name are displayed correctly.

However, If you then change what employee records you want to bring back. The data-driven repeating section will now be incorrect, the order will be wrong and the employee names will match against the wrong ID’s etc. This happens because the data source is updating from a field that isn’t unique. The fix to this issue is changing the unique field to Employee ID as that is truly a unique field and the rows will update accordingly.

When to use a unique field.

Use a unique field if the data source results can change between runs. The default is to save a selected index which assumes the data is the same between runs. It is also the fastest as it doesn’t need to fetch the data while setting up the question. Unique fields save the value of that column to the answer file, and when it is reloaded it will attempt to find the row that has that value again. Where ever it might now be.

📘

Note

Remember a unique field must be truly unique; there can’t be duplicates of the same value in the data field