Version: v3.0

Configure your engine

Highlights:

What is an engine?
Sheets
Portals
Workbooks
Field examples

What is an engine?

An engine, also called a data engine, is a server-side processor that accepts, validates, and transforms data from your users. You create and configure your engine in code using our SDK. You then deploy your engine to Flatfile's servers, where it listens for incoming data. When it receives data, it processes and validates it according to your rules, then makes it available to fetch via the Flatfile API.

The main components of an engine are:

Sheets, also known as data templates, where you describe the shape of data you want to accept and the validations and transformations you want to perform
Portals, the UI components that accept data from your users
Workbooks, collections of associated Sheets and Portals that you deploy to Flatfile

Sheets

A Sheet is a data template, or a description of one type of data that you want your system to accept.

Sheets contain Fields, which are similar to columns in a database, and represent individual properties of the data record (e.g. "first name", "last name"). In addition to Fields, your Sheet can have template-wide hooks and options, which are defined in the Sheet options object.

// src/index.ts
// ...
// Define Sheets with fields
const Employees = new Sheet(
  "Employees Sheet",
  {
    // Define Fields
    firstName: TextField({
      required: true,
      label: "First Name",
    }),
    lastName: TextField({
      required: true,
      label: "Last Name",
    }),
  },
  {
    // Optional - Sheet options (see https://flatfile.com/versioned-docs/3.0/guides/workbooks/#sheet-options)
    // allowCustomFields: true,
    // recordCompute: (record) => {},
    // batchRecordsCompute: async (payload: FlatfileRecords<any>) => {},
  },
);
// ...

Fields

A Field object represents a column of data in a Sheet. They are similar to columns in a database. Fields have three defining features:

Type: the basic type of data stored in this field (e.g. text, number, boolean)
Options: additional properties describing the field (e.g. required, unique, default value)
Hooks: functions that clean, transform, and validate incoming data

// src/index.ts
// ...
fieldName: TextField({
  label: "Name",
  description:
    "A favorite type of field like: TextField, NumberField, DateField, OptionField, BooleanField ...",
  required: true,
  unique: false,
  default: "Did not awnser :(",
});
// ...

Field types

Field type	Description
`TextField`	a string of text
`NumberField`	a numerical value
`DateField`	a date
`OptionField`	a field with a set of pre-defined values
`BooleanField`	a true / false field
`LinkedField`	a field that links two sheets together (available with Workspaces)

Field options

Field option	Value type	Description
`label`	`string`	A label that is displayed to user.
`description`	`string`	Description of the field.
`primary`	`boolean`	a primary key for a sheet that must be unique.
`required`	`boolean`	Requires a value to be valid.
`unique`	`boolean`	Must have a unique value to be valid.
`default`		The default value for this field.
`cast`	`function`	Takes string or undefined and return either primitive type specified by the field, null, or throw an error.
`compute`	`function`	Transform a typed value to typed value.
`validate`	`function`	Returns annotations (e.g. warn, error or comments) about a typed value.
`annotations`	`object`	Automatically filled in messages when `default` or `compute` changes a value.
`stageVisibility`	`object`	Controls what parts of mapping/review/export a field occurs in.
`matchStrategy`	`string`	(`OptionField` only) Sets whether Flatfile should only accept exact matches for OptionField values. This field takes either 'fuzzy' or 'exact' as a variable.
`sheet`	`Sheet`	(`LinkedField` only) Selects the sheet that this field should be linked to.
`upsert`	`boolean`	(`LinkedField` only) Controls whether values should be upserted to the sheet you are linking to. Default is true.

See the SDK Object reference for more detail on Field types and options.

Field Hooks

Field Hooks are functions that help to clean up or validate the input data of a field.

There are three options: cast, compute and validate. See this guide to learn more about Field Hooks.

salary: NumberField({
  label: "Salary",
  description: "Annual Salary in USD",
  required: true,
  validate: (salary: number) => {
    const minSalary = 30_000;
    if (salary < minSalary) {
      return [new Message(`${salary} is less than minimum wage ${minSalary}`, "warn", "validate")];
    }
  },
});

ComputedField

A ComputedField is a field that is derived from the value of other fields. It is not expected to be present in an upload, so it is not shown to the user during field mapping.

const mySheet = new Sheet(NumberField({}), {
  hours: NumberField({}),
  rate: NumberField({}),
  pay: ComputedField(NumberField({}), {
    dependsOn: ["hours", "rate"],
    compute: (record: Record<string, TPrimitive>) => {
      const hours = record["hours"] as number;
      const rate = record["rate"] as number;
      return hours*rate;
    }
    destination: "pay",
  }),
});

dependsOn takes an array of fields that are used to compute the ComputedField, and compute receives the entire record. destination is the name of the field to save the result to. The fields in dependsOn must be present on the record, otherwise the ComputedField will throw an error.

To include optional fields in a ComputedField, use the possiblyDependsOn option:

const mySheet = new Sheet({
  firstName: TextField({}),
  middleName: TextField({}),
  lastName: TextField({}),
  fullName: ComputedField(TextField({}), {
    dependsOn: ["firstName", "lastName"],
    possiblyDependsOn: ["middleName"],
    compute: (rec: Record<string, TPrimitive>) => {
      const firstName = rec["firstName"] as string;
      const middleName = rec["middleName"] as string;
      const lastName = rec["lastName"] as string;
      if (middleName) {
        return `${firstName} ${middleName} ${lastName}`
      } else {
        return `${firstName} ${lastName}`
      }
    }
    destination: "fullName",
  }),
});

Sheet Options

Sheet option	Type	Description
`allowCustomFields`	`boolean`	Create additional fields from upload data when incoming column does not match any existing field.
`recordCompute`	`function`	Function that receives a row with all required fields fully present and optional fields typed optional?:string. Best used to compute derived values, can also be used to update existing fields.
`batchRecordsCompute`	`function`	Asynchronous function that is best for HTTP/API calls. External calls can be made to fill in values from external services. This takes records so it is easier to make bulk calls.
`previewFieldKey`	`string`	Specifies which field should be used as a human-readable preview for records in this sheet when it is linked to by a `LinkedField`.

const Employees = new Sheet(
  "Employees Sheet",
  {
    // Define Fields
    firstName: TextField({
      required: true,
    }),
    lastName: TextField({
      required: true,
    }),
    fullName: TextField(),
  },
  {
    // Sheet options with example Data Hooks
    allowCustomFields: true,
    recordCompute: (record) => {
      const fullName = `{record.get('firstName')} {record.get('lastName')}`;
      record.set("fullName", fullName);
      return record;
    },
    batchRecordsCompute: async (payload: FlatfileRecords<any>) => {
      const response = await fetch("https://api.us.flatfile.io/health", {
        method: "GET",
        headers: {
          Accept: "application/json",
        },
      });
      const result = await response.json();
      payload.records.map(async (record: FlatfileRecord) => {
        record.set("fromHttp", result.info.postgres.status);
      });
    },
  },
);
// ...

See this guide for more information about recordCompute and batchRecordsCompute.

Portals

A Portal is an embedded UI component for uploading data. Your users will use the Portal to upload their files. View a live example of a Portal here.

To create a Portal give it a name and assign it to a sheet defined in the workbook. Use the name of the variable you assigned your Sheet to, not the name of the Sheet. In this example, the variable name is Employees, so you'd pass sheet: "Employees".

Example:

const EmployeesPortal = new Portal({
  name: "Employees Portal",
  sheet: "Employees",
});

See this guide for instructions on how to embed a Portal into your application.

Workbooks

A Workbook is collection of associated Sheets and Portals. Workbooks wrap up a set of Sheets and Portals into one object, which you will then deploy to Flatfile.

tip

The Quickstart guide shows you how to generate a starter Workbook that comes with examples.

To create a Workbook:

Import dependencies.
Define Sheets with Fields.
Define Portals (optional).
Export the Workbook.

This is what the starting Workbook scaffold looks like in the starter project at src/index.ts:

src/index.ts
// 1. Import dependencies
import { NumberField, Portal, Sheet, TextField, Workbook } from "@flatfile/configure";

/**
 * 2. Define Sheets with Fields
 * Define your Sheet configuration and Fields here, or import them:
 * import { YourSheet } from './path-to-your-sheet/your-sheet.ts'
 */
const MySheet = new Sheet("My Sheet", {
  firstName: TextField(),
  lastName: TextField(),
  age: NumberField(),
});

/**
 * 3. Define Portals (optional)
 * Define your Portals here, or import them:
 * import { YourPortal } from './path-to-your-portal/your-portal.ts'
 */
const MyPortal = new Portal({
  name: "My Portal",
  sheet: "MySheet",
});

// 4. Export Workbook  - Update to reference your Workbook with Sheet(s) and Portal(s)
export default new Workbook({
  name: "MyWorkbook",
  namespace: "my-workbook",
  portals: [MyPortal],
  sheets: {
    MySheet,
  },
});

To deploy your Workbook to Flatfile, export it from your project's src/index.ts file and run the following command:

npx flatfile publish

Alternatively, you can specify a path to any file where a Workbook is exported:

npx flatfile publish ./path-to-your-workbook

To deploy the FullExample Workbook in the starter project from the quickstart guide, you would run:

npx flatfile publish ./examples/workbooks/FullExample.ts

Field examples

TextField

A field that represents a string of text. Note: You don't need to supply all the field options. In this case we want the fullName field to be unique.

fullName: TextField({
  required: true,
  unique: true,
  description: "Given name",
});

NumberField

A field that represtents a number. This example uses a validate function to determine if value is valid for this field. In this example, salary must be greater than or equal to $30,000, and provides a message to be displayed when it's not valid.

salary: NumberField({
  label: "Salary",
  description: "Annual Salary in USD",
  required: true,
  validate: (salary: number) => {
    const minSalary = 30_000;
    if (salary < minSalary) {
      return [new Message(`${salary} is less than minimum wage ${minSalary}`, "warn", "validate")];
    }
  },
});

DateField

A date field converts most string values into a Date object. A warning validation appears if the input value doesn't match to a date.

hireDate: DateField({
  required: true,
  description: "hire date",
});

By default, we return the field formatted using Date.toLocaleDateString(). If you would like to override the way it is formatted, you can pass a callback function to the egressFormat option.

hireDate: DateField({
  required: true,
  description: "hire date",
  egressFormat: (v: Date) => {
    // v = new Date('August 19, 1975 23:15:30');
    return v.toTimeString();
    // Expected output: "23:15:30 GMT+0200 (CEST)"
    // Note: your timezone may vary
  },
});

OptionField

A field with a set of pre-defined values. Incoming values are matched to the options provided in options based on the strategy provided in matchStrategy. Strategy options are:

fuzzy (default): compares the record to the list of category options and labels to find and recommend approximate matches.

exact: requires a 100% match between the value in the field and the category option in your template. This is case insensitive, so “sales” and “Sales” would still count as a match.

department: OptionField({
  label: "Department",
  matchStrategy: "exact", // default is: fuzzy
  options: {
    engineering: "Engineering",
    hr: "People Ops",
    sales: "Revenue",
  },
});

BooleanField

A field that is true or false.

isInactive: BooleanField({
  required: false,
  description: "Is the employee inactive?",
});

LinkedField

A field that links two sheets together. The sheet attribute specifies which sheet this field links to.

Note

LinkedField can only be implemented in Workspaces. Please contact support@flatfile.io if you are not using Workspaces and would like access to this feature.

Note: You may optionally set upsert: false which disables the default upsert behavior and will display an error on this sheet if a value imported in the LinkedField does not exist on the parent sheet. For more information about our Relational Data feature, visit our Relational Data Guide.

email: LinkedField({
  unique: true,
  label: "Email",
  primary: true,
  sheet: BaseSheet,
  upsert: false,
});

Here is the sheet linked to in the above field.

Note that the linked sheet must contain at least one field marked as unique.

Note: You must specify a previewFieldKey as an additional sheet option to define which field will be used as a human-readable preview for the linked record.

const BaseSheet = new Sheet(
  "BaseSheet",
  {
    email: TextField({
      unique: true,
      primary: true,
    }),
    firstName: TextField("First"),
    middleName: TextField("Middle"),
    lastName: TextField("Last"),
  },
  {
    previewFieldKey: "email",
  },
);

Creating reusable fields

Suppose you have a TextField for collecting email addresses, and you use the field's validate hook to run some basic regex to validate that the data is formatted like an email address:

src/index.ts
import { Message, TextField, Sheet } from "@flatfile/configure";

const MySheet = new Sheet("Person", {
  email: TextField({
    required: true,
    validate: (val: string) => {
      const emailRegex = /[a-zA-Z0-9]+@[a-zA-Z0-9]+\.[a-zA-Z0-9]+/;
      if (!emailRegex.test(val)) {
        return [new Message(`${val} is not formatted like an email`, "warn", "validate")];
      }
    },
  }),
});

Now suppose you have six different Sheets that all have email address as one of their fields. Instead of writing the same validate hook six times, it would be easier and more maintainable to write a single, shared EmailField that can be used in all six Sheets. That's where makeField comes in.

Flatfile provides a utility function called makeField that allows you to create new, reusable field types. makeField receives a base field type and an object containing the options you want to set.

In the above example, instead of defining the validation for an email field inline in the Sheet configuration, we can use makeField to create a reusable email field:

src/fields/EmailField.ts
import { makeField, Message, TextField } from "@flatfile/configure";

export const EmailField = makeField(TextField(), {
  validate: (val: string) => {
    const emailRegex = /[a-zA-Z0-9]+@[a-zA-Z0-9]+\.[a-zA-Z0-9]+/;
    if (!emailRegex.test(val)) {
      return [new Message(`'${val}' is not formatted like an email`, "warn", "validate")];
    }
  },
});

You can then use this field in any Sheet configuration by importing it and using it like any other field type:

src/index.ts
import { Sheet } from "@flatfile/configure";
import { EmailField } from "./fields/EmailField";

const MySheet = new Sheet("Person", {
  email: EmailField({
    required: true,
  }),
});

Any Sheet using this field will get the regex validation that you wrote in src/fields/EmailField.ts. Note that you can still use all of the options that exist on the base TextField - in this case, we're using the required option.

Example: PercentageField

This field uses the cast hook to accept percentages as numerical values. For example, the string "50%" will be cast to 0.5, while regular numbers will be left as-is.

src/fields/PercentageField.ts
import { makeField, NumberField } from "@flatfile/configure";

export const PercentageField = makeField(NumberField(), {
  cast: (val: any) => {
    const stringVal = String(val).trim();
    let stringNumericalVal;
    let multiplier;
    if (stringVal.charAt(stringVal.length - 1) === "%") {
      stringNumericalVal = stringVal.substring(0, stringVal.length - 1);
      multiplier = 0.01;
    } else {
      stringNumericalVal = stringVal;
      multiplier = 1;
    }
    const numericalVal = parseFloat(stringNumericalVal);
    if (isNaN(numericalVal)) {
      return null;
    } else {
      return numericalVal * multiplier;
    }
  },
});

src/index.ts
import { NumberField, Sheet } from "@flatfile/configure";
import { PercentageField } from "./fields/PercentageField";

const MySheet = new Sheet("Loan", {
  principal: NumberField({
    required: true,
  }),
  interestRate: PercentageField({
    required: true,
    default: 0.05,
  }),
});

Highlights:​

What is an engine?​

Sheets​

Fields​

Field types​

Field options​

Field Hooks​

ComputedField​

Sheet Options​

Portals​

Workbooks​

Field examples​

TextField​

NumberField​

DateField​

OptionField​

BooleanField​

LinkedField​

Creating reusable fields​

Example: PercentageField​