HomeGuidesRecipesAPI
HomeGuidesAPILog In

Overview

The Data question makes requests to external systems and data stores. The yielded results are displayed via controls such as a drop down menus, tables and/or as content within generated word or PDF documents.

Actual Connections to external systems are configured in Manage and are different depending on the targeted technology of the source. However, once a connection is established, the configuration of the actual data question is common. For more information about data connections, refer to this page.

Often, the data question is configured in a way that does not require interaction from the user. For example, querying an HR system for the current user’s position and address and then display it on the screen.

Data Question Properties

On top of the normal question properties (question text, mandatory etc.), data questions require extra configuration to ensure the requests and results are made and displayed as intended. Each is described in the sections that follow.

Property

Description / Examples

Data Connection

The external system that the question should target. This should be configured in Manage.

Data Object

The particular entity or records that the question should target. Each Data Connection has one or more Data Objects that are configured in Manage.

Selection

Determines whether a single or multiple record/rows are to be selected from the dataset returned by the connection.
For example, a data connection might return a list of countries where the user is required to pick one result whereas another might return a list of symptoms where a user can pick one or more rows.

Appearance

How the returned data set is displayed within a form. The appropriate option is dependent on the data being returned and the selection type.
For example, drop down menus work well when there aren’t too many options expected in in the dataset and a data grid works well for when results should be displayed in a table like fashion with multiple columns.

Some display types require extra configuration. See links above for more information.

Display Field

For display types that show a single column of data, namely Auto complete, Drop-down list or Popup window, defines which field to actually display in the control.

Note: For display types that show several columns The Display Fields panel is available for more finite configuration of each column.

For more information, refer to Display Field/s.

Unique Field

Unique fields ensure that selected record(s) remain true between workflow steps and saves even though the returned data sets might change.

Choose a field/column that uniquely identifies each record. For example a “ClientId” might be a unique field for each client whereas Surname or DOB is likely not unique. This concept is often referred to as a primary key.

Without a unique field, SmartIQ will remember the index of the selected row. However, if the data set changes between saves or workflow steps, the record at index “x” might differ.

Maximum Results

Restricts the number of rows available in the results. The default value is 100 while existing questions remain blank or unrestricted.

Note: An additional override can be added to SmartIQ Manage, Product or Scheduler by adding MaximumRows: to the AppSettings section of the AppSettings.json file. For example, add "MaximumRows": "1000" to ensure that no data connection will display more than a thousand results. Designers can set a lower value in the Data Question.

Cloud environments are deployed with a default of 1000 records.

Default Type

Which record to pick by default when the form is first loaded.

The options are

  • (None)
  • Default Index - Specify a number and it will pick that item, e.g. 1 would select the first record or 0 would select no records
  • Default Value - Specify a value that is present in the unique field column and that record will be chosen, e.g. ClientId = 1234
  • Select Single Result – Automatically selects the record if only one record is returned via the data connection Note: The List Box, Popup Window and Search options automatically select all fields. If you want to clear all checkboxes by default, select one of the following under Default Type:
  • Default Index = 0
  • Default Value = None

Allow Data Upload

For use cases where the user can provide their own data (for example a CSV or XML file).

Auto Complete Display Type

Auto Complete display type displays results as the user types and shows results in a drop-down list for selection.

By default, the Auto Complete display type works by filtering a returned dataset to display only the rows that contain the term entered by the user, referred to as the [UserEnteredTerm]. For example, for a dataset containing a list of countries, if the user typed “United”, the results may include United Arab Emirates, United Kingdom, United States.

For data connections whose purpose is to receive partial search terms and return a dataset containing potential results, the Auto Complete display type supports passing the [UserEnteredTerm] to the data connection upon each user keystroke and displaying the result.

For example, if the data connection was an address parsing service and the user typed “12345 George”, SmartIQ would pass the [UserEnteredTerm] to the service and display the entire returned data set for selection. The options might include “12345 George Street” and “12345 Georges Terrace”. For more information, see User Entered Data Filters, after Data Filters below.

Auto Complete has the following additional properties:

Property

Description / Examples

Watermark

Watermark text that is shown before the user types anything into the field.

Min Characters

The minimum number of characters that the user types before results start to display. This is a useful feature when using data sources that have large amounts of data to retrieve.

Min Characters Message

This message will appear below the Auto Complete box if the user has less characters than the minimum character number specified above.
[MinCharacters] can be used in the message to reference the appropriate value. For example, "Enter at least [MinCharacters] characters to find a result".

No Data Text

This is the message that will display if no results are found. For example, “No Results Found For [UserEnteredFilter], try another term”

Data Grid Display Type

The Data Grid display type renders as a table with pagination, sorting, search UI elements.

Data Grids have the following additional properties:

Property

Description / Examples

Selection = (None)

This behaves like an uneditable data connection where it is not possible to select a row.

Sub Heading Field

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

Results Per Page

Determines how many rows will appear on each page of the data grid

Show Search

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

Allow Select All
(For Multiple Rows Only)

Display of "Select All" and "Select None" buttons allowing the user to quickly select or deselect all rows.

Periodic Refresh (Dashboard Only)

Enables the data source to continually update itself

Refresh Period

The time in minutes before the data source refreshes itself

Data Filters

Data filters are term(s) passed to the data connection so that appropriate result(s) are returned. For example, when retrieving client details, a “ClientID” filter might be passed to the data connection.

Filters must be configured to match what is expected by the data connection. Filters can be hardwired constant values or be sourced dynamically from previous answers in the question set. In the example below, the result will contain a data set with records with a fee greater than “75”.

Property

Description / Examples

Filter Field

Filter Field of the data object to pass a value to at runtime. For example, ID, CustomerNumber, TransactionDate etc.
Available options will depend on the data connection and the particular data object targeted by the data question. Options are configured by the data object properties in Manage.

Comparison

Options are:

  • Equals
  • Not Equal To
  • Greater Than
  • Greater Than or Equals
  • Less Than
  • Less Than or Equals
  • Begins With
  • Ends With
  • Contains – Will have this value, e.g., “united” will show United Arab Emirates, United Kingdom, United States
  • Doesn’t Contain – Will NOT have this value, e.g., “united” will result in any country that does not have united in the name
  • Contained In – Can be found within this value, e.g., “Dominican Republic” will result in Dominica and Dominican Republic
  • Not Contained In – Cannot be found within this value, e.g., “Dominican Republic” will result in any country except Dominica and Dominican Republic
  • Equals Null – Record has no metadata entry and shows a Null result, which is different from a blank result.
  • Not Equal to Null – Will have Response Metadata or Custom Fields

Type

Options are:

  • Parent Question – a reference to an answer in the question set that precedes this data question
  • Constant Value – a hardwired value entered by the Designer
  • Fragment Output – a reference to a fragment output that precedes this data question.

Example of the data filtered by using a Parent Question.

User Entered Data Filters

A user entered data filter will send request(s) to the data source as the user types. Thus this filter type is only available to the Auto Complete display type. Typically, this feature accepts partially complete search terms and passes them to the data connection for predicted results.

In the screenshot below, Auto Complete suggests suburbs after receiving a partial or full postcode.

A more common use for Auto complete with user entered data filters are address searches wherein a data source 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 connection to process the user entered filter into a real result.

📘

Notes and Best Practices:

  • This data filter approach is only appropriate for data connections that expect a partial search term. For example retrieving all the records that contain the character ‘a’
  • When you make a selection from a result set, the [userEnteredTerm] typed is saved in the answer file. When the project is saved and resumed at a later date, a subsequent call to the data connection is made again, hopefully returning the same result. However, if for any reason the data connection returns different results, previous selections may be lost.
  • In the client app, the question is only enabled in online mode.

How to add a user-entered data filter

Add a data source and configure its properties, specifically specifying ‘Auto Complete’ as the display type and choosing an appropriate data source.

Navigate to the Data Filters tab, the User Entered option will be an available type. Specify the data filter for which the [UserEnteredFilter] should be passed to.

Results Transformation

Results Transformation allows the manipulation of data after it has been returned from the data connection. Transformation may include removing unneeded columns, removing duplicate results (distinct) and aggregate calculations such as Average and Sum. The transformed results are displayed on screen and/or as content within a generated document.

Schema Fields and Aggregates

Schema fields and aggregate limits results to particular columns and (optionally) presents it with totals and statistics for data analysis. Often the resulting data is presented as an on screen report within a dashboard.

The following example shows how many students there are in each year level. To get this result, they added Year in the Schema Field and combined Student with the count function in Aggregate.

In another example, the user wanted to know how many students participate in each activity. To get this result, they added Activity in the Schema Field and combined Student this with the count function in Aggregate.

Aside from Count, the data aggregation can also be done with Average, Minimum, Maximum and Sum.

Distinct

The Distinct Results Only will filter out any duplicate records returned by the data connection. There are times when a data source column contains duplicate values. The distinct can be applied to the entire record or targeted at a specific field or a combination of fields.

📘

Note

If aggregate schema fields are in use, the distinct checkbox will be disabled as the aggregate functions already group records into distinct groups.

In the example below, the data set was limited to the Activity column and the distinct check box used to display only unique results.

You can also combine two or more fields which means the combination has to be distinct. Similarly, adding more fields does the same thing as distinct ensures that a combination of ALL entries are unique.

In this example, there are duplicates of Cricket or duplicates of Aaron, but there are no duplicates of Cricket AND Aaron.

👍

Tip

For a more organized way of presenting the data, use the Sub Heading Field to display the first column as a heading to group results.

Sort Fields

Sorting allows you to change the order of your data. Sorting priority can also be set up giving you the ability to sort multiple levels. For example, sort by School Activity, then School Year, then Name of Student. Use the up and down arrows to set the order of priority.

📘

Note

If Schema Fields have been added in Results Transformation, Sort Fields will only show those options to choose from. This includes aggregated data lists.

Display Field/s

For data question whose appearance type displays columns (namely Data Grid, Search or Popup), the display fields icon/panel appears to allow finite control of each column.

The default setting in Display Fields (right panel) is always All. Any changes made to Sort Filter and Results Transformation reverts the Display Fields settings to the default settings.

Property

Description / Examples

Display Field

Select from the available options. This will show the available fields that have been set up in Results Transformation, if configured. If not, all available display fields will show as options.

  • Formula_ - This allows a designer to enter a formula which will resolve per row of the data connection. 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.

Column Title

Specify a custom heading for the column. For example show “Name as Given Name”

Sortable (Data Grid Only)

Display sort ascending/descending arrows in the column title so the user can arrange the records in an order that is appropriate for themselves.

Dashboards

Data Grids in Dashboards show additional configuration types.

Launch Link

Selecting Launch from the drop-down list will change the text of any field into a link when the data grid appears in Produce. When clicked, the link will bring the user directly to the form. However, links may not appear if the current user does not have permission to access that form.

Operations Dropdown

Enabling this option causes a dropdown to appear in the selected display field/column of the data grid in Produce.

Operations or actions that appear are only those the current user can perform on the listed items and may vary depending on the data object type.

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

📘

Data Grid Properties

This option will only show in Display Fields if Selection type is None in the Data Question Properties.

Validation

📘

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

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

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

Answers and Data Fields

Each answer of a Data Question represents a single field from a record/row returned from a data connection. For example, "Id", “FirstName” and “data/client/Name”. The name of each field depends on the technology targeted by the data connection.

If the data field contains a value that conforms to a known structure, like a binary, date, number, html, etc., a data convert can be applied to the field so it can be processed as such at runtime.

For example, a data connection returns a text value of “2020-04-01”. However, it is known that the value is actually a date and should be displayed as 1 April 2020 or a date used in a days overdue calculation.

The available data covert types are explained in detail in the table below.

Data Type

Description

Date

Use when the data connection returns a string/text value that is actually a date.

A pattern is required to indicate which characters should be interpreted as the day/month/year and separator. For example the date “2020-04-01” has the pattern “yyyy-MM-dd”.

The pattern must match the string format exactly for the parser to work.

Note that the string " 5/01/2009 8:30 AM" cannot be parsed successfully with "M/dd/yyyy" because the time component is not included in the pattern. Additionally, the string "5/01/2009 09:00" cannot be parsed successfully with a pattern of "MM/dd/yyyy hh:mm" because the date string does not precede the month number with a leading zero, as the pattern requires.

For details of what characters can be used in a pattern see Custom date/time format strings

Number

Use when the data connection returns a string/text that is actually a number. At runtime, any white space will be removed from the value and the remaining text converted to a number.

Image

Use when the inserting a binary file from a data source into a generated document. Additional properties such as height and width can be configured so the image can be appropriately inserted.

At runtime, the value returned by the field must be one of (auto detected):

  • Actual binary data
  • A Base64 String
  • A URL from which to source the image data

Text

Removes any leading or trailing white space from the value only. Useful only for fixed width text data.

Html/Rich Text

Use when inserting HTML text into a generated document. Applies the formatting specified in the HTML in output file.

For example, a value of “Some bold and italic text”. Would appear as “Some bold and italic text.”

Attachment

Attaches the value as a file to the generated documents at submission. Useful for example, when passing the file to an action to send it to a recipient or system. Requires a filename to be specified for the file, mainly so the data can be opened with the right application once received.

At runtime, the value returned by the field must be one of (auto detected):

  • Actual binary data
  • A Base64 String
  • A URL from which to source the file data