Cross-reference (Xref)

Cross-reference (Xref)

Starfish ETL has built-in methods for maintaining relationships between different systems. Put simply, you will be storing an ID from your Origin, and the new corresponding ID from your destination for the record.

For example, if you are migrating Company records from one system to another, and later in the process you’ll also be importing Contacts that will be related to these companies you’ll need a method to select which company the contact belongs to. One method would simply be to store the Old (Origin) primary company ID somewhere inside of the new company table on your destination. Then you could use a SmartLookup and pull down the ID by a filter clause. This is perfectly acceptable; however you'll take a performance hit because for each row you’ll have to perform another lookup operation. Using Xref functions, these ID relationships are stored in memory, thus making the lookup operation instantaneous – speeding up your process. The idea will be to write out the ID relationships for accounts while that job is running. Then, when the Contacts job runs it can initialize and read from this list to quickly & easily relate to the correct Company.

Xref Writes

To write to an initialized Xref list, you may use the Post-Process Operation “Xref Write”. Select the list from the Xref List drop-down. Then supply a value or variable to use as the Origin/Old ID, and one for the Destination/New ID. If you are writing to a list for the first time it may not be in the drop down, simply type the name of the list into the Xref List box.

Alternatively, you can use the XrefWrite() Scripted function to write to an Xref list.

Xref Reads

To read from an Xref list, you may use the “Xref Read” function field in your mapping.

Alternatively, you can use the XrefRead() Scripted function to read from an Xref List.

    • Related Articles

    • Working with Cross References (Xref files)

      Working with Cross References (Xref files) Xref files servermany purposes. The most common case is as foreign key lookups. Another common case is to use Xrefs as pick list lookups. Tables can be created manually. They can be created by importing the ...
    • Check for duplicates using xref

      I needed to check for duplicate attachments in a system and remove the duplicates. I created a Job that used the same origin and destination and pulled all records from the Attachments table. I had a before operation that would read and write to the ...
    • Look in many xref lists for an origin ID

      Function ScriptedField Dim newid Dim oldid oldid = "@@ORG:OLDID@@" newid = XrefRead("Accounts",oldid) If Len(newid) = 0 Then newid = XrefRead("Contacts",oldid) End If If Len(newid) = 0 Then newid = XrefRead("Opportunities",oldid) End If If Len(newid) ...
    • Starfish Scripting Class Object

      From within VBScript operations (ScriptedVariable, VBScriptProcedure, ScriptedField) in additional to normal VBScript functions, the developer has access to a custom “Starfish” class. The tables below define its usage. This class may also be used ...
    • Creatio / Hubspot Essentials

      Note: Before beginning it is important to understand that while this is a prebuilt integration it is highly recommended that someone who is familiar with both systems, their databases and your processes perform the implementation.  It is also ...