- If you want Rapidi to perform an update, add or delete on specific records, you can make use of the feature 'Actions'.
- You can view 'Actions' as a kind of dictation machine, where you dictate exactly what was done in the source database, so that Rapidi will know what to do in the destination database.
- For example, we have updated a customer in our source database. We create an Actions entry specifying that we updated this customer. We have also created a Transfer, informing Rapidi where to go and what to do. By running this Transfer, Rapidi will refer to the Actions table and hereby find the corresponding record in the destination database and update it there as well.
- NOTE: Actions are mostly used for deleting records in the destination. It is the most efficient way to delete records in the destination database/system as there is basically no overhead! If you check the "Delete" item on a normal transfer, then this will result in reading through all records in the destination (within the destination filters set) and make a lookup in the source database/system to see if the record still exists. If it does not exists in the source database/system, then it is deleted in the destination. Using the normal "Delete" feature can make the transfer take a long time to complete!
- TIP: You can now also Filter on the Action table (when reading from the Action Table). See Action delimitations
Transfers that use "Actions"
To use Actions, you need to create one Transfer for each Table that you want to handle Actions for. I.e. one Transfer for the Customers Table and another Transfer for Items table. This is because in this way you can specify more details about the Table, like the Table Link (key fields) - which is needed as Rapidi cannot always determine the primary key of a table - it is only possible for some connection types like NAVSQL, but not for e.g. OData! This also performs a lot better than the old way used with Replicator 4.30 - where all actions were processed by one transfer and the Replicator had to open/close tables for each action. The new way demands more setup, but gives much better performance.
See also Upgrading_from_Replicator_4_30
Actions Table Name:
In the field "Actions Table Name", you specify the table containing the Actions entries.
Actions Control Field:
In the field "Actions Control Field" you specify the field "Source Counter" - the field that you want to use as Source Control in your Action Table.
TIP: If using the NAVSQL Connector or in general an MS-SQL-based system, you can use the "timestamp" column as Source Control - in this way you don't need to create and maintain a specific column for this purpose.
Actions Table Layout:
Below is an example of an Actions Table (created in MS Dynamics NAV) and what fields should be in the table. If you want to use Actions with others systems than NAV, you can do so - just create fields with the same names and types.
Actions field Option string: Update, Add, Delete, UpdateAdd (Note it starts with a comma) - when you define it as an Option string in NAV.
Please Note: When creating the Actions from NAV and filling in the Key field, you should use the ordinal number for Option fields (like 2 for an Order - option field Type on the SalesHeader table).
Below are the fields listed for general use:
|Source Counter||BigInteger or timestamp||must be incremented for each row inserted in the table, there should be a key/index defined on this field|
|Action||Option or Integer||1=Update, 2=Add, 3=Delete, 4=Update/Add|
|Key||Text||insert the values for key fields, separated by +|
|Table Name||Text||insert the name of the Table|
The fields "Action", "Key" and "Table Name" must have these names and types as above!
The "Source Counter" field (and any other fields that you chose to add to the table) can be named as you wish.
TIP (Key field format):
When filling out the Key field in the action, you need to specify the values in a format that Rapidi can understand, as Rapidi tries to convert these values to the actual type of the corresponding column in the database/system.
For Text fields, this is straightforward - simply put the value (no ' or " around). These key values must not contain the separator value. The default separator value is "+" sign. The separator value can be changed - please contact Rapidi support if you need it changed.
For a Datetime field (also Date fields in NAV), the default format is DD-MM-YYYY HH:MM:SS - e.g. 23-02-2020 00:00:00 (for a date field 23rd February). If you have an empty Date field in NAV, simply put an empty value (nothing) in the Key field for the date.
TIP2 (delete multiple lines):
If you want to use one Action Table entry to delete many records in a table - e.g. to delete ALL lines for a Sales Order, you can use the following trick:
You can create the actions with only the document type and the order number (i.e. the key of the main table - the Sales Header - for example the Key field with: 1+SO00056 ). Then make a Transfer with "Actions" and "Delete" and in the Table Link, you specify only the part of the key - e.g. "Document Type" and "Document No" fields. This works well with NAVSQL Connections and should also work with other MS-SQL or ODBC based Connections.