How to update external database for the Data Set?

After some changes made by verification operators some records in Vendors/Business Units of the Data Set were added and some were changed. How can I transfer these modifications to my external database?

The Data Set includes two tables:

  1. Main – is used during the work of FlexiCapture, for example, data from the table is used for such functionality as searching of the company from the list.
  2. Diff – is used to store the results of the user’s data editing during the work in FlexiCapture.

After connection of the Data Set to the database Main table is created, and its contents fill from the external database. When the Data Set is edited by Verification operators during their work in FlexiCapture, the changes are placed into Diff table and then are added to Main table.

For transferring added and modified records it is necessary to move the records from the Diff table to the corresponding external database table. Here are the detailed instructions of how to update the external database for the Vendors Data Set. For the Business Units Data Set you need to perform similar actions.

  1. At first you need to copy SQLite database file from the place where the project is stored:
    1. If the project is local, then the path to the database file is: <Project>\Templates\InvoiceData\Vendors.Cache\db.
    2. If the project is uploaded to the server, then it is recommended to use file storage and then the path to the SQLite database will look like: <Project>\DatabaseCaches\Vendors\db, for example:
  2. Open the copy of the database (for example, using SQLite Administrator) and save Diff table in .csv in Unicode.

    To achieve it you need:

    1. Open the copy of the database, for example, in SQLite Administrator.
    2. Export Diff table in .csv file, choosing the Diff table by cursor and clicking Data → Export.
    3. In the drop-down list Save as type choose CSV format.
    4. After clicking on the Save button the program will suggest to choose the separator char. You can use, for example, “|” (without quotes) as separator char:
    5. It is necessary to take into account that if you use SQLite Administrator then the table will be saved not in Unicode. That is why you need your system localization to support displaying of all characters used in the table (diacritic characters, etc.). To save the .csv table in Unicode you can open it in Notepad and save in Unicode using Save as...  button.
  3. Transfer data from the .csv file to the certain table of the external database using SQL Server Import and Export wizard
    1. Open SQL Server Import and Export Wizard and choose Data Source: Flat File Source, choose the saved .csv file and set the options as showed in the screenshot:
    2. In Columns settings make sure that the Row and Column delimiters were chosen correct and that the table is displayed correct:
    3. Choose a Changes table of the external database as a destination.
    4. Set mapping between source and destination columns clicking on Edit Mappings… button.
  4. After that you need just to move the records from the table where you saved them to the main Vendors table of the external database.

    The records which were added to Diff table during operators work can be divided into two types: added records (which were produced by creating new Vendors) and modified records (which were produced by modifying of existing Vendors).

    Rows of Diff table that correspond to added records, contain ‘1’ value in the __FCInternalDiffState column. Rows that correspond to modified records, contain ‘0’ value in that column.

    1. Here is SQL query for transferring of the modified records ([__FCInternalDiffState] = '0') from the Changes table to Vendors table of the external database:
      	-- Update Vendors table
      UPDATE Vendors
      SET
      	-- For the Column1 of Vendors table assign value from the
      	--Column1 of Changes table, if it is not empty, i.e. was
      	--specified or changed by user
      Vendors.Column1 = ( CASE changesDb.Column1 WHEN '' THEN
      	Vendors.Column1 ELSE changesDb. Column1 END),
      Vendors.Column2 = ( CASE changesDb.Column2 WHEN '' THEN
      	Vendors.Column2 ELSE changesDb. Column2 END),
      ...
      Vendors.ColumnN = ( CASE changesDb.ColumnN WHEN '' THEN
      	Vendors.ColumnN ELSE changesDb. ColumnN END),
      FROM 
      	--Perform for that records from Vendors table which have the same
      	--VendorId key with the records from Changes table with the type
      	--“modified record”   s(__FCInternalDiffState = 0)
      Vendors JOIN (SELECT * FROM changesDb WHERE
      	changesDb.[__FCInternalDiffState] = '0') AS changesDb
      ON Vendors.[VendorId] = changesDb.[_VendorId]
    2. During the transferring of the added records ([__FCInternalDiffState] = '1') you need to define the rule of the filling VendorId column of Vendors table. In general the value of this column is defined by business-rules and may have any value.
      1. If the column VendorId is unique identifier of int data type and its value have no limitations, then you can use the following SQL query to transfer the records from Changes table to Vendors table:
        INSERT INTO Vendors (Column1, Column2, ... ColumnN)
        SELECT 	
        	Column1,
        	Column2,
        	...
        	ColumnN
        FROM сhangesDb
        WHERE changesDb.[__FCInternalDiffState] = '1'

        It is important to mention that you shouldn’t specify VendorId column among columns Column1, Column2, … ColumnN .

        After transferring of new records to Vendors table you can renumber all the VendorId values of the records using the following query:

        ALTER TABLE Vendors ADD Id_new INT IDENTITY(1, 1)
        GO
        ALTER TABLE Vendors DROP COLUMN VendorId
        GO
        EXEC sp_rename 'Vendors.Id_new', 'VendorId', 'Column'
      2. If renumbering of VendorId column for the already existing table records does not suit you, you can use the following query – for each added to Vendor table record the value of VendorId column will be autoincremented beginning from the maximum value of VendorId in already existing Vendors records.
        SET IDENTITY_INSERT Vendors ON;
        GO
        DECLARE @start_number INT
        SET @start_number = (SELECT MAX(VendorId) FROM Vendors)
         
        INSERT INTO Vendors (VendorId, Column1, Column2, ... ColumnN)
        SELECT 	
        	(@start_number + ROW_NUMBER() OVER(ORDER BY [_BUId])) AS VendorId,
              	Column1,
        	Column2,
        	...
        	ColumnN
        FROM changesDb
        WHERE changesDb.[__FCInternalDiffState] = '1'
  5. After finishing of data transferring it is recommended to update the Data Set in such a way to clear Diff table. After this next time after operators work the table will have only new modifications. To achieve it you need to:
    1. Make backup of Normalization.xml file from the place where the project is stored:
      1. If the project is local, then the path is: <Project>\Templates\InvoiceData\Vendors.Cache\Normalization.xml.
      2. If the project is uploaded to the server, then it is recommended to use file storage and then the path will look like: <Project>\DatabaseCaches\Vendors\Normalization.xml
    2. Open ABBYY FlexiCapture 11 Document Definition EditorDocument Definition Properties Data Sets.
    3. Choose necessary Data Set and press Set Up... button. In the pop up window press Ok button without changing any settings. It will lead to recreating of the Data Set with updated records and cleared Diff table, Normalization.xml file will clear too.
    4. Replace Normalization.xml file with its backup.

Was this article helpful?

0 out of 0 found this helpful

Have more questions? Submit a request

Recently viewed