Create Table References Based on External IDs

In this guide, we'll walk through the process of creating internal table references in Xano based on matching external IDs across different data tables. This is particularly useful when importing or migrating data from external sources, where tables are connected through external IDs, and you want to establish internal references within Xano for seamless data integration.

Prerequisites

Before we begin, ensure that you have the following:

1. Data Tables: Two or more data tables with matching external IDs across them. In our example, we'll use a "Company" table and a "Project" table, both containing an "external ID" field.

2. Table Reference: A table reference field in one of the tables, pointing to the table you want to create the internal reference to. In our case, we'll add a table reference field called "company ID" in the "Project" table, referencing the "Company" table.

Step 1: Add a Table Reference Field

1. Navigate to the "Project" table in Xano.
2. Click on the "Add Field" button and select "Table Reference."
3. Choose the "Company" table as the target table for the reference.
4. Give the field an appropriate name, e.g., "company ID."

Step 2: Create a Function

1. In Xano, go to the "Functions" section and click "Add New Function."
2. Give the function a descriptive name, e.g., "Create Internal References."

Step 3: Query Data Tables

1. Inside the function, use the "Query Records" operation to fetch all records from both the "Company" and "Project" tables. Store these results in separate variables, e.g., companyRecords and projectRecords.

const companyRecords = await query("Company", {});
const projectRecords = await query("Project", {});

Note: If you have a large number of records, consider implementing pagination to avoid querying all records at once.

Step 4: Loop Through Project Records

1. Use a "For Each" loop to iterate through each record in the projectRecords array.

for (const project of projectRecords) {
 // Loop logic goes here
}

Step 5: Find Matching Company Record

1. Inside the loop, use the "Find First Element" operation to find the first matching company record based on the external ID.

const matchedCompany = companyRecords.find(
 (company) => company.external_ID === project.external_ID
);

This will search through the companyRecords array and return the first object where the external_ID matches the external_ID of the current project record.

Step 6: Update Project Record

1. Use the "Edit Record" operation to update the "company ID" field in the current project record with the ID of the matched company record.

await editRecord("Project", project.id, {
 company_ID: matchedCompany.id,
});

This will update the "company ID" field in the "Project" table with the ID of the matching company record, effectively creating the internal table reference.

Step 7: Run the Function

After completing the function setup, you can run the function to perform the internal data migration. All project records should now have the correct internal table reference to the associated company record.

You can verify the successful migration by navigating to the "Project" table and inspecting the "company ID" field values. Additionally, you can use the "Show Table References" feature in Xano to confirm that the "Project" and "Company" tables are properly linked.

By following these steps, you can easily migrate data from external sources and create internal table references within Xano, ensuring seamless data integration and relationships between your tables.