User-Defined Tables Guide

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

📸 Screenshot Needed: User Defined Tables main interface showing the table list with search/filter options. Navigate to Manufacturing → User Defined Tables to capture this.

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)

📸 Screenshot Needed: An example table showing customer settings with key-value pairs.

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 purple Create Table button in the top-right corner.

📸 Screenshot Needed: The Create Table modal when it first opens.

Step 3: Configure Table Settings

Fill in the table configuration:

FieldDescriptionExampleTable NameUnique identifier (lowercase with underscores)customer_templatesDescription this table is used forMaps customers to their preferred document templatesKey Type: Single Key or Composite Key, Primary Key Name: Name of the main key columncustomer_codeKey Description the key representsCustomer code from ERPValue Column Name: Name of the value columnsettingsValue Type: Type of data storedobject

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, unclear)
• customer-templates (use underscores, not hyphens)

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

📸 Screenshot Needed: The Data Manager showing a table with several entries.

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, you choose what type of data the value column will hold:

Type Description Example UsestringSimple text value, status codes, names, identifiersnumberNumeric value: Prices, quantities, thresholdsbooleanTrue/false flag Feature flags, enabled/disabledobjectJSON object with multiple fields, complex settings, nested dataarrayList of values: Multiple options, listsdocument_templateReference to a template: Customer-specific templatesprinterReference to a printer: Default printer assignments

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

customer_code (Key)settings (Value)CUST001{ template_id: "inv-001", ... }CUST002{ template_id: "inv-002", ... }CUST003{ template_id: "inv-001", ... }

// 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)

customer_code (Key 1)ship_to_code (Key 2)document_settings (Value)CUST001SHIP-A{ template: "bol-001" }CUST001SHIP-B{ template: "bol-002" }CUST002SHIP-A{ template: "bol-001" }

// 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

📸 Screenshot Needed: The Create Table modal showing the composite key option enabled with both key fields visible.

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

📸 Screenshot Needed: The Export button and format selection dropdown.

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) {
    // Use defaults when no entry exists
    return {
      success: true,
      data: { template_id: "default-template" }
    };
  }
  
  return { success: true, data: settings };
}

10. Common Use Cases

Customer-Specific Document Templates

Map each customer to their preferred invoice/packing slip template:

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

Control feature rollout per customer:

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

Store regional or customer-specific pricing:

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

Track order or process status:

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

// Later, check status
function checkStatus(context) {
  const status = tables.getValue("order_status", context.orderId);
  return { 
    success: true, 
    data: { 
      status: status?.status || "unknown",
      shipped_at: status?.shipped_at
    } 
  };
}

Blocklists / Allowlists

Control access or filtering:

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

Screenshots Summary

Please capture the following screenshots to complete this documentation:

1. User Defined Tables main interface - The table list with search/filter options
2. Example table with data - A table showing customer settings with key-value pairs
3. Create Table modal - The modal when first opened
4. Data Manager - Showing a table with several entries
5. Composite key configuration - The Create Table modal with composite key enabled
6. Export options - The Export button and format selection

For more information on using tables in scripts, see the Script Engine Guide.