Please Note: All of the files referenced in this article are available in the .ZIP file at the bottom of the article. This article is intended for advanced Integrify users with some development experience
This article is a companion to our webinar, "Managing Tables with a Grid or Process," where we discussed two different solutions for building your own Integrify process to make updates to a data table.
You can download the files referenced below (at the bottom of this article in a .ZIP file) and import them into your Integrify instance to see the sample processes in action.
Installing and Using Sample Table Maintenance Processes
Note: By convention, we place the reports that support forms into a separate category named “Form Data Reports.” This is not a requirement but makes locating these reports easier.
Table Maintenance with Grid Process
Form Task ‘Accept Changed to Parts Table’
The button labeled “EXAMINE GRID OBJECT IN CONSOLE” allows you to use your browser’s debugging tool to examine the structure of the JSON object that the grid creates when it stores its data in the Integrify database. It’s not necessary for the full functionality of this example but is useful if you want to know how the grid’s data is packaged for storage.
DB Push Task ‘Replace Cust_Part_Tech table with Updated Grid’
The SQL statement below takes in a JSON object stored in the Integrify database and converts it into a dataset that can be used to
SQL statement in the DB Push task:
--delete previous interim tables
DROP TABLE IF EXISTS [CUST_PART_TECH_NEW];
--copy the data from the just-completed grid
--into a new table named cust_part_tech_new
--this statement also parses the JSON object
--that the grid data is stored in for insertion into the new table
select derived.* into [CUST_PART_TECH_NEW]
FROM OPENJSON (@GRID, N'$.gridCellValues')
Appliance varchar(100) N'$.Appliance.Answer', -- Select List
Mechanism varchar(100) N'$.Mechanism', -- Short Text
Technician varchar(100) N'$.Technician'
) ) derived ;
--delete any existing backup tables
DROP TABLE IF EXISTS [CUST_PART_TECH_OLD];
--make the previous data table into the backup copy
EXEC sp_rename [CUST_PART_TECH], [CUST_PART_TECH_OLD];
--make the newly created table the current data table
EXEC sp_rename [CUST_PART_TECH_NEW], [CUST_PART_TECH];
Vendor Maintenance Process
This process uses an Integrify search box question and a series of matching fields to create new records and edit or delete existing records in the specified table. Each time the save or delete button is pressed, the process performs a database update immediately in the background using a parallel process path. The form task remains active for further updates.
Import the process file named Vendor_Maintenance.json to create the needed workflow. In the Integrify database or (if you have the private database option) in your private database, create the Cust_Ship_To table using the Cust_Ship_To.sql file included in the installation package. You may need the Integrify support team to help install this file if you do not have access to a private database.