Fields: Relational

This script demonstrates how to create & work with the following relational fields in NocoDB: LinkToAnotherRecord, Lookup, Rollup
For detailed descriptions of each field type and associated configuration options, refer to the NocoDB meta API documentation.
// NocoDB Script: Relational Fields Demo (LinkToAnotherRecord, Lookup, Rollup)

const projectsTableName = 'Projects';
const tasksTableName = 'Tasks';

// ---------------------
// Ensure Projects Table
// ---------------------
let projectsTable = base.getTable(projectsTableName);
if (!projectsTable) {
    projectsTable = await base.createTableAsync(projectsTableName, [
        { name: 'Project Name', type: "SingleLineText" },
        { name: 'Client', type: "SingleLineText" }
    ]);
    output.text(`✅ Created table "${projectsTableName}"`);
}

// ---------------------
// Ensure Tasks Table
// ---------------------
let tasksTable = base.getTable(tasksTableName);
if (!tasksTable) {
    tasksTable = await base.createTableAsync(tasksTableName, [
        { name: 'Task Name', type: "SingleLineText" },
        { name: 'Status', type: "SingleSelect", options: {
                choices: [
                    { title: 'To Do', color: '#6c757d' },
                    { title: 'In Progress', color: '#007bff' },
                    { title: 'Done', color: '#28a745' }
                ]
            }},
        { name: 'Hours', type: "Number" }
    ]);
    output.text(`✅ Created table "${tasksTableName}"`);
}

// ---------------------
// Create LinkToAnotherRecord: Tasks → Projects
// ---------------------
if (!tasksTable.getField('Project')) {
    await tasksTable.createFieldAsync({
        name: 'Project',
        type: "LinkToAnotherRecord",
        options: {
            relation_type: 'mm',
            related_table_id: projectsTable.id
        }
    });
    output.text(`🔗 Created "Project" field in "Tasks" as LinkToAnotherRecord to "Projects"`);
}

// ---------------------
// Create Lookup: Client info in Tasks from linked Project
// ---------------------
if (!tasksTable.getField('Project Client')) {
    await tasksTable.createFieldAsync({
        name: 'Project Client',
        type: "Lookup",
        options: {
            related_field_id: tasksTable.getField('Project').id,
            related_table_lookup_field_id: projectsTable.getField('Client').id
        }
    });
    output.text(`🔎 Created Lookup field "Project Client" in "Tasks"`);
}

// ---------------------
// Create Rollup: Total task hours per project
// ---------------------
if (!projectsTable.getField('Total Task Hours')) {
    await projectsTable.createFieldAsync({
        name: 'Total Task Hours',
        type: "Rollup",
        options: {
            related_field_id: projectsTable.getField('Tasks').id,
            related_table_rollup_field_id: tasksTable.getField('Hours').id,
            rollup_function: 'sum'
        }
    });
    output.text(`📊 Created Rollup field "Total Task Hours" in "Projects"`);
}

// ---------------------
// Summary Output
// ---------------------
output.markdown(`### 🧾 Fields in "${tasksTableName}"`);
output.table(tasksTable.fields.map(f => ({
    'Field Name': f.name,
    'Type': f.type
})));

output.markdown(`### 🧾 Fields in "${projectsTableName}"`);
output.table(projectsTable.fields.map(f => ({
    'Field Name': f.name,
    'Type': f.type
})));