¶ 1. Overview.
The objective is to import a data load from Excel into a pre-registered data table using an API (Application Programming Interface) → URL.../api/DataLoad/LoadExcelIntoDataTable, where we will insert parameters with the code of the source data table, the column code, and the row code where the file is located in the source data table, and the code of the destination data table. The processed data will go to the destination dataTable where we pass the codes through these parameters to the API via the QueryString. The process retrieves the necessary information to populate the data table in the Excel file, based on the column name in the data table and the column headers in the Excel file, which must match. It also takes into account the data type of the destination data table, attempting type conversions when necessary.
¶ 1.1. Prerequisites.
The prerequisites for the functionality are simply a user logged into T6, a data form with a file type column for uploading the Excel file, and a destination data table.
¶ 1.1.1 User Permissions.
We need to configure some user permissions as system prerequisites to create the process and workflow trigger to upload data from Excel to T6.
- In Users, select the user who will be responsible for creating the required process and trigger, and click on Permissions.
- A panel will appear on the side; click on Features.
- On the Features tab, select the desired application.
- In User Resources, under Manager, click
to expand the options. - Under Manager, we need to grant the following permissions to the user:
- Manage data table structures;
- Manage trigger structures;
- Manage process structures;
- Manage the workflow;
- Publish processes;
- View processes.
- Still in User Resources, under Analyst, click to expand the options.
- Under Analyst, we need to grant the following permissions to the user:
- Work in the workflow with any type of task;
- Execute formulas in the form.
- Still in User Resources, under Planner, click to expand the options.
- Under Planner, we need to grant the following permissions to the user:
- Work in the workflow with form tasks;
- View triggers.
¶ 2. Features.
The process is based on the columns of the destination data table. All columns from the destination table that have a counterpart with the same name in the Excel file (header) are imported. That is, columns present in the Excel file that do not exist in the data table are ignored. The data table’s type configuration and constraints are also considered. If the data table cannot have a particular field as null and the file does not provide this information, the import will not be possible. In this case, the information must be included in the Excel file or the data table should be modified to allow null values.
The process looks in the table referenced by dataTableSourceCode, in the Excel file located in the column and row specified by the sourceRowCode and sourceColumnCode parameters, respectively, and imports the file into the data table specified in the dataTableDestinationCode parameter.
This API accepts 4 parameters via querystring:
- dataTableSourceCode: Code of the source data table, which can be identified through the item code displayed in the explorer. (This is the data table where the Excel file to be imported is located).
- sourceColumnCode: Code of the column in the source data table (source). To identify it, check the
REP_DATATABLE_COLUMNStable in the database server under thecodDataColumncolumn, and determine which row contains the Excel file added to the source data table. (This information, along with the sourceRowCode parameter, specifies which column in the source table the file is located). - sourceRowCode: Code of the row in the source data table (source). It can be located by opening the source data form. (This information, along with the sourceColumnCode parameter, specifies which row in the source table the file is located).
- dataTableDestinationCode: Code of the destination data table, which can be identified through the item code displayed in the explorer. (This is the data table that will receive the information from the Excel file).
An example of how the QueryString will look for use: URL.../api/DataLoad/LoadExcelIntoDataTable?dataTableSourceCode=***&sourceColumnCode=***&sourceRowCode=***&dataTableDestinationCode=***
The process will only import the file in its entirety; if any errors occur, the entire process will be canceled, reverted, and a notification will be displayed in the notifications menu.
¶ 3. Usage.
To use the import of Excel data into T6 data tables, we first need an Excel file to attach to the source table.
¶ 3.1. Creating Data Tables.
Within T6, we will need to create a source data table (Source), a destination data table, and a data form for each table.
- Create or select the source data table and click on edit.
- In column configuration, add a column by clicking on
; a wizard will open where you will name the column and select the type. In this data table, the created column must be of the file type. - Create or select a data form to receive the Excel file and click on edit.
- In the Properties tab, under Data Table, select the source data table with the file type column.
- In the Layout tab, under Data Table Columns, select the previously created file type column and click on
. - Click on
and open the data form. - With the data form open, select the file type column and add the Excel file that will have its data loaded, then click on
. - Create or select the destination data table and click on edit.
- In column configuration, add a column by clicking on ; a wizard will open where you will name the columns and select the type.
When creating columns in the destination data table, it is mandatory that the column names exactly match the headers in the Excel file, and the column type must be compatible with the data type in the Excel file. If the type is not compatible, T6 will attempt to perform the conversion.
If the process fails, it will be necessary to adjust the data table or the Excel file, possibly requiring renaming columns in the table or the Excel file headers. Failures can also occur due to data table constraints, such as disallowed null fields or incompatible data types.
- Create or select a data form that will receive the data and click on edit.
- In the Properties tab, under Data Table, select the destination data table.
- In the Layout tab, under Data Table Columns, select the columns from the Excel file that will be displayed in the form and click on .
- Click on and open the data form (note that the form will be empty, only showing the columns).
¶ 3.2. Using the Process through Workflow.
¶ 3.2.1. Creating the Process:
- Create a workflow process and click on edit.
- On the process creation screen, create a new task.
- Name the task and select the type, which should be Automatic Hyperlink.
- Click on Next. In the Automatic Hyperlink field, enter the querystring with the parameters filled in →
URL.../api/DataLoad/LoadExcelIntoDataTable?dataTableSourceCode=***&sourceColumnCode=***&sourceRowCode=***&dataTableDestinationCode=***(we can manipulate the API parameter values using the Instance(workflowParameter) function). - Click on Finish and connect the task to the end of the process.
- Upon completion, you will have a process similar to this:
- Click on ; a wizard will open, allowing you to assign a person responsible for the task via parameters. Click on Finish.
¶ 3.2.2. Creating the Trigger for the Workflow Process:
- Create a trigger for the workflow process and click on edit.
- A panel will open on the side. Under Process, select the process created earlier with the API execution task.
- Upon selecting the process, the Process Parameters option will be displayed, where you will assign a responsible user.
- Click on Save to complete the creation of the trigger.
- After saving the trigger, a double click on it will display a panel on the side, where you can view the process to which the trigger is associated, the trigger name and description, and the process parameters with the assigned responsible user.
- After confirming the information, click on Next to confirm.
- Click on Finish; a notification will be generated informing you about the process completion.
If an error occurs during the API insertion, a system notification will be generated informing you of the issue.
- For more information on creating and configuring workflow processes and triggers, visit our help center: Workflow BPM.
¶ 3.3. Using Swagger.
For information on configuring and implementing Swagger, visit our help center: Swagger.
To open Swagger, use the endpoint: URL.../swagger/index.html
- Example of endpoint in a cloud environment:
qablue.tech6cloud.com/swagger/index.html; (In this case, qablue is your domain name.) - Example of endpoint in local environment:
(IIS server)/(application name in IIS)/swagger/index.html
The URL provided before the API will depend on your domain in the cloud, or, in the case of an on-premises environment, on the settings provided in your Internet Information Services (IIS).
When accessing the endpoint, a page will open containing all the system's APIs. Locate the API (/api/DataLoad/LoadExcelIntoDataTable) and click on 
to expand it:
- The parameters required for executing the API will be displayed. To enable parameter insertion and execute the API, click "Try it out".
When expanding the API, the possible responses that the system can return will also be displayed, as well as a detailed view of the request, such as:
