Import JSON Syntax

Reference for the universal import payload structure

Quick Reference

At a glance — which fields are needed for each action:

Required   Optional   — Not applicable

Top-Level Structure

The import payload is a JSON object where each key is a datatype (table name, pluralized) and the value is an array of import items.

{
  "DATATYPE": [
    {
      "action": "create" | "update" | "update_or_create" | "delete" | "create_ddf",
      "id": int | object,
      "data": { "field": "value", ... },
      "template": int,
      "search_data": { ... },
      "related_data": { "DATATYPE": [ ... ] },
      "foreign_key": { ... }
    }
  ]
}

You can import multiple datatypes in a single request:

{
  "customers": [ { "data": { "name": "Acme Corp" } } ],
  "contacts": [ { "data": { "lastname": "Doe", "customer_id": 42 } } ]
}

Actions

Each import item can specify an action. If omitted, defaults to "create".

create Default

Creates a new record in the target table. Timestamps u_created and u_modified are set automatically.

Examples

Simple create (action omitted, defaults to "create"):

{
  "notes": [
    {
      "data": {
        "category_id": "116",
        "customer_id": "222",
        "date": "1771410600",
        "name": "### Meeting Notes\n\nDuration: 8 min",
        "visible_to": "everyone",
        "manual": "1"
      }
    }
  ]
}

Create with nested related_data:

{
  "orders": [
    {
      "action": "create",
      "data": {
        "name": "Webshop Order: Premium Package"
      },
      "related_data": {
        "samples": [
          {
            "action": "create",
            "data": {
              "template_id": 389,
              "description": "Webshop: Premium Package"
            }
          }
        ]
      }
    }
  ]
}

update

Updates existing record(s) matched by id. Only changed fields are written — if no actual changes are detected, the update is skipped entirely (no log entry produced).

Examples

Update by primary key:

{
  "orderitems": [
    {
      "action": "update",
      "id": 663,
      "data": { "price": "108.19" }
    },
    {
      "action": "update",
      "id": 664,
      "data": { "price": "108.19" }
    }
  ]
}

Update by WHERE conditions (object id):

{
  "customers": [
    {
      "action": "update",
      "id": { "DDF_custom_nr": "CUST-001" },
      "data": {
        "name": "Updated Company Name",
        "status": "1"
      }
    }
  ]
}

Update by WHERE conditions (object id) are also combinable with multiple conditions. Those will always be executed as AND. Here for example it will try to update all customers with matching DDF_custom_nr, status and template_id values.

{
  "customers": [
    {
      "action": "update",
      "id": { "DDF_custom_nr": "CUST-001", "status": 2, "template_id": 4 },
      "data": {
        "name": "Updated Company Name",
        "status": "1"
      }
    }
  ]
}

update_or_create

Attempts to find a record matching id. If found, performs an update. If not found, performs a create instead. Requires both create and update permissions for the datatype.

Example

{
  "datakeys": [
    {
      "action": "update_or_create",
      "id": { "key": "voucher-001" },
      "data": {
        "key": "voucher-001",
        "type": "voucher",
        "value": "50.00",
        "description": "Gift voucher"
      }
    }
  ]
}

delete

Deletes record(s) matched by id. A backup of the deleted record is stored in the deletes table before removal. No data field is needed.

Examples

Delete by primary key:

{
  "orders": [
    {
      "action": "delete",
      "id": 2
    }
  ]
}

Delete by WHERE conditions:

{
  "orders": [
    {
      "action": "delete",
      "id": { "name": "Webshop Order: Premium Package" }
    }
  ]
}

create_ddf Schema

Adds a new Dynamic Data Field (DDF) column to a database table and optionally creates a corresponding templatefield record. This action requires the import interface to use the universal_templates datatype.

field_data — Column Definition

Defines the SQL column to add via ALTER TABLE. All values are validated against strict character rules to prevent SQL injection.

templatefield_data — Templatefield Record

If provided, a templatefield record is created for the new column. The template is resolved automatically by looking up a template named {singular_datatype}fields (e.g. samplefields for the samples table, equipmentfields for equipment).

Duplicate Handling

If the column already exists on the table, the operation is silently skipped — no error is thrown. The response reports it as ddfs_ignored instead of ddfs_created.

Permission Requirements

The import interface must use datatype: "universal_templates". This grants the create_ddf permission automatically. Regular universal import interfaces with per-datatype permissions cannot use create_ddf.

Examples

Basic DDF creation with a templatefield:

{
  "samples": [
    {
      "action": "create_ddf",
      "field_data": {
        "name": "DDF_Test_Field",
        "type": "VARCHAR",
        "length": 255
      },
      "templatefield_data": {
        "name": "Test Field",
        "searchable": 0
      }
    }
  ]
}

Create a column without a templatefield:

{
  "equipment": [
    {
      "action": "create_ddf",
      "field_data": {
        "name": "DDF_Serial_Number",
        "type": "INT"
      }
    }
  ]
}

Full templatefield_data with all options:

{
  "samples": [
    {
      "action": "create_ddf",
      "field_data": {
        "name": "DDF_Full_Example",
        "type": "VARCHAR",
        "length": 255
      },
      "templatefield_data": {
        "name": "Full Example Field",
        "order": 14,
        "visible_to": "_e93_",
        "editable_by": "_e93_",
        "attributes": {
          "required": true,
          "readonly": false,
          "hidden": false
        },
        "show": 0,
        "searchable": 0,
        "quicksearch": 0,
        "unique": 0,
        "editable_foreach_sample": 1,
        "fieldtype": "text",
        "note": "Help text for this field",
        "color": "#ff0000",
        "translatable": 0,
        "json_field": 0,
        "translations": {
          "en": { "name": "Full Example Field EN" }
        }
      }
    }
  ]
}

Response Format

The summary reports DDF operations per datatype:

{
  "summary": {
    "samples": {
      "ddfs_created": 1,
      "ddfs_ignored": 0
    }
  },
  "imports": {
    "samples": {
      "ddfs_created": ["DDF_Test_Field"],
      "ddfs_ignored": []
    }
  }
}

Field Reference

Complete reference of all fields available on each import item.

Features

Templates

Set "template": <template_id> to load default field values from a saved Template record. The template's field values serve as a base — any fields in your data object will override the template values.

If the template has a datatype_status set, it will be applied as the record's initial status (unless overridden in data).

{
  "customers": [
    {
      "template": 15,
      "data": {
        "name": "New Customer",
        // Template provides defaults for country_code, language, etc.
        // This "language" value overrides the template's default:
        "language": "en"
      }
    }
  ]
}

search_data

Resolve foreign keys dynamically by searching a related table instead of providing a numeric ID directly. Each key in search_data must be a foreign key field ending with _id.

The table name is derived by pluralizing the key minus _id (e.g., category_id → searches categories table). The resolved id is set on the data object automatically.

{
  "customers": [
    {
      "data": {
        "name": "Sub Company"
      },
      "search_data": {
        // Finds a customer where DDF_custom_nr = "parent_123"
        // and sets customer_id on the data object
        "customer_id": { "DDF_custom_nr": "parent_123" },
        // Finds a category where name = "Lab Notes"
        "category_id": { "name": "Lab Notes" }
      }
    }
  ]
}

related_data

Nest child imports inside a parent record. The children automatically receive the parent's ID as their foreign key — you don't need to set it manually.

The foreign key column is derived as {singular_parent_datatype}_id. For example, children nested under assets will have asset_id set automatically.

related_data follows the same structure as the top-level payload and can be nested recursively:

{
  "assets": [
    {
      "data": { "name": "Main Asset" },
      "related_data": {
        // Each subasset gets asset_id = <new asset ID> automatically
        "subassets": [
          { "data": { "name": "Sub A" } },
          { "data": { "name": "Sub B" } }
        ]
      }
    }
  ]
}

related_data also works with update actions — the matched record's ID is used as the parent key:

{
  "orders": [
    {
      "action": "update",
      "id": 42,
      "data": { "name": "Updated Order" },
      "related_data": {
        // New samples linked to the updated order
        "samples": [
          { "data": { "description": "New sample" } }
        ]
      }
    }
  ]
}

Status Handling

Most datatypes have a set of valid statustypes (status keys). The import system validates and auto-assigns statuses:

// Status key must match an existing statustype for the datatype
{
  "customers": [
    {
      "data": {
        "name": "New Customer",
        "status": "0"
      }
    }
  ]
}

Number Patterns

For certain datatypes, the system can auto-generate formatted numbers based on configured patterns. This is skipped if the number field already has a value in data.

Pattern placeholders: %y (2-digit year), ##### (auto-incrementing counter), plus arbitrary literal text. A date offset can be configured per-datatype via cnf_{datatype}_number_pattern_date_offset.

// With pattern "K:%y-#####I", the nr field is auto-generated as e.g. "K:26-00001I"
// To provide your own number instead, just include it in data:
{
  "customers": [
    {
      "data": {
        "name": "Custom Number Customer",
        "nr": "CUSTOM-001"
      }
    }
  ]
}

Special Behaviors

Parameter Value Imports

When importing parameters, setting any of the value fields value0 through value9 automatically populates related metadata fields:

• value{N}_imported — set to 1
• value{N}_modified — set to the current Unix timestamp
• value{N}_modified_by — set to the current employee ID

You do not need to set these metadata fields manually — they are populated automatically whenever a value field is present in data.

Array Values

If any value in data is an array or object, it is automatically JSON-encoded before being stored in the database.

No-Op Updates Skipped

If an update or update_or_create action results in no actual field changes (all values match the existing record), the update is skipped entirely — no database write and no log entry are produced.

Validation Rules

The following rules are enforced for every import item. Violations result in a 400 error with a descriptive message.

• id is forbidden inside data — The primary key must not be in the data object. For update and update_or_create, put it in the top-level id field instead.

• u_created and u_deleted are forbidden in data — These timestamp fields are managed automatically and cannot be set manually.

• id is required for update, update_or_create, and delete — Must be a non-empty integer or a non-empty object. null, empty string, or empty array will be rejected.

• data is required for create, update, and update_or_create — Must be a non-empty object. Missing or non-array values are rejected.

• Invalid status values are rejected — If a status is provided, it must match a valid statustype key for the target datatype.

• Unknown columns cause an error — If a field in data doesn't exist as a column in the target table, the import fails with a descriptive error.

• search_data keys must end with _id — And the referenced table must contain a matching record. Throws an error if not found.

• template must be a numeric ID — Non-numeric values or non-existent template IDs are rejected.

• field_data is required for create_ddf — Must be an array/object. If templatefield_data is provided, it must also be an array/object.

• DDF names must start with DDF_ — And contain only letters, numbers, and underscores. Names like DDF_Test; DROP TABLE samples;-- are rejected.

• DDF type, length, and default are validated — type allows only alphanumeric characters and underscores. length allows only digits and commas. default allows only alphanumeric characters and underscores.

• create_ddf requires universal_templates import interface — Regular import interfaces with per-datatype permissions cannot use create_ddf.

Forbidden Datatypes

The following datatypes cannot be used as import targets. Attempting to import into them will return an error.

configs · logs · pages · testruns · imports · statsusages · ai_prompts · ai_prompt_sessions · deletes · statusupdates