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.

(*) 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:

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:

=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.

<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.

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