Getting Started

Prerequisites

Before you begin, confirm the following are in place:

1. JWT Bearer token with the TenantAdmin role. All Multi-Query endpoints require a valid JWT issued by the BizFirst identity service. The token must include the TenantAdmin role claim and a valid TenantID claim. Contact your platform administrator if you do not have one.
2. SQL Server database with at least one table. The Multi-Query engine connects to the BizFirst platform database via SqlServerDeriveEngine. Your target tables must exist and be accessible to the platform service account.
3. Access to the dbo.Shared_Configurations table. Templates are stored as JSON values in this table. You need INSERT permission on the table, or access to an admin UI that manages configuration records.

Step 1 — Write Your Template

Start with a minimal two-level template. The root query fetches Departments; the child query fetches the Employees belonging to each department, referencing the parent's primary key via @parent.DepartmentID.

{
  "name": "Departments with Employees",
  "sql": "SELECT DepartmentID, DepartmentName, ManagerID, CostCentreCode
          FROM   Departments
          WHERE  TenantID = @TenantID
            AND  Deleted  = 0
            AND  Archived = 0
          ORDER BY DepartmentName",
  "recordType": "Department",
  "isTemplate": true,
  "parameters": [],
  "options": {
    "outputFormat": "json",
    "outputFormatTemplate": "fragment",
    "maxDepth": 5,
    "cacheSeconds": 60
  },
  "children": [
    {
      "collectionName": "employees",
      "sql": "SELECT EmployeeID, FirstName, LastName, JobTitle,
                     EmploymentType, HireDate, IsActive
              FROM   Employees
              WHERE  DepartmentID = @parent.DepartmentID
                AND  TenantID     = @TenantID
                AND  Deleted      = 0
              ORDER BY LastName, FirstName",
      "recordType": "Employee",
      "isTemplate": false,
      "children": []
    }
  ]
}

Save this JSON to a local file (e.g., MY_FIRST_TEMPLATE.json) for the next step.

Step 2 — Store the Template

Insert the template JSON into dbo.Shared_Configurations. The Code column becomes the catalogue code used in all API calls and expression directives.

INSERT INTO dbo.Shared_Configurations
  (Code,                   [Value],
   TenantID, AppDomainID,  DataDomainID, DataSegmentID,
   ClientAccountID,        SourceAppID,
   IsActive, Deleted,      Archived,
   CreatedBy, CreatedOn,   LastModifiedBy, LastModifiedOn)
VALUES
  ('MY_FIRST_TEMPLATE',
   -- Paste your QueryTemplate JSON here as a string
   '{
     "name": "Departments with Employees",
     "sql":  "SELECT DepartmentID, ...",
     "recordType": "Department",
     "isTemplate": true,
     "parameters": [],
     "options": { "outputFormat": "json", "cacheSeconds": 60 },
     "children": [...]
   }',
   42,   -- TenantID
   1,    -- AppDomainID
   1,    -- DataDomainID
   1,    -- DataSegmentID
   1001, -- ClientAccountID
   'BizFirstPayroll',  -- SourceAppID
   1,    -- IsActive
   0,    -- Deleted
   0,    -- Archived
   1,    -- CreatedBy (admin user ID)
   GETDATE(),
   1,
   GETDATE());

Adjust the identity columns (TenantID, AppDomainID, ClientAccountID, etc.) to match your environment. The Code value (MY_FIRST_TEMPLATE) is what you pass to the API.

Step 3 — Execute

Call the JSON endpoint with your Bearer token. The engine looks up the template by code, injects TenantID from the JWT, and returns the nested result.

Request

GET /api/v1/expressions/multiquery/MY_FIRST_TEMPLATE.json
Host: api.bizfirstai.com
Authorization: Bearer eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9...

Expected Response Shape

[
  {
    "DepartmentID": 1,
    "DepartmentName": "Engineering",
    "ManagerID": 44,
    "CostCentreCode": "ENG-001",
    "employees": [
      {
        "EmployeeID": 101,
        "FirstName": "Alice",
        "LastName": "Nguyen",
        "JobTitle": "Senior Engineer",
        "EmploymentType": "FullTime",
        "HireDate": "2022-03-14",
        "IsActive": true
      },
      {
        "EmployeeID": 108,
        "FirstName": "James",
        "LastName": "Osei",
        "JobTitle": "Engineering Lead",
        "EmploymentType": "FullTime",
        "HireDate": "2019-07-01",
        "IsActive": true
      }
    ]
  },
  {
    "DepartmentID": 2,
    "DepartmentName": "Finance",
    "ManagerID": 12,
    "CostCentreCode": "FIN-001",
    "employees": [
      {
        "EmployeeID": 204,
        "FirstName": "Priya",
        "LastName": "Sharma",
        "JobTitle": "Payroll Analyst",
        "EmploymentType": "FullTime",
        "HireDate": "2021-01-18",
        "IsActive": true
      }
    ]
  }
]

Each root record (Department) contains its child collection (employees) nested inline. The engine executes one query per level, per parent row, and assembles the tree in memory before responding.

Next Steps