Reference Fields Proof of Concept: Managing Large Option Sets
Last updated: July 11, 2025
The Challenge: When Simple Enums Aren't Enough
Picture this: You're setting up a data import for your global organization. Your users need to select countries for their records, but you notice the dropdown becomes sluggish as you add more options. While Flatfile's enum fields can technically handle more than 100 options, you'll start seeing performance degradation as you exceed this number, affecting user experience and import efficiency.
This performance challenge commonly appears when dealing with:
Country lists (195+ options)
Product categories (hundreds of options)
Department codes (can be thousands)
Industry-specific codes (ICD-10, NAICS, etc.)
Why Enums Start to Struggle
When enum fields grow beyond 100 options, you might notice:
Slower dropdown rendering
Laggy search/filter responses
Degraded overall performance
Poorer user experience
The Solution: Reference Fields
Reference fields in Flatfile offer an elegant solution to this limitation. Instead of a simple dropdown, reference fields create a relationship between sheets, allowing you to maintain a separate list of options that can scale to thousands of entries while providing a smooth, searchable selection experience.
Real-World Example: Global Company Database
Let's walk through a practical example of managing a global company database where each company needs to reference a country from a complete list of countries in the world.
The Implementation
import api from "@flatfile/api";
import api from "@flatfile/api";
export default function (listener) {
listener.namespace(["space:reference-demo"], (space) => {
space.on("job:ready", { job: "space:configure" }, async (event) => {
const { spaceId, environmentId, jobId } = event.context;
try {
await api.jobs.ack(jobId, {
info: "Setting up Reference Demo workbook...",
progress: 10,
});
// Create a list of countries (more than 100 options)
const countries = [
"Afghanistan",
"Albania",
"Algeria",
"Andorra",
"Angola",
"Antigua and Barbuda",
"Argentina",
"Armenia",
"Australia",
"Austria",
"Azerbaijan",
"Bahamas",
"Bahrain",
"Bangladesh",
"Barbados",
"Belarus",
"Belgium",
"Belize",
"Benin",
"Bhutan",
"Bolivia",
"Bosnia and Herzegovina",
"Botswana",
"Brazil",
"Brunei",
"Bulgaria",
"Burkina Faso",
"Burundi",
"Cambodia",
"Cameroon",
"Canada",
"Cape Verde",
"Central African Republic",
"Chad",
"Chile",
"China",
"Colombia",
"Comoros",
"Congo",
"Costa Rica",
"Croatia",
"Cuba",
"Cyprus",
"Czech Republic",
"Denmark",
"Djibouti",
"Dominica",
"Dominican Republic",
"East Timor",
"Ecuador",
"Egypt",
"El Salvador",
"Equatorial Guinea",
"Eritrea",
"Estonia",
"Ethiopia",
"Fiji",
"Finland",
"France",
"Gabon",
"Gambia",
"Georgia",
"Germany",
"Ghana",
"Greece",
"Grenada",
"Guatemala",
"Guinea",
"Guinea-Bissau",
"Guyana",
"Haiti",
"Honduras",
"Hungary",
"Iceland",
"India",
"Indonesia",
"Iran",
"Iraq",
"Ireland",
"Israel",
"Italy",
"Jamaica",
"Japan",
"Jordan",
"Kazakhstan",
"Kenya",
"Kiribati",
"North Korea",
"South Korea",
"Kuwait",
"Kyrgyzstan",
"Laos",
"Latvia",
"Lebanon",
"Lesotho",
"Liberia",
"Libya",
"Liechtenstein",
"Lithuania",
"Luxembourg",
"Madagascar",
"Malawi",
"Malaysia",
"Maldives",
"Mali",
"Malta",
"Marshall Islands",
"Mauritania",
"Mauritius",
"Mexico",
"Micronesia",
"Moldova",
"Monaco",
"Mongolia",
"Montenegro",
"Morocco",
"Mozambique",
"Myanmar",
"Namibia",
"Nauru",
"Nepal",
"Netherlands",
"New Zealand",
"Nicaragua",
"Niger",
"Nigeria",
"North Macedonia",
"Norway",
"Oman",
"Pakistan",
"Palau",
"Panama",
"Papua New Guinea",
"Paraguay",
"Peru",
"Philippines",
"Poland",
"Portugal",
"Qatar",
"Romania",
"Russia",
"Rwanda",
"Saint Kitts and Nevis",
"Saint Lucia",
"Saint Vincent and the Grenadines",
"Samoa",
"San Marino",
"Sao Tome and Principe",
"Saudi Arabia",
"Senegal",
"Serbia",
"Seychelles",
"Sierra Leone",
"Singapore",
"Slovakia",
"Slovenia",
"Solomon Islands",
"Somalia",
"South Africa",
"South Sudan",
"Spain",
"Sri Lanka",
"Sudan",
"Suriname",
"Sweden",
"Switzerland",
"Syria",
"Taiwan",
"Tajikistan",
"Tanzania",
"Thailand",
"Togo",
"Tonga",
"Trinidad and Tobago",
"Tunisia",
"Turkey",
"Turkmenistan",
"Tuvalu",
"Uganda",
"Ukraine",
"United Arab Emirates",
"United Kingdom",
"United States",
"Uruguay",
"Uzbekistan",
"Vanuatu",
"Vatican City",
"Venezuela",
"Vietnam",
"Yemen",
"Zambia",
"Zimbabwe",
];
// Create the workbook
const workbook = await api.workbooks.create({
spaceId,
environmentId,
name: "Reference Demo Workbook",
sheets: [
// Main sheet that will reference countries
{
name: "Company",
slug: "company",
fields: [
{
key: "name",
type: "string",
label: "Company Name",
constraints: [{ type: "required" }],
},
{
key: "country",
type: "reference",
label: "Country",
config: {
key: "name",
ref: "countries",
relationship: "has-many",
},
constraints: [{ type: "required" }],
description:
"Select from over 195 countries using reference field",
},
{
key: "founded_year",
type: "number",
label: "Founded Year",
constraints: [],
},
],
},
// Reference sheet with countries (more than 100 options)
{
name: "Countries",
slug: "countries",
fields: [
{
key: "name",
type: "string",
label: "Country Name",
constraints: [{ type: "required" }, { type: "unique" }],
},
{
key: "code",
type: "string",
label: "Country Code",
constraints: [],
},
],
},
],
});
await api.jobs.ack(jobId, {
info: "Populating countries data...",
progress: 50,
});
// Find the countries sheet
const countriesSheet = workbook.data.sheets.find(
(sheet) => sheet.slug === "countries"
);
if (countriesSheet) {
// Prepare country records for insertion
const countryRecords = countries.map((country) => ({
name: { value: country },
code: { value: country.substring(0, 3).toUpperCase() }, // 3-letter code generation
}));
// Insert records into the countries sheet
await api.records.insert(countriesSheet.id, countryRecords);
}
await api.jobs.ack(jobId, {
info: "Populating sample company data...",
progress: 80,
});
// Add sample company data
const sampleCompanies = [
{
name: "TechCorp Global",
country: "United States",
founded_year: 1995,
},
{ name: "Nordic Innovations", country: "Sweden", founded_year: 2005 },
{ name: "Sakura Technologies", country: "Japan", founded_year: 1980 },
{
name: "African Dynamics",
country: "South Africa",
founded_year: 2015,
},
{
name: "European Solutions",
country: "Germany",
founded_year: 2000,
},
{
name: "Maple Leaf Industries",
country: "Canada",
founded_year: 1988,
},
{ name: "Aussie Tech Co", country: "Australia", founded_year: 2010 },
{ name: "Brazilian Ventures", country: "Brazil", founded_year: 1992 },
{
name: "French Innovation Labs",
country: "France",
founded_year: 2008,
},
{
name: "British Systems Ltd",
country: "United Kingdom",
founded_year: 1975,
},
];
// Find the company sheet
const companySheet = workbook.data.sheets.find(
(sheet) => sheet.slug === "company"
);
if (companySheet) {
// Prepare company records for insertion
const companyRecords = sampleCompanies.map((company) => ({
name: { value: company.name },
country: { value: company.country },
founded_year: { value: company.founded_year },
}));
// Insert records into the company sheet
await api.records.insert(companySheet.id, companyRecords);
}
await api.jobs.complete(jobId, {
outcome: {
message: `Reference Demo workbook created with ${countries.length} countries and ${sampleCompanies.length} sample companies populated.`,
acknowledge: true,
},
});
} catch (error) {
console.error(
"[Reference Demo Listener] Error during space:configure:",
error
);
await api.jobs.fail(jobId, {
outcome: {
message: "Failed to set up Reference Demo workbook.",
acknowledge: true,
},
});
}
});
// Log all events in this namespace for debugging
space.on("**", async (event) => {
console.log(
`[Reference Demo Listener] Event '${event.topic}' received for sheet: ${event.context.sheetSlug}`
);
});
});
}
The Magic in Action
When users interact with this setup, they'll experience:
Smart Search: Type "Uni" and instantly see matches like "United States", "United Kingdom", etc.
No Limits: All 195+ countries are available
Data Integrity: Only valid countries can be selected
Fast Performance: Quick loading and smooth scrolling
Setting It Up in Your Project
Create the Listener
# In your project directory
touch reference-fields-listener.tsDeploy Your Listener
Copy the code above
Customize the fields for your needs
Deploy to your Flatfile environment
Create a Space
const space = await api.spaces.create({
name: "My Reference Demo",
namespace: "space:reference-demo"
});Beyond Countries: Other Use Cases
This pattern works great for many scenarios:
Product Management
{
name: "Product",
fields: [
{
key: "category",
type: "reference",
ref: "categories" // Links to sheet with 1000+ categories
}
]
}Healthcare Records
{
name: "Patient",
fields: [
{
key: "diagnosis",
type: "reference",
ref: "icd10_codes" // Thousands of diagnosis codes
}
]
}Financial Systems
{
name: "Transaction",
fields: [
{
key: "account_code",
type: "reference",
ref: "chart_of_accounts" // Hundreds of account codes
}
]
}Pro Tips for Success
Keep Reference Data Updated
// Add an action to update reference data { operation: "update-reference-data", mode: "background", label: "Update Reference Data" }Add Helpful Metadata
// In your reference sheet fields: [ { key: "name", type: "string" }, { key: "code", type: "string" }, { key: "description", type: "string" }, { key: "last_updated", type: "date" } ]Optimize for Performance
Index important fields
Include relevant search fields
Consider data volume when designing
Ready to Get Started?
Reference fields open up a world of possibilities for handling large option sets in Flatfile. Whether you're dealing with countries, categories, or codes, you can now provide a seamless experience for your users without running into option limits.
Need Help?
Remember, the key to success with reference fields is thinking about your data relationships. Take time to plan your reference data structure, and you'll create a robust, scalable solution for your users.