Version: v2.0

How to work with external data in Flatfile

One of the most useful features when working with Flatfile is the ability to work with data outside of Flatfile. You have the ability to do things such as use Flatfile to clean the user data already in your system, compare new data to the current data in your system during an import and keep data consistent as edits are being made. In this guide, we will show you some of the most useful ways to use Flatfile with your external data source as well as help you avoid some of the common pitfalls when using an external datasource with Flatfile.

Initializing the importer with data

Common use cases:

You have data in your database, and you need a user to verify its accuracy and/or make corrections.
You have data in a 3^rd party tool, and you would like to use Flatfile to help migrate and clean the data before using it in your system.
You have previously unfinished/unclean imports (maybe from before implementing Flatfile) that you need a user to finish.
Using Flatfile as a way to edit user data in an ongoing fashion.

When we talk about initializing the importer with data, we are talking about bypassing the standard, "choose a file" style import to access some external data using Flatfile as a means of editing and submitting the clean data. During this process, you are able to provide feedback in terms of errors and warnings that will indicate to the user what may need to be corrected.

If you check out our requestDataFromUser() SDK reference, you will notice that there is an optional object (LoadOptionsObject) you can pass into the method to initialize the importer with data instead of a user file. When you pass in this object, the matching stage of the import (which would include field hooks and record init hooks) is skipped and you will go to the Review phase of the import. In all of the common use case scenarios above, this will be the starting point. It is useful to know that within the InputObject SDK reference, you will pass in the data, but can optionally pass in the sequence (index number) and any error messaging. We allow for different error messaging, and you can learn more about the levels of messaging here.

Let's take a look at an example of how this works. For a typical import, you might call the requestDataFromUser() method like in the below example:

import FlatfileImporter from "@flatfile/adapter"

const importer = new FlatfileImporter("License Key", FlatfileConfig)

const launchFlatfile = () => {
  importer.requestDataFromUser()
}

let launchButton = document.getElementById('launch')

launchButton.addEventListener('click', launchFlatfile)

For the sake of the example, we are taking Flatfile and launching an import using the click event of the launchButton which is assumed to be a valid element in your UI.

To make things easier to visualize, we are going to set a variable with some data and pass that into the method. Keep in mind that you can work with external data, but it will need to have the same data structure as what you see here.

const passedInData = {
  source: [
    {
      data: { firstName: "John", email: "john@doe.com", userId: 12345 },
      errors: [{ key: "userId", message: "userId already exists in database", level: "error" }],
    },
    {
      data: { firstName: "", email: "jane@doe", userId: 54321 },
      errors: [
        { key: "firstName", message: "Name cannot be blank", level: "error" },
        { key: "email", message: "Email invalid. Please correct email address", level: "error" },
      ],
    },
  ],
};

Now, we can pass this object into the method as seen below:

const launchFlatfile = () => {
  importer.requestDataFromUser(passedInData);
};

And when the importer starts, your user would see the below screenshot.

Portal Errors Screenshot

Validating a field against an external datasource

Common use cases:

Needing to make sure values being imported aren't duplicated in your system.
Formatting data with functionality from your server.

Being able to validate your data against a server or use an external resource for formatting data is useful in the process for having clean data. With Field Hooks, you can send the entire dataset of a particular field to your server or some form of external validation. In the below example, we have used typicode's jsonplaceholder API to replicate having some data to validate against on a server. You can check out our data on GitHub, but what we are looking for is to make sure that there are no emails in the following data that are already on our server.

First name	User ID	Email
David	0001	david@flatfile.com
John	0005	john@doe.com
Paula	0009	paula@fakeemail.com

note

In this example, we use the placeholder API to return data to us from the outside source, but you can also send the fields to your server and process the information there.

Below is what the code would look like for validating this against the external datasource.

import FlatfileImporter from "@flatfile/adapter";
import $ from "jquery";

const importer = new FlatfileImporter("4171f0b4-5f5c-4b32-a008-356ebb813e4e", {
  fields: [
    {
      label: "Name",
      key: "name",
    },
    {
      label: "User ID",
      key: "userId",
    },
    {
      label: "Email Address",
      key: "email",
    },
  ],
  type: "Contact",
});
importer.registerFieldHook("email", async (values) => {
  let out = [];
  let userEmails = await fetch("https://my-json-server.typicode.com/flatfilers/csb-api/users")
    .then((response) => response.json())
    .then((json) => Array.from(json, (x) => x.email));
  values.forEach((item) => {
    if (userEmails.includes(item[0])) {
      console.log("it includes it");
      out.push([
        {
          info: [
            {
              message: "FIELD HOOK UPDATE: Record already exists externally",
              level: "error",
            },
          ],
        },
        item[1],
      ]);
    }
  });
  console.log(out);
  return out;
});

importer.setCustomer({
  userId: "19234",
  name: "Jon",
});

$("#launch").click(function () {
  importer
    .requestDataFromUser()
    .then(function (results) {
      console.log(results);
      importer.displaySuccess("Thanks for your data.");
    })
    .catch(function (error) {
      console.info(error || "window close");
    });
});

What happens in the example with the data provided is that the first row comes back showing that the email is already in the data and is a duplicate value.

Using record hooks with external data

You can also use record hooks with external data. The key thing to remember when using record hooks with an external datasource is that these do not run as one bulk operation like field hooks, but rather run on each individual record. So while it is absolutely possible to use record hooks on initialization of the importer, it is best practice to use field hooks on initialization for working with external data, and then use record hooks on change to keep the information updated. In this example, we will add onto the fields hooks example from above, but we will add the record hooks validation to the change event to make sure that when a record is updated, we didn't update an email address to one already listed in the system.

import FlatfileImporter from "@flatfile/adapter";
import $ from "jquery";

const importer = new FlatfileImporter("4171f0b4-5f5c-4b32-a008-356ebb813e4e", {
  fields: [
    {
      label: "Name",
      key: "name",
    },
    {
      label: "User ID",
      key: "userId",
    },
    {
      label: "Email Address",
      key: "email",
    },
  ],
  type: "Contact",
});
importer.registerFieldHook("email", async (values) => {
  let out = [];
  let userEmails = await fetch("https://my-json-server.typicode.com/flatfilers/csb-api/users")
    .then((response) => response.json())
    .then((json) => Array.from(json, (x) => x.email));
  values.forEach((item) => {
    if (userEmails.includes(item[0])) {
      console.log("it includes it");
      out.push([
        {
          info: [
            {
              message: "FIELD HOOK UPDATE: Record already exists externally",
              level: "error",
            },
          ],
        },
        item[1],
      ]);
    }
  });
  console.log(out);
  return out;
});

importer.registerRecordHook(async (record, index, mode) => {
  console.log(mode); // this will log either "init" or "change"
  let out = {};
  if (record.email && mode === "change") {
    let userEmails = await fetch("https://my-json-server.typicode.com/flatfilers/csb-api/users")
      .then((response) => response.json())
      .then((json) => Array.from(json, (x) => x.email));
    if (userEmails.includes(record.email)) {
      out.email = {
        info: [
          {
            message: "RECORD HOOK UPDATE: Record already exists externally",
            level: "error",
          },
        ],
      };
    }
  }
  return out;
});

importer.setCustomer({
  userId: "19234",
  name: "Jon",
});

$("#launch").click(function () {
  importer
    .requestDataFromUser()
    .then(function (results) {
      console.log(results);
      importer.displaySuccess("Thanks for your data.");
    })
    .catch(function (error) {
      console.info(error || "window close");
    });
});

Notice that with the record hooks we added here, we are verifying that the mode (which can be thought of as the record hook event) is change before proceeding to perform a server call. Filtering out the init hooks here prevents the code from running each record individually on initialization which could cause as many server calls as there are records in the file.

If you want to learn more about Data Hooks® and their best practices, you can check out our guide on Data Hooks® best practices.

How to work with external data in Flatfile

Initializing the importer with data​

Validating a field against an external datasource​

Using record hooks with external data​

Initializing the importer with data

Validating a field against an external datasource

Using record hooks with external data