The Link Storage feature allows you store and retrieve link values in a very easy and straightforward way. It enables you to store the link or key values in the RapidiOnline central service configuration database. These link-values could, for example, be a list of customer numbers in your ERP (e.g. in Microsoft Dynamics NAV/AX/GP - the primary key in Customer table) and the corresponding Salesforce.com Account Id. By storing these values in the RapidiOnline central service configuration database, you do not need to create additional fields (e.g. ExternalId field in Microsoft Dynamics NAV, AX or GP).
On other data integration Platforms, you can find similar features named as "Cross Reference Keys" or "Cross Reference Table". We believe that our Link Storage feature is more powerful as you need to specify only a few things and then rest is handled by the system.
If you are using Microsoft Dynamics GP as your ERP system this feature will be very useful as you have limited possibilities to add new fields in Microsoft Dynamics GP. But even for other systems, it is a very useful feature as you
- avoid adding any extra fields to your systems
- save time during the implementation of the integration
- make it easier to upgrade your ERP to newer versions (fewer dependencies)
For each Link Storage, you specify which Connection, Table Name and Link Fields it should contain and then you can specify the Link Storage on the Transfer(s) that use and update the values in the Link Storage.
Creating a Link Storage
The Link Storage feature is conceived in a very generic way. You can create as many Link Storages as needed - each Link Storage should be designated to hold one specific set of links - for example, ERP Customer Numbers and corresponding CRM Account Ids or ERP Product Numbers and corresponding CRM Product Ids. Each side of the Link should consist of the fields that make up a unique key in that table (usually the primary key) - you can specify more than one field (there are no limits on the number of fields in the link key) and the link key can have a different number of fields on each side.
Under "Link Storages" in the menu, when you create a New Link Storage or when you edit an existing Link Storage, you specify the following fields:
- Code: Give the Link Storage a descriptive Code - e.g. CUST or CUST_UK or ITEM. Code must be unique and uppercase letters and numbers.
- Description: Here you describe what kind of values are on the left side (1st column) of this Link Storage - e.g. "NAV Customer No's" or "GP Item Numbers". This field is used directly as a header for the 1st (left) column under Link Storage Values. Therefore it is important to choose a good description.
- Description 2: Here you describe what kind of values are on the right side (2nd column) of this Link Storage - e.g. "Account Id's" or "Product Id's". This field is used directly as a header for the 2nd (right) column under Link Storage Values. Therefore it is important to choose a good description.
- LinkField Separator: For Link Storages that have more than one field in either the 1st or 2nd column, the values will be separated by some character. The default character is '#', but you can specify a different character in this field if needed. If '#' is fine for you leave the field empty.
When you save or create the Link Storage with the above fields, you are presented with two columns, where you need to further specify what is inside each column of this Link Storage. This information is used when running a Transfer (on which you have specified this Link Storage) to identify the fields values that are needed to fill each column. When the Transfer is running, it will try to match the current Source Connection and Source Table Name of the Transfer with what you specify on the Link Storage. It has to match either the 1st column or the 2nd column. When a match is found, it retains the Link Fields specified (on the Link Storage) and uses these to build a unique key to store in the Link Storage Values. Likewise, it will try to match the Transfers current Destination Connection and Destination Table Name with the opposite column in the Link Storage - and use the Link Fields specified in the same way.
As you might guess from the above, the same Link Storage can be specified on Transfer going in both directions - i.e. from ERP to CRM and from CRM to ERP system - for the same tables or objects. This means that the Link Storage can be kept up-to-date with Link Key values when a new record is created in any of the systems integrated - for example, both when creating a new Customer in the ERP system and when creating a new Account in the CRM system.
So for each column, you can create one or more combinations of a Connection, a Table Name and one or more Link Fields. When you create or edit one of these Link Storage Fields, you are presented with a form with the following Fields:
- Connection: Here you select one of the Connections that you have setup. The Connection needs to match the Connection used on the Transfer where this Link Storage is specified (either Source Connection or Destination Connection).
- Table Name: Here you specify the corresponding Table Name. It needs to match the Table Name used in either Source Table or Destination Table on the Transfer that uses this Link Storage.
- Link Fields: Here you specify the fields that together make up a unique key for the above table. If you specify more than one field, the values will be separated by the above specified "LinkField Separator" (or default by '#' sign if no LinkField Separator is specified).
Specifying the Link Storage on a Transfer
Now that you have created a Link Storage, its time to get some values into the Link Storage. This is very simple as you just have to select the Link Storage on one or more Transfer. On the Transfer, in the General section, there is a field called "Link Storage" where you can select one of the Link Storages that you have created.
When a Link Storage is selected on a Transfer, is has the following effect: Each time the Transfer is running, each record is read by the transfer, and where a corresponding destination record is found (or created), the values for the Link Fields specified in the Link Storage will be inserted in the Link Storage. The Link Storage values are updated if they are different from the values read by the Transfer.
Tip: If you are using Source Control or Mirror to read only the changed records, you can reset the RTI value to 0 or -1 to read all records and in this way be sure that all Link Values are added to the Link Storage when you initially start to use it and have data in both systems.
Using the Link Storage (on a Transfer) as the Table Link
If you just specify the Link Storage on a Transfer as described above, the Link Storage Values will be updated, but the Transfer will still use the Table Link specified to know what records to match between the systems. This is still fine in many situations - for example, if the Customer No. from the ERP system is also transferred to a corresponding Customer Number field on the CRM Account.
If you want the Transfer to use the Link Storage to know what record is actually Linked, then you can simply leave the "Table Link" empty on the Transfer. Then the Transfer will do a lookup in the Link Storage Values to find the Key to use to check the record in the destination, or if it does not find a match, it will know that it should create a new record in the destination (and subsequently update the Link Storage Values).
Link Storage Values
If you click the second [+] in the header for the Link Storage, you will see a new section with all the Link Storage Values that are currently in this Link Storage. The titles for the 1st and 2nd column corresponds to the "Description" and "Description 2" values entered for the Link Storage and should be descriptive of the content of each column.
You will be able to use pagination (situated to the right) to navigate forward and back in all the Link Storage values.
You can also use the two search fields to quickly check for specific values in each column.
Entering something in each search field will update the pagination also. If you want to revert to show all values, simply blank out both search fields.
When you see one or more Link Storage Values in the list, you can edit or delete each one using the icons in the action column (third column).
Below the list with Link Storage Values, there are buttons to create a new Link Storage Value (in case you want to do that manually) and to delete all Link Storage Values for this Link Storage (in case you want to delete all values and start again).
Using the LSLookup function to retrieve values
Now that you know how to setup Link Storages and to get values into the Link Storage Values, its time to put these values into use in other Transfers. Using the LSLookup(<link storage code>,<link fields values string>) function, you can retrieve values from the Link Storage from other Transfers.
This is very useful for example if you have a Link Storage with the combination of ERP Customer No's and corresponding CRM Account Ids, you can use this function on an Invoice or Sales Order Transfer to get the Account Id to attach the Invoice or Sales Order on the CRM side. It is usually much faster to get this information from the Link Storage than to use for example a DBLookup formula to get the same from the CRM system.
The LSLookup function can also lookup values in the reverse order - e.g. having the Account Id and retrieving the Customer No.
You can learn more about how to use the LSLookup function here: LSLOOKUP
Statistics issued from the Link Storage feature
When a Transfer is running, it will collect some statistics for the use of a Link Storage and also for the use of the 'LSLookup' function. These statistics are shown both in the console (the section above 'General') and Log after the Transfer finishes.
The statistics for using a Link Storage named 'CUST' on a Transfer could look like this:
LinkStorage Statistics for CUST: Get 5, Checked 2, Updated 2, Added 1 (00:00:03-422)
which means that 5 lookups were made to the Link Storage Values, 2 records (links) where the same, 2 records (links) were updated and 1 new entry was made in the Link Storage Values. All the database operations took 3 seconds and 422 thousands of a second to complete (total time).
The statistics for using an 'LSLookup' function on a Transfer (in the Field List) could look like this:
LSLookup Statistics for CUST: Get 18 (00:00:02-345)
which means that 18 lookups were made to the Link Storage Values and it took 2 seconds and 345 thousands of a second to complete (total time for all 18 lookups).
The statistics allow you to get an overview of the usage of the Link Storage feature and how it performs.
Using the Link Storages feature requires that you use version 4.0.01g or later (central and RapidiConnectors).