A simple guide to help on-prem clients to integrate InfoCapture with their existing BI (Business Intelligence) software.
InfoCapture provides an easy and quick way to create eform, capture information and design business process for your organisation. Many enterprises may have already implemented BI (Business Intelligence) tool inhouse such as Microsoft Power BI, or Tableau,
Many of this tool provides connection directly to SQL database allowing data to be manipulated in realtime. This guide is to help understand how InfoCapture data is stored and structured and how to create SQL view for your BI tools.
InfoCapture tables are designed to allow your users to create form and design business process without coding knowledge. This may not provide direct view required for BI tools, in this case, the SQL view needs to be created.
SQL View: a view is a virtual table based on the resultset of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
How to create SQL View depends on the type of database
Understanding InfoCapture Tables
We are using an example InfoCapture Project called "Claromentis Asset Records" below
InfoCapture Database Tables
Data relational diagram between InfoCapture Project (hd_project) InfoCapture Tickets (hd_issue) and InfoCapture Field Data (fb_field_data)
This is the main InfoCapture project table contain information about project name, properties and other settings.
id= project ID
name = project name
All tickets/issues are stored in this table
id= global unique id of each ticket
project_id = id of the project
id_in_project =unique issue id within the project
form_data_id= reference to the form data
individual form data is stored in this table
fb_field_data has three columns
Only one of them actually contains data, depending on the field type (which is known for each form, but also can be found in table fb_field by matching fb_field.form_id with fb_form_data.form_id
and fb_field_data.field_sym_name with fb_field.sym_name).
hd_issue and fb_field_data both have a column form_data_id,which is not visible in the frontend but identifies a group of fields in that issue (it's a reference to fb_form_data,but it's not relevant for the reporting).