User-Defined Tables Guide

Create and manage custom data tables for configuration, mappings, and dynamic data storage in DataMagik.

Table of Contents

1. Overview
2. Creating a Table
3. Table Schema Design
4. Managing Table Data
5. Column Types
6. Single Key vs Composite Key
7. Using Tables in Scripts
8. Data Import & Export
9. Best Practices
10. Common Use Cases

1. Overview

User-defined tables (UDTs) provide a flexible way to store structured data that can be accessed from scripts, automations, and document templates. Think of them as simple database tables you can create and manage without writing SQL.

Common Uses:

• Customer-specific configuration (templates, preferences, settings)
• Lookup mappings (product codes → descriptions, customer → ship-to addresses)
• Status tracking (order status, processing flags)
• Reference data (pricing tiers, discount rules, region codes)

2. Creating a Table

Step 1: Open User-Defined Tables

Navigate to Manufacturing → User Defined Tables in the main menu.

Step 2: Click "Create Table"

Click the Create Table button in the top-right corner.

Step 3: Configure Table Settings

Step 4: Save the Table

Click Create Table to save. The table will appear in your list and is ready to receive data.

3. Table Schema Design

Naming Conventions

• Use lowercase letters with underscores for table names
• Make names descriptive but concise
• Prefix related tables (e.g., customer_templates, customer_addresses, customer_settings)

Good examples: customer_templates, product_mappings, region_pricing, order_status

Avoid: CustomerTemplates (use lowercase), ct (too short), customer-templates (use underscores)

Key Column Design

The key column(s) uniquely identify each row. Choose keys that:

• Are stable (don't change over time)
• Are meaningful (easy to understand what they represent)
• Are available in your scripts and automations

4. Managing Table Data

Viewing Data

1. Click on a table in the list to select it
2. Click the Data button to open the data manager
3. View all entries in a searchable table format

Adding Entries

1. Click Add Entry in the data manager
2. Enter the key value(s)
3. Enter the value data (depends on value type)
4. Click Save

Editing Entries

1. Find the entry in the data manager
2. Click the Edit button (pencil icon)
3. Modify the value data
4. Click Save

Deleting Entries

1. Find the entry in the data manager
2. Click the Delete button (trash icon)
3. Confirm the deletion

Warning: Deletion is permanent. Ensure no scripts depend on the entry before deleting.

5. Column Types

Value Types

When creating a table, choose what type of data the value column will hold:

Object Type (Most Flexible)

The object type is most commonly used because it can store multiple related values:

{
  "template_id": "inv-template-001",
  "default_printer": "Shipping-Zebra",
  "notify_email": "orders@customer.com",
  "priority": "high",
  "discount_percent": 5
}

Accessing Different Types in Scripts

// String type
const status = tables.getValue("order_status", orderId);
// status = "shipped"

// Number type
const threshold = tables.getValue("inventory_thresholds", productCode);
// threshold = 100

// Boolean type
const isEnabled = tables.getValue("feature_flags", "new_checkout");
// isEnabled = true

// Object type
const settings = tables.getValue("customer_settings", customerCode);
// settings = { template_id: "...", notify_email: "...", ... }
const templateId = settings.template_id;

6. Single Key vs Composite Key

Single Key Tables

Use a single key when one identifier uniquely identifies each row.

Example: Customer Settings

// Access with single key
const settings = tables.getValue("customer_settings", "CUST001");

Composite Key Tables

Use composite keys when you need two values to uniquely identify a row.

Example: Customer Shipping Documents (by Customer + Ship-To)

// Access with composite key
const settings = tables.getValue(
  "customer_shipping_docs",
  "CUST001",
  "SHIP-A"
);

When to Use Composite Keys

• Customer + Location — Different settings per shipping location
• Product + Variant — Settings per product size/color
• Date + Code — Historical data with date partitioning
• Region + Category — Regional pricing by product category

7. Using Tables in Scripts

Basic Read Operations

function main(context) {
  // Get full entry (includes metadata)
  const entry = tables.get("customer_settings", context.customerCode);
  // entry = { key: "CUST001", value: { template_id: "..." }, created_at: "...", updated_at: "..." }
  
  // Get just the value (simpler)
  const settings = tables.getValue("customer_settings", context.customerCode);
  // settings = { template_id: "..." }
  
  // Check if exists
  if (tables.exists("blocklist", context.customerCode)) {
    return { success: false, error: "Customer is blocked" };
  }
  
  return { success: true, data: settings };
}

Write Operations

function main(context) {
  // Create or update (upsert)
  tables.set("order_status", context.orderId, {
    status: "processing",
    updated_at: new Date().toISOString(),
    updated_by: context.userId
  });
  
  // For composite keys, pass both keys
  tables.set(
    "customer_shipping_docs",
    context.customerCode,
    context.shipToCode,
    { template: "custom-bol", special_instructions: "..." }
  );
  
  return { success: true };
}

Listing & Filtering

function main(context) {
  // List all entries in a table
  const allCustomers = tables.list("customer_settings");
  // allCustomers = [{ key: "CUST001", value: {...} }, ...]
  
  // For composite key tables, filter by first key
  const customerLocations = tables.list(
    "customer_shipping_docs",
    context.customerCode
  );
  // Returns all ship-to locations for this customer
  
  return { 
    success: true, 
    data: { 
      total_customers: allCustomers.length,
      locations: customerLocations 
    } 
  };
}

8. Data Import & Export

Exporting Data

1. Open the Data Manager for your table
2. Click the Export button
3. Choose format (JSON or CSV)
4. Download the file

Importing Data

1. Open the Data Manager for your table
2. Click the Import button
3. Select your JSON or CSV file
4. Review the preview
5. Confirm the import

JSON Format for Import:

[
  {
    "key": "CUST001",
    "value": {
      "template_id": "inv-001",
      "notify_email": "orders@customer1.com"
    }
  },
  {
    "key": "CUST002",
    "value": {
      "template_id": "inv-002",
      "notify_email": "orders@customer2.com"
    }
  }
]

CSV Format for Import (Simple Values):

key,value
CUST001,inv-001
CUST002,inv-002
CUST003,inv-001

9. Best Practices

Table Design

• Keep tables focused — One table per concept (don't mix customer settings with product settings)
• Use descriptive names — customer_document_preferences is better than cust_prefs
• Document your tables — Use the description field to explain what the table is for
• Plan for growth — Use object types when you might need to add more fields later

Performance

• Keep tables reasonably sized — For very large datasets (10,000+ entries), consider alternative storage
• Use specific keys — Avoid listing entire tables when you only need one entry
• Cache when appropriate — If you read the same entry multiple times in a script, store it in a variable

Data Integrity

• Validate before writing — Check that values are in the expected format before saving
• Use consistent key formats — If keys are customer codes, always use the same format (uppercase, trimmed)
• Handle missing entries gracefully — Always check if tables.getValue() returns null

function main(context) {
  const customerCode = context.customerCode.toUpperCase().trim();
  
  const settings = tables.getValue("customer_settings", customerCode);
  
  if (!settings) {
    return {
      success: true,
      data: { template_id: "default-template" }
    };
  }
  
  return { success: true, data: settings };
}

10. Common Use Cases

Customer-Specific Document Templates

function main(context) {
  const config = tables.getValue("customer_templates", context.customerCode);
  const templateId = config?.invoice_template || "default-invoice";
  
  documents.generate(templateId, {
    customerName: context.customerName,
    orderNumber: context.orderNumber,
    items: context.items
  });
  
  return { success: true, script_message: "Document generated" };
}

Feature Flags

function main(context) {
  const flags = tables.getValue("feature_flags", context.customerCode) || {};
  
  if (flags.new_checkout_enabled) {
    // Use new checkout logic
  } else {
    // Use legacy checkout
  }
  
  return { success: true };
}

Pricing Tiers

function main(context) {
  const pricing = tables.getValue(
    "regional_pricing",
    context.region,
    context.productCategory
  );
  
  const basePrice = context.basePrice;
  const multiplier = pricing?.multiplier || 1.0;
  const finalPrice = basePrice * multiplier;
  
  return { success: true, data: { price: finalPrice } };
}

Status Tracking

function main(context) {
  tables.set("order_status", context.orderId, {
    status: "shipped",
    shipped_at: new Date().toISOString(),
    tracking_number: context.trackingNumber
  });
  
  return { success: true };
}

Blocklists / Allowlists

function main(context) {
  if (tables.exists("ip_blocklist", context.ipAddress)) {
    return { 
      success: false, 
      error: "Access denied",
      notification_level: "error"
    };
  }
  
  const isPremium = tables.exists("premium_customers", context.customerCode);
  
  return { 
    success: true, 
    data: { premium_access: isPremium }
  };
}