This article is for the latest version of the Flatfile Data Exchange Platform.
By default, Reference Fields create a 1:1 link between two sheets. Where identical unique values exist on two different sheets, those values can be leveraged to link the data on those sheets and create a more complex record.
In some cases, you may want to update one sheet with additional values from the linked sheet to ensure those values are exact matches between the two sheets. To do that, you can use a hook deployed in your listener to grab those values and set a field in one sheet based on the value in the linked sheet.
A basic hook for this is here:
/**
Performs a vlookup-like operation on a record by retrieving a value from a linked record
and setting it to a target field on the original record. The linked record is specified
by a reference field on the original record, and the lookup value is specified by a
lookup field on the linked record. If a lookup value is found, it is set to the target
field on the original record and an info message is added to the record indicating
the source of the value.
@param {Object} record - The record to perform the vlookup on.
@param {string} referenceFieldKey - The name of the reference field on the original record
that links to the linked record.
@param {string} lookupFieldKey - The name of the field on the linked record that contains
the value to be looked up.
@param {string} targetFieldKey - The name of the field on the original record that the
lookup value should be set to.
*/
export const vlookup = (
record,
referenceFieldKey,
lookupFieldKey,
targetFieldKey
) => {
console.log('Initial Record: ' + JSON.stringify(record));
const links = record.getLinks(referenceFieldKey);
console.log('Linked Record: ' + JSON.stringify(links));
const lookupValue = links?.[0]?.[lookupFieldKey];
console.log(
'Reference Fields Key: ' +
referenceFieldKey +
' : ' +
'LookUpValue: ' +
lookupValue
);
if (lookupValue) {
record.set(targetFieldKey, lookupValue);
record.addInfo(
targetFieldKey,
`${targetFieldKey} set based on ${referenceFieldKey}.`
);
}
};
Alternatively, this could be configured like this in your code:
// Get relevant fields from record
const hireDate = record.get('hireDate')
const endEmploymentDate = record.get('endEmploymentDate')
const job = record.getLinks('jobName')
const title = record.get('positionTitle')
const empType = record.get('employeeType')
// Get the effective date and inactive status of the job (if it exists)
// `effDate` is the effective date of the job, or `null` if no job is linked to the record
const effDate = isNotNil(job) ? job[0]?.effectiveDate : null