Open topic with navigation

Dedup Table

Many clients have custom functionality that includes custom tables with links to individual or organization records. netFORUM baseline includes any of these tables in build releases, but custom tables must be added one by one and it is not done automatically.

In order to include the data from these tables in the Combine and Merge Duplicate Records process follow the configuration steps below.

Managing Records

Any table can be added to the netFORUM combine/merge dups process by updating the dedup table in the Admin module.

The dedup table has three required values:

• File Group is the group or section of the combine/merge process in which the new table will reside. When the combine/merge process gets to this section, all of the tables in this section will be processed.
• Table Name is the name of the table being combined/merged.

• Customer Key Name is the Customer Key co_customer.cst_key foreign key column within the table being combined/merged. There should be one record in the dedup table for every Customer Key reference in your table. For example, if your table has two columns that are FK's to co_customer then you will need two records in Dedup Table, one for each customer key column. For more information, search Co customer on the Wiki.

• Order - the order in which this table should be processed. This could be important if this table is dependent on other tables being combine/merged earlier or later in the process.

• Before SQL Merge and After SQL Merge - enter any SQL commands or stored procedures to run right before or after the update. You can use this to update any related data. You can apply parsed values (For more information, search Parsed Values on the Wiki) on the "from" and "to" cst_key as shown here:

dbo.ad_advertiser_dedup @cst_key_from={cst_key_from}, @cst_key_to={cst_key_to}

A "Before SQL" command could also be used to work with data that has unique constraints. For example, suppose you have a table client_abc_co_customer_x_state that links customers to states in which they do business. This table has a composite unique constraint on a01_cst_key and a01_sta_code to prevent a customer from having the same state twice. If you were to try to merge two customers that both serve the same state, you will violate the unique constraint and the combine/merge process will fail. Therefore you should have a "before sql" script like this that will delete the "duplicate" rows from the customer that will be removed by the combine feature.

DELETE FROM client_abc_co_customer_x_stateWHERE a01_cst_key = {cst_key_from}AND a01_sta_code IN -- this will be a list of states the the "to" customer has: (SELECT a01_sta_code FROM client_abc_co_customer_x_state WHERE a01_cst_key = {cst_key_to} )

Custom Tables

Note: When new Customer Key columns are added to or removed from netFORUM, the dedup table needs to be modified accordingly.

As an example, during financial conversions, it is typical for Abila to create a historical financial table that contains financial history records. The typical naming convention for this file is client_xxxx_accounting_history with table prefix a01 or some other 3 characters. (The xxxx is substituted with the client's initials or acronym. The a01_key is typically the primary key, and there will be a a01_cst_key column that is the foreign key to co_customer.cst_key. It is this field -- a01_cst_key -- that will go in the Customer Key Name field.) For more information, search Co customer on the Wiki.

To add the client_xxxx_accounting_history table to the combine/merge process:

1. Click the Admin tab.
2. Navigate to dedup table.
3. In the item links panel, click add. The Add Dedup Table page displays.
4. From the file group drop-down list, select the appropriate module.
5. From the table name drop-down list, select the appropriate table.
6. From the customer key name drop-down list, select the column that is a foreign key that holds the value of a co_customer.cst_key. For more information, search Co customer on the Wiki.
7. Click Save to save the record.

The next time the combine/merge process is run, the client_xxxx_accounting_history table will be included in the process and the data from the client_xxxx_accounting_history table will be combined/merged within the accounting section.