Add delete Statements for Custom Tables

DataSync keeps the destination in sync with Sage Intacct. When a record is deleted in Sage Intacct, DataSync deletes it too, using a DELETE statement. The default template includes these statements only for its own predefined tables. If you add a table to the incremental load extraction yourself, no statement exists for it, so records deleted in Sage Intacct stay in the destination table.

To delete them for your table too, add a DELETE statement for your table to the post execution script of the Audit History table. Each statement needs the table name, the Audit History object type, and the key field that matches ZZ_Audithistory.Objectkey.

Add a DELETE statement for a custom table

1. In DataSync, select the Extractions module.
2. Select the Sage Intacct Refresh extraction and click the link in the Tables column.
3. Search for the Audithistory table, then click the pen icon under Post Execution Script.
4. At the end of the script, copy an existing DELETE statement.
5. Replace <TableName>, <objecttype>, and <KeyField> with the values of your table. Keep the rest of the statement identical.
6. Click Save.

IF OBJECT_ID('##DestinationSchema.<TableName>', 'U') IS NOT NULL
    DELETE t
    FROM ##DestinationSchema.<TableName> t
    WHERE EXISTS (
        SELECT 1
        FROM ##DestinationSchema.ZZ_Audithistory h
        WHERE LOWER(h.Objecttype) = '<objecttype>'
          AND h.Objectkey = CAST(t.<KeyField> AS varchar(100))
    );

tip

A row is deleted only if its object type and key appear in ZZ_Audithistory.