Scribe Insight – Cross Reference Keys and the Cross Reference Lookup

If you’re familiar with Scribe Insight, you know that on install a SQL database is created to help with a myriad of functions, processes, tools, etc. This database is called SCRIBEINTERNAL. One specific table comes in very handy when migrating or processing a multitude of data. This table, called KEYCROSSREFERENCE, it works in conjunction with the Cross Reference Keys feature inside of the Scribe Insight Workbench.

So, how do we take advantage of the Cross Reference Keys feature? It’s easy.

After you create your Scribe Workbench job, setting the Source & Target and mapping fields, you click on the Cross Reference Keys button at the top to open the dialog.

[img src=”/wp-content/uploads/2015/10/CrossRefButton-300×92.jpg” alt=”Cross Reference Button” class=”aligncenter”]

[img src=”/wp-content/uploads/2015/10/CrossRefDialog-295×300.jpg” alt=”CrossRefDialog” class=”aligncenter”]

Within the dialog box, you need to select your Target Connection. If you have a simple job, there should be only one item to select.

[img src=”/wp-content/uploads/2015/10/TargetConnection-300×63.jpg” alt=”TargetConnection” class=”aligncenter”]

Under Operation, select “Insert into Internal database table.” By default, on the right, KEYCROSSREFERENCE is selected. Leave that as is and add a Label.

[img src=”/wp-content/uploads/2015/10/Operation-300×79.jpg” alt=”Operation” class=”aligncenter”]

Now you’re ready to add specific fields from both the Source and Target into a record in the KEYCROSSREFERENCE table.

If we take a look at the KEYCROSSREFERENCE table, we’ll see there are 6 fields. One holds the Label, one holds the Create Date, two hold information about the Source record processed, and two hold information about the Target record processed. Please note that an entry is only entered if the row being processed in the Scribe job is successful.

[img src=”/wp-content/uploads/2015/10/TableColumns-300×22.jpg” alt=”TableColumns” class=”aligncenter”]

So back to our dialog box. Under Source Mapping we need to select a Key field and a Name field. The Key field needs to be unique. Hopefully, each row of your source data will contain a unique ID or GUID. If not, you can combine fields (i.e., first name, last name, date of birth) to create a unique ID. We then add a Name field – pick something descriptive. Note, there are times I use the same field in both the Key and Name fields. The Name field in my opinion is less important but can be used to verify source data.

Under Target Mapping, we first need to select the Data Object. If you have only one Target connection, there will be only one Data Object to select from. Multiple targets = multiple data objects to select from.

Once we select the Data Object, we add a Key and Name field just like above. If you are migrating or integrating data to an ERP or CRM system, chances are that on insert of a record, a unique ID will be created for that record. If so, that would be the best field to choose for the Target Mapping Key field.

[img src=”/wp-content/uploads/2015/10/Mappings-300×148.jpg” alt=”Mappings” class=”aligncenter”]

Since we are entering data into the KEYCROSSREFERENCE table, the field choices on the right are grayed out. Each Key and Name field needs to go into a specific field in the KEYCROSSREFERENCE table.

At the very bottom of the dialog box, we see the Cross Reference Lookup section. Check the “Cross reference lookup used in this DTS” check box and leave the “Select or enter variable name” set to the default of XRefValue.

[img src=”/wp-content/uploads/2015/10/CrossRefLookup-300×37.jpg” alt=”CrossRefLookup” class=”aligncenter”]

Click OK to save and close the dialog box and it’s information.

So what have we done? We’ve created a way to capture information about our processed data. As mentioned above, when a record is processed successfully, a record will be added to the KEYCROSSREFERNCE table. By using a unique ID from the Source data, we know exactly what’s been processed and we have a way to use this information going forward.

[img src=”/wp-content/uploads/2015/10/CrossRefData-300×57.jpg” alt=”CrossRefData” class=”aligncenter”]

So how do we use this KEYCROSSREFERNCE table and the XRefValue Cross Refernce Lookup? First let’s take a look at the XRefValue variable that was created by opening our variable list. To do so, open the Variables list by clicking on the Variables button at the top of the Workbench.

[img src=”/wp-content/uploads/2015/10/VariableList-300×85.jpg” alt=”VariableList” class=”aligncenter”]

[img src=”/wp-content/uploads/2015/10/XRefValueVariable1-300×89.jpg” alt=”XRefValueVariable1″ class=”aligncenter”]

Double-click the XRefValue variable to open it. What we see here is that the variable has a formula associated to it. The formula uses the XRefLookupInternal() function to do a lookup in the KEYCROSSREFERNCE based on your Cross Refernce Key label (“InsertLead”) and the unique ID associated to the Source row (S1 is my PID field in the source). If it finds a match, it returns a value. If it doesn’t find a match, it returns a NULL value.

[img src=”/wp-content/uploads/2015/10/XRefValueVariable2-300×116.jpg” alt=”XRefValueVariable2″ class=”aligncenter”]

Now that we know what the variable returns for each row processed, we can use this information in our Target Steps. In each Target Step you have the ability to add a “Pre-Operation Step Flow Control Formula.” We can now create our own formula using Scribe functions to determine if a row has already been processed.

Let’s set the stage:

• We have an import file of 200,000 contacts.

• We create a Scribe import job to insert the contacts into MSCRM.

• We include Cross Reference Key information and add the Cross Reference Lookup as described above.

• We run the Scribe job.

• 30,000 records fail due to a data mismatch.

• We need to rerun the import job to insert all of the contacts which failed.

I know, you’re probably saying we could just change the Insert step to an Update/Insert step and return the job. True, but doing a call to MSCRM is more time consuming when we can easily check our local SQL table to see if a record has been processed.

In order to check if a record has already been processed, we add the following formula to the “Pre-Operation Step Flow Control Formula” in the first step of the Target process.

IF(ISERROR(XRefValue),TRUE(),NEXTROW())

[img src=”/wp-content/uploads/2015/10/PreOpFormula-300×216.jpg” alt=”PreOpFormula” class=”aligncenter”]

The reason we use ISERROR is because the XRefValue variable either returns a value or a NULL. If NULL, ISERROR(XRefValue) will be a True statement. We then use TRUE() to continue processing the Source record. If False, we use NEXTROW() to bypass this Source row becuase XRefValue returned a value and we then know this Contact record has already been succesfully insterted in MSCRM and a record was inserted into the KEYCROSSREFERENCE table.

There are other uses for the KEYCROSSREFERENCE table. Being able to keep track of processed data is very important.

Another example is to keep track of record owners when reassigning records through Scribe. If you need to revert back to the original owners for any reason, and the Cross Reference Key is setup properly, you’ll have previous and new owners safely stored in a SQL table available to revert back to ownership to their original state.

Data, it’s a wonderful thing. Beringer Associates is always here to provide expert knowledge in topics like these. Please contact us with any questions you may have.