This is not comparable to a foreign key. There should be no specification of which column to reference as it is multi-variate. A user may establish a relationship in mapping with a soft reference like a company name. Once stored in our system, we will reference by our internal IDs, and on egress we provide the known system_id if available.

The relationship defines a reference to another Sheet. Links should be established automatically by the mapping engine (the logic that creates a mapping plan automatically when a user maps their data into a workbook) or similar upon an evaluation of unique or similar columns between datasets.

​
Configuration Options

  • ref - Full path reference to another Sheet/table configuration. Must be in the same Workbook
  • relationship - The type of relationship this defines. Currently, only has-one is supported

​
Usage

​
Inheritance

This example introduces two fields linked by a relationship, inheriting the respective email addresses of the father and mother from another Sheet.

workbooks.create
// in the context of create Workbook API call
{
  "fields": [
    {
      "key": "father",
      "type": "reference",
      "config": {
        "ref": "parents",
        "key": "email",
        "relationship": "has-one"
      }
    },
    {
      "key": "mother",
      "type": "reference",
      "config": {
        "ref": "parents",
        "key": "email",
        "relationship": "has-one"
      }
    }
  ]
}

​
Lookups

This example mimics a vlookup operation by fetching a value from a linked record and applying it to a designated field in the original record.

The linked record is designated by a reference field in the original record, and the sought-after value is defined by a lookup field in the linked record.

When a lookup value is located, it’s placed into the original record’s target field. Furthermore, a message is added to the record, detailing the origin of the retrieved value.

record
Object

The record to perform the vlookup on.

referenceFieldKey
string

The name of the reference field on the original record that links to the linked record.

lookupFieldKey
string

The name of the field on the linked record that contains the value to be looked up.

targetFieldKey
string

The name of the field on the original record that the lookup value should be set to.

// in the context of listener.use(recordHook){}

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 (isNotNil(lookupValue)) {
    record.set(targetFieldKey, lookupValue);
    record.addInfo(
      targetFieldKey,
      `${targetFieldKey} set based on ${referenceFieldKey}.`
    );
  }
};
ConstraintsAccess