Data connections act as data sources for SmartIQ forms and dashboards, allowing projects to read and write data maintained by external systems. Their use is vast, from populating simple drop down menus to sourcing records from large data sets, displaying reports, generating graphs, etc; through a Data Question or a Writable Data Object.
Data can be sourced from a variety of systems and stores and whilst some concepts are generic, specific details differ between technologies. For example a simple CSV file requires a file path, whereas a database requires a server and authentication for a successful connection. Thus when a data connection is added its type will define what connection information is to be provided by the administrator.
SmartIQ is packaged with a collection of connectors designed to connect with industry standard technology, such as SQL Server, XML, JSON, APIs and web services, etc. Each connection type has its own connection requirements and methods to read and write data.

Important Data Connection Concepts

Below are a list of data connection concepts in order of significance that are very important to understanding how to correctly manage data sources. It is recommended that you read through, as best you can, all the conceptual articles as they show you how to correctly operate data sources, even if they don't appear to instruct you how to directly operate data sources.

🚧

Data retrieval limitation

SmartIQ can only retrieve and hold up to 1000 rows of data from an a data source. Therefore filtering larger data sets solely in SmartIQ is not possible. You should first filter the data at the data source or use an enterprise service bus (e.g. MuleSoft) before importing to SmartIQ.

🚧

It is not recommended to filter on answers that are generated using a formula in SmartIQ (eg: concatenating first and last name)

The use of filters on fields generated by formulas forces SmartIQ to load the entire datasource and calculate the formulas before applying the filter within SmartIQ. This is much slower and will potentially run into issues with limits on the maximum number of rows permitted to be loaded by a datasource.

Filters should be applied to unmodified fields as filtering these usually occurs efficiently within the datasource.

🚧

Text based datasources (eg: CSV and text files) should have all relevant fields pregenerated (eg: concatenate into a new column before import)

A text based datasource can be slow and inefficient, and may not filter and sort in SmartIQ as expected. The use of such datasources should be limited to simple selection lists, based on static data that rarely changes, and is limited in length (eg: a few hundred rows).

Data Connection Types

OneSpan is an external connection that only needs to be actioned in settings.

Usage

A single data connection to a data store might provide access to many different records. For example a CRM system might read/write entities such as customers, locations, groups etc. Whereas a database might provide access to Tables, Views, Functions and Procedures. SmartIQ manages this with data objects where one data connection owns many data objects, as depicted below.

Data Objects

As described above data Objects represent specific entities or concepts within a connection. Regardless of the connection type SmartIQ will present the object generically and allow the administrator to Configure:

• The specific entity or technology to target depending on the connection type
• Define caching settings for offline data viewing (mobile apps only where appropriate)
• Define if the object access is read only or read/write where technically possible.
• Fields for which the data can be filtered on, known as key fields (e.g. lookup a customer by customer Id).
• Define fields for which to ignore that are irrelevant or disruptive to business process the data object is required for (known as schema fields).
• Define fields that are appropriate/not appropriate to be shown in a User interface, known as display fields (e.g. show an employees name but not their salary).

📘

Further Reading

Each of the above points together with specific instructions on configuration requirements for specific data connection types can be found in the sub articles below or within the other articles in this category.

Data Object Access of Read Only or Read/Write

SmartIQ supports maintenance of records (add, edit and delete) via a data connection, either during a form via a specific UI known as data modal or in the background without the user knowing upon the completion of an individual workflow step or final competition of a workflow.

Whereas read only data objects are straight forward, writable data object has more to think about for example, whether:

• It is technically possible to write to the data object within the data connection
• It is appropriate to maintain data via the SmartIQ with regards to the business process and security policy.

To define an individual data as writable, select Read/Write as the option for the data object's access property.

Making a Data Connection

New data connections require a name, content folder (mirroring the Project folders), a connection type indicating the data base type, and the ‘Connection String’ in the Connection Attributes field which tells SmartIQ how to connect to the data source.

Filters and Fields

Whilst for most scenarios the default data object configurations is appropriate SmartIQ offers much more granular such as defining what fields the data can be filtered by, how it should be filtered and what to fields to display to end users or to omit completely from responses. For example an employee table could be configured so that it can be filtered only by id or surname and to ignore some legacy fields no longer used by the organisation. Making its use in forms much simpler for form designers.

Filter Fields

Filter fields are passed to data objects at run time to return targeted results. Their use and how they appear will depend on both their both their connection type and object type. For technologies such as web services and database stored procedures they will mimic input parameters whereas for database tables they will list the columns available to build where clauses. See the documentation on the specific data connector for how filter fields apply to specific date objects. Add filter fields as needed.

🚧

Filter fields will only filter for non-blank values when a field is set to required

IQ doesn't send requests when filters set to required are being sent a blank request

When filtering for 'logical blank' i.e = "" then that field must not be set to required else the request to get the database will not be passed.

Custom Fields

The 'add custom' field allows you to add fields that aren't already present in your data. This allows for the addition of placeholder fields for any data that is to be written to the data connector in future, or data relationships to be drawn in the Designer. Best not to duplicate already available filter fields.

Required Filters

Required filters are filter fields where a request to a data source will not occur when the filter value resolves to NULL or to an Empty String at run time.
These can be particularly useful for calling data-connections where accidental calls with empty filters can result in large (too large to process) data sets. Required filters protect form designers from common mistakes.

🚧

Filter value not presence of filter

A required filter does not mean that the filter must be provided each time the data object is used, rather as stated above that when the filter is used ensure its value does not resolve to a null or empty value.

The image below shows the field EmployeeNo configured as a required filter.

Auto Filters

Auto filters are filter fields that is automatically applied each time the data object is used to make a request, filter fields values will be sourced from the users profile at run-time. Auto filters are useful when the results returned always relate somehow to the user at run time, for example retrieve all staff that report to the current user. Ensuring that users can only see their own staff.
Auto filters aid mobile syncing where when configured can return records of interest to the current user for offline use.
The following image depicts the EmployeeNo Filter 'Auto Filtered' to the user's UserId. Thus at run time results will be returned depending on the user making the request.

Schema Fields

Schema fields allow the SmartIQ Administrator to omit fields that are not required by the business. This could be that they are redundant or not of interest to the business processes targeted by SmartIQ Using schema fields ensures responses are kept to a minimum to ensure optimal performance.

🚧

Support For Schema Fields

Schema fields are not supported by all technologies and will appear only where applicable.

By default where schema fields are applicable a select all Schema fields option will display. To specify particular schema fields uncheck the option and use the dialog provided to select the appropriate fields.
The image below shows a redundant title field being excluded from the schema field list. Essentially making it ignored to SmartIQ.

Display Fields

Display fields define which fields can be seen by end users during a Form. In the image below the salary field is excluded as a display field.