Every time we use the term “foreign key lookup”, we see eyes glaze over. That’s not surprising, in the current political climate introducing talk of foreign keys makes people worried we’re going to draw lines over Brexit or the recent US midterms.
In this post we’ll ignore both Brexit and the midterms. Rather, we’ll explore how we use foreign key lookups as part of a broader set of wizardry to ensure that data in the target system looks like it actually belongs in the target system.
Let’s start with putting some data in our minds:
- Customers belong to an Account.
- Helpdesk tickets are logged by a Customer and on behalf of an Account.
- Phone calls from a Customer about a Helpdesk ticket need to be related to that ticket. The ticket exists in the context of the Customer that logged it on behalf of a specific Account.
And so on and so forth. Also, it is becoming evident why Recursyv is named for some very geeky word play to do with recursion.
Integrating is about more than just plugging a virtual cable between two different pieces of software. Data exists in context and that context needs to be maintained on both sides of an integration. Properly integrated data will show in context in both systems.
Foreign key checks and balances
For each each piece of data we shift between systems, there is a series of checks we need to perform. This allows us load the data into target system in the correct context.
It starts at a pretty basic level … what is this piece of data? Is it a Customer, an Account, an Event, a Case, etc. Typically we shift data by type, so bearing in mind we’re looking at data of type “Customer”, we can then run subsequent checks.
Has the Customer record changed? Transferring every record every time we sync would be a significant time / processing overhead. We want to focus our efforts only where data has changed. The sophistication of the source system’s API will determine whether we can simply ask for data based on changes to a specific field. If not, we need to perform some fancy footwork inside Seamless to determine whether a record needs to be shifted.
Does the target system already know this Customer? We can use record identifiers to determine whether the updated record is an existing Customer or not. If we cannot find the Customer, we need to create a new one. For some record types there may be additional fields which uniquely identify the record, e.g. for Customer we can also search by email address.
Does this Customer belong to an existing Account? If this is the case, typically we’ll have an Account identifier from the source system that we need to look up in the target system. That way we can identify the correct Account record in the target system. If the Account does not yet exist in the target system, we may have to create both the Account record and the Customer record.
Is there any associated reference data which needs to be translated? Many applications have controlled lists for certain fields, e.g. the title field may allow only “Dr, Mr, Mrs, Miss, Ms, Sith Commander”, etc. As you’d imagine, different applications will have different values and even slightly different labels allowable in their lists, e.g. “Prof.” v. “Professor”.
What becomes of all these checks?
Running these checks allows us to turn the content of some data in transit into a piece of data formatted for the target system. Imagine we’re syncing a customer, a Mr Wile E Coyote of Acme Inc, from Salesforce to Dynamics. We’ll perform all the checks described as part of transforming the data from Salesforce flavoured to Dynamics flavoured.
- Wile E Coyote has been created in Salesforce so there is a change to the data which we need to sync.
- We look for Wile E Coyote in Dynamics and there is no record, so we need to create a new record.
- Wile E Coyote belongs to an existing Account “Acme Inc”.
- We have the Salesforce Account record identifier for Acme Inc which we will use to lookup the Account in Dynamics. That lets us know the Dynamics Account record identifier so we’ll add that to the new Customer record in the “Parent Account” field.
- Wile E Coyote has the title of “Mr”, we need to translate that to the underlying list value in Dynamics and include that in the record we create.
What’s all this talk of foreign agents?
A final explanation – why “foreign key lookup”? Well, in database terms, a primary key is a field on the database table that uniquely identifies a record (e.g. a customer reference number). A foreign key is a field on a record that refers to a record on another table (e.g. on the customer record, a common foreign key field is the record identifier of the account that the customer belongs to).