Intelledox

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

Table/View

Choose the Data Object to display (must be setup in Manage)

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

Determines if the question is to retrieve a single or multiple rows from the data source. Optionally, If the Data Grid Appearance option is selected (see below), a (None) Selection option is available. This must be selected when using Data sources in dashboards where the operations drop down and linking features are desired.

Appearance

The options are:
Auto Complete: (Single Selection Only) 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. Additional options are available when this option is selected (see below).
Drop-down List: 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’.
List Box: (Multiple Selection Only) A box containing the various data source entries in a select-able format.

Display Field

Defines which field to display to the end user when using a drop-down list or auto complete appearance.

Unique Field

Assign unique values.

Grouping Field (Data Grid only)

Groups the data in sections based on equal values of the defined field.

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

Allow Select All (Multiple Select "Search" and "Data Grid" Only)

Controls the display of "Select All" and "Select None" buttons to allow the user to quickly select or deselect all rows.

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

Watermark (Auto Complete Only)

A watermark to display on the auto complete box.

Min Characters (Auto Complete Only)

This option determines the minimum number of characters that a user must type into an Auto Complete data source field before the data source is triggered. This is a useful feature when using data sources that contain large amounts of data to retrieve.

No Data Text (Auto Complete Only)

This is the message that the data source will display if not enough characters are provided.[MinCharacters] can be used in the message to reference the appropriate value at run time e.g "Enter at least [MinCharacters] characters to find a result"

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 Dependencies

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

  1. 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 the field content into an image, if it is stored as binary or base64 data, or as a local 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.

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

  2. 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.
  1. 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 <AddressMail> node from the following XML structure, a custom key field can be used to ensure only the address according to a particular <customerID> 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

Data Grid

Designers can select a display type of Data Grid for a data source. This display type is similar to a search question type with additional options such as pagination, sorting, search and a formula display fields.

If you have Data Grid display type you will be presented with the following additional options on the data source panel:

Data Grids have the following specific properties

Selection type of (None)

This behaves like an uneditable data source, it is not possible to select a row.

Results Per Page

How many rows will appear on each page of the data source.

Show Search

Checking this option enables a search text box to appear above the data grid.

Grouping Field

Groups rows with the same data in the selected column.

Display field 'Formula'

This allows a designer to enter a formula which will resolve per row of the data source. This is potentially slow as it is not cached in any way and is done after data has been fetched if you have a lot of rows and a complex formula it could take a long time.

Column Title

This allows a designer to specify the column title.

Sortable

Arrows will appear if sorting options if this is checked

Live Refresh

Enables the data source to continually update itself

Note: Live Refresh applies to Dashboard projects only

Refresh Period

The timer in second before the data source refreshes itself

Data Grid Dashboard Operations

Operations such as (delete, reassign, unlock etc) and links (open, view history, load last answer etc) are available in Infiniti Dashboard Projects when using an Infiniti Data source with the Datagrid display type.

Data Grid Operations Apply to Data Grids in Dashboard Projects only

These data grids and data sources can be used in Content project types, but the Links and Operations options will not appear.

When designing the data grid

In the Display Fields for a data grid of the right type two additional fields will appear

  1. Link - this is a drop-down with only a couple of options (eg: Launch Project, Launch History etc) that vary from the data object to data object. Adding a Link to a display field will cause a link to that behavior to appear on the display field when the data grid appears in Produce. 
    • The appropriate selections must be made when adding or updating the Display Field item. If Links are available a column will appear to show which Display Field the link is attached to and which Link is attached.
    • If the link type is Launch for a new project, then an Include Parameters option will appear. If this option is checked, an input table will be available which can be used to add parameters to the link.
  2. Operations Dropdown - this is a checkbox which when selected causes a dropdown to appear in the selected display field column of the data grid in Produce. The drop-down only appears if there is an operation available on that line of the data object, and only available operations will appear - possible operations include Delete, Reassign, Unlock etc.

When the data grid is displayed

  • Links will appear on the text of any field of a Dashboard project data grid that has had a link type selected. If Link Parameters have been set up for these links they will be passed through to the new form when the link is click.
    • After the link is clicked, the back button will return the user to the Dashboard project they came from
    • Links may not appear if the current user does not have permission to link to that list item (eg: it is locked by another user etc)
  • Operations appear as a drop-down in the selected field/column of any Dashboard project data grid that has had Operations Dropdown checked
    • The form designer does not control what items appear in the Operations drop-down. Depending on the data object type the operations may vary - see the list below
    • For User type data objects, the operations that appear are only those the current user can perform on the listed items. 
    • For Admin type data objects, all possible operations will appear but will not execute if the current user does not have permissions to perform those operations.

User: Answer Files

Launch Saved Answer

User: Documents

Download Document

User: Form Activity

Launch Project; Launch History

User: Available Forms

Launch Project

User: Individual and Group Tasks

Launch

User: Individual Tasks

Launch

User: Group Tasks

Launch

Admin: In Progress Tasks

Launch

Possible Operations:

User: Answer Files

Resubmit; Delete

User: Documents

Delete

User: Form Activity

none

User: Available Forms

Reload the last answer

User: Individual and Group Tasks

Unlock; Reassign; Delete

User: Individual Tasks

Unlock; Reassign; Delete

User: Group Tasks

Unlock; Reassign; Delete

Admin: In Progress Tasks

Unlock; Reassign; Delete

Launch Link Parameters for Data Grids (Dashboard Projects Only)

Data Grids may include parameters as part of the launch link. This works for Infiniti Data Connector (User Available Forms).

When the launch link type is selected, the option to Include Parameters is available. Checking this option opens an input grid to which you can add parameter names and values. The values can be entered as formulas and question references as needed.

When the Data Grid is displayed in the dashboard, the parameters will be included in the link URL and will be integrated by the newly opened form if required.

Updated 2 months ago

Data


Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.