Relationships

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.

​

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

​

Inheritance

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

// 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.

// 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}.`,
    );
  }
};