Data Question
The Data question makes requests to external systems and data stores. The yielded results are displayed via display types such as a drop down menus, tables, etc, and/or as content within generated word or PDF document templates.
Usage
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.
On top of the normal question properties (question text, mandatory etc.), data questions have properties that require extra configuration to ensure the requests and results are made and displayed as intended.
Turning on Troubleshooting mode may be helpful for data sources
To ensure that the data question is populated with data and that the correct rows are selected.
Actual Connections to external systems are configured in Manage Data Connections 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.
If your data has specific formatting eg HTML then make sure to pass it to a rich text question otherwise its formatting will not be converted.
Properties
| Property | Description / Examples |
|---|---|
| Data Connection | The system that provides the question with data, either a repeating section or a database. Repeaters as data connections will provide their questions as the data fields. Database connections should first 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 Display Types for more information. Drop-down List (Single Row Only) List Box (Multiple Rows Only) Pop-up Window Search |
| 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. Cloud environments are deployed with a default of 1000 records as a fallback; this cannot be exceeded by setting a higher value at the question level. Note: Result count limiting takes place after other operations (e.g. filtering, sorting). For best results, ensure that data filters are sufficient to return fewer rows than the limit value. Of particular note is the User Entered Data Filter which can be used to ensure that Autocomplete questions do not hit the row limit unexpectedly. |
| 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, for example, ClientId = 1234. Select Single Result – Automatically selects the record if only one record is returned via the data connection. 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, 0 or none |
| Allow Data Upload | For use cases where the user can provide their own data (for example a CSV or XML file). |
Auto Complete and User Entered Filtering
Auto Complete display type filters data as the user types and displays results in a drop-down list for selection. This is also known as user entered filtering and is exclusive to the auto complete data question type. This data filter approach is only appropriate for data connections that expect a partial search term.
Use caution when using an auto complete type to filter large datasets
Make sure to have filters set up to minimize the possible number of returned results before the data base is queried.
Using auto complete to call and filter all of the results from a large database will at best only search a limited number of records and at worse be incredibly unresponsive, as the question loads and filters on the maximum dataset SIQ can handle. Maximum Technical Limitations and Recommendations
The Auto Complete display type works differently depending on the filter type applied.
For a parent question, constant value, or a fragment output filter type the question will gather a dataset from a database first, then filter based on the filters provided to display only the rows that contain the term entered by the filters value.
For a User Entered filter type, the database connection will be queried and display the predicted result after every key stroke, so it is recommended to limit the filter setting min charters property to 3. The recommended filter comparison types for a User Entered filter in Auto Complete are Contains or Begins With
Make sure to apply the User Entered filter type
With a User Entered filter type applied the question will retrieve a dataset filtered by what the user has entered before applying all other filter types.
Without applying the User Entered filter type a user Entered string will only filter on the dataset that has already been retrieved and filtered by the other filter types.
This is a common mistake that causes performance issues.
For example a large database with countries and addresses could not be filtered reasonably using anything except a User Entered filter type. If the user types “United” against the dataset, the results may include United Arab Emirates, United Kingdom, United States.
| 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 User Entered filter, try another term” |
When using auto complete with a Custom Data Object the data must contain the auto-generated ObjectId, so this should be included in any Results Transformations.
When resuming project a subsequent call to the data connection is made, if for any reason the data connection returns different results, the previous selections may be lost.
When a selection from a result set is made, the User Entered text typed is saved in the answer file when the project is saved. When resumed, past selections will automatically make a subsequent call to the data connection.
In the mobile app, the Auto Complete question type is only enabled in online mode
Data Grid
The Data Grid display type renders as a table with pagination, sorting, search UI elements.
| 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. |
| Show Export | Displays an Export button allowing the displayed data to export to an Excel or csv file. |
| 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 |
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 with contextual options in the selected display field/column of the data grid in Produce. Contextual option 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.
Data Tab
These options are available under the data tab in the inspector panel.
Display Fields
For data question types that display columns (namely Data Grid, Search or Popup), the display fields option allows finite control of each column such as column headers. Display Field will only show available items from the Data Connection, no fields can be added from display fields.
| 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.
|
| 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. |
| Custom Sorting (Data Grid Only) | Overrides the default sorting and provides an alternative way to sort the column by using:
|
Data Question Filters
These filters are not to be confused with connector filters and feilds, data question 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.
While filters can be set at the question level, its suggested that any filters on large data sets be pre-set in the connector filters and feilds to minimize issues with performance and data retrieval.
Filters can be used when driving a repeater to, automatically or with user input, determine how many pages/sections to display.
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.
| 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:
|
| Type | Options are:
|
| Aggregate | This option is available when the parent question is a multi-row Data question. When selected, each row of the parent question's data becomes its own filter within a group on the current filter set. When creating the grouping for an Aggregate filter, positive filter types (such as Equals, or Contains) are wrapped in an Any Of group to avoid the filters excluding matches from other filters in the aggregate. While negative filter types (such as Not Equals, or Not Contains) are wrapped in an All Of group to avoid the filters including records that are excluded by other filters in the aggregate. A filter referencing repeated data without Aggregate would result in a single filter using the pipe-separated data values. |
Sort
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.
If Schema Fields have been added in Results Transformation, Sort Fields will only show those options to choose from. This includes aggregated data lists.
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
Restricting the schema fields changes the columns available to the data question as a whole, not only the selection of distinct records via the Distinct checkbox.
Schema fields and aggregates limit results to particular columns and (optionally) presents it with statistics for data analysis. Often the resulting data is presented as an on screen report within a dashboard using a Visuals question.
The optional aggregates are: average, count, max, min, and sum
Distinct
The Distinct Results Only option will filter out any duplicate records returned by the data connection. There are times when a data source column contains duplicate values. The distinct operation will be applied to all selected schema fields. Adding more fields does the same thing as distinct ensures that a combination of ALL entries are unique.
If aggregate schema fields are in use, the distinct checkbox will be disabled as the aggregate functions already group records into distinct groups.
Union
Data Union gathers data from different sources into a data question, with the extra information behaving like part of the data question. These sources include non-repeated questions, repeating sections, other data questions, or combinations of the three, and are referenced with respect to the relevant data field.
Important
- Data Union adds rows to the data but will NOT combine the data into a single row.
- A Data Union question on its own will not write or save data to a table. To permanently store data, use either a Custom Data Object, Call SQL Procedure Action or a similar action.
- the only SmartIQ data object supported is the Custom Data Objects
- Chained Data Union is unsupported
A repeat source provides the option of displaying/adding the specified answers, from the repeating questions, within the data. However, a unique identifier needs to be provided to the to the data objects unique field to ensure that each additional answer can be added as well. This should be done using a function variable question, for example simply iterating numerically from the previous unique field value.
Answer
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. The data convert option informs SmartIQ that the data is supposed to be formatted as a known structure like a binary, date, number, html.
A pattern needs to be defined for some data formats like date, and must match the string format exactly for the parser to work. "9/01/2009 8:30 AM" cannot be parsed successfully with "dd/MM/yyyy" but can with "d/MM/yyy hh:mm " because there is not trailing 0 for the day and the time component is required. For details of what characters can be used in a pattern see Custom date/time format strings
| Data Type | Description |
|---|---|
| Date | Use when a string is returned that should be a date. Define the day/month/year/time pattern. |
| Number | Use when a string/text that is actually a number is returned. At runtime, any white space will be removed from the value and the remaining text converted to a number. |
| Image | Use when 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, or 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 (this should include the correct file extension), 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):
|
Validation
To ensure the quality of retrieved data, SmartIQ enables you to apply validation rules to the answer of a data question. If a data field fails validation, SmartIQ will display an error message. This validator works the same way as the validator question type.
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
Updated 8 days ago