quickbase_create_advanced_relationship
Define and establish table relationships in QuickBase, automatically creating lookup fields between parent and child tables to streamline data connections and enhance database structure.
Instructions
Create a comprehensive table relationship with automatic lookup fields
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| childTableId | Yes | Child table ID | |
| lookupFields | No | Lookup fields to create automatically | |
| parentTableId | Yes | Parent table ID | |
| referenceFieldLabel | Yes | Label for the reference field to create | |
| relationshipType | No | Type of relationship | one-to-many |
Implementation Reference
- src/quickbase/client.ts:259-301 (handler)The core handler function that implements the advanced relationship creation: creates reference field, establishes relationship, and optionally creates lookup fields.async createAdvancedRelationship( parentTableId: string, childTableId: string, referenceFieldLabel: string, lookupFields?: Array<{ parentFieldId: number; childFieldLabel: string }>, relationshipType: 'one-to-many' | 'many-to-many' = 'one-to-many' ): Promise<{ referenceFieldId: number; lookupFieldIds: number[] }> { try { // Step 1: Create the reference field in the child table const referenceFieldId = await this.createField(childTableId, { label: referenceFieldLabel, fieldType: 'reference', required: false, unique: false, properties: { lookupTableId: parentTableId } }); // Step 2: Create the relationship await this.createRelationship(parentTableId, childTableId, referenceFieldId); // Step 3: Create lookup fields if specified const lookupFieldIds: number[] = []; if (lookupFields && lookupFields.length > 0) { for (const lookup of lookupFields) { const lookupFieldId = await this.createLookupField( childTableId, parentTableId, referenceFieldId, lookup.parentFieldId, lookup.childFieldLabel ); lookupFieldIds.push(lookupFieldId); } } return { referenceFieldId, lookupFieldIds }; } catch (error) { console.error('Error creating advanced relationship:', error); throw error; } }
- src/tools/index.ts:419-449 (registration)Tool registration in the quickbaseTools array, including name, description, and inputSchema.{ name: 'quickbase_create_advanced_relationship', description: 'Create a comprehensive table relationship with automatic lookup fields', inputSchema: { type: 'object', properties: { parentTableId: { type: 'string', description: 'Parent table ID' }, childTableId: { type: 'string', description: 'Child table ID' }, referenceFieldLabel: { type: 'string', description: 'Label for the reference field to create' }, lookupFields: { type: 'array', items: { type: 'object', properties: { parentFieldId: { type: 'number', description: 'Field ID in parent table to lookup' }, childFieldLabel: { type: 'string', description: 'Label for lookup field in child table' } }, required: ['parentFieldId', 'childFieldLabel'] }, description: 'Lookup fields to create automatically' }, relationshipType: { type: 'string', enum: ['one-to-many', 'many-to-many'], default: 'one-to-many', description: 'Type of relationship' } }, required: ['parentTableId', 'childTableId', 'referenceFieldLabel'] } },
- src/tools/index.ts:77-86 (schema)Zod schema definition for input validation of the tool.const CreateAdvancedRelationshipSchema = z.object({ parentTableId: z.string().describe('Parent table ID'), childTableId: z.string().describe('Child table ID'), referenceFieldLabel: z.string().describe('Label for the reference field to create'), lookupFields: z.array(z.object({ parentFieldId: z.number().describe('Field ID in parent table to lookup'), childFieldLabel: z.string().describe('Label for lookup field in child table') })).optional().describe('Lookup fields to create automatically'), relationshipType: z.enum(['one-to-many', 'many-to-many']).default('one-to-many').describe('Type of relationship') });
- src/quickbase/client.ts:303-323 (helper)Helper method used by createAdvancedRelationship to create individual lookup fields.async createLookupField( childTableId: string, parentTableId: string, referenceFieldId: number, parentFieldId: number, lookupFieldLabel: string ): Promise<number> { const response = await this.axios.post('/fields', { tableId: childTableId, label: lookupFieldLabel, fieldType: 'lookup', properties: { lookupReference: { tableId: parentTableId, fieldId: parentFieldId, referenceFieldId: referenceFieldId } } }); return response.data.id; }
- src/quickbase/client.ts:242-248 (helper)Helper method called by createAdvancedRelationship to establish the basic relationship.async createRelationship(parentTableId: string, childTableId: string, foreignKeyFieldId: number): Promise<void> { await this.axios.post('/relationships', { parentTableId, childTableId, foreignKeyFieldId }); }