Overview

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 e­form, capture information and design business process for your organisation.  Many enterprises may have already implemented BI (Business Intelligence) tool in­house such as Microsoft Power BI, or Tableau, 

Many of this tool provides connection directly to SQL database allowing data to be manipulated in real­time.  This guide is to help understand how InfoCapture data is stored and structured and how to create ​SQL view ​for your BI tools. 

SQL View

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 result­set 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. 

Resource

How to create SQL View depends on the type of database

MSSQL 

MySQL

Understanding InfoCapture Tables

InfoCapture Project

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 ­

• v​alue 
• value_int 
• value_text​

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 f​b_field​ by matching f​b_field.​f​orm_id ​with f​b_form_data.​form_id ​

and fb_field_data.f​ield_sym_name​ with f​b_field.s​ym_name).

hd_issue​ and f​b_field_data​ both have a column f​orm_data_id,​which is not visible in the front­end but identifies a group of fields in that issue (it's a reference to f​b_form_data,​but it's not relevant for the reporting).