The relationship defines a reference to another Sheet, similar to a Foreign Key. 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