Output Data Pipelines

A Data Pipeline is a set of data, for example, a transaction’s header details or its items. In the Form Designer, the pipelines are listed in the Data Tree, and you add their fields to a template (see About Data Pipelines).

Transactions#

Transaction Header and Parties#

The following Data Pipelines can be used on transaction Output Templates.

• trans_header: The transaction’s header (one row):
  • Number: trans_no, trans_ver_no and trans_no_version (the number and version, for example, 1001/1)
  • Dates: date_trans, date_expires, date_due and date_period
  • Details: description, order_no, note, category, status, status_note, credit_term and credit_term_code
  • Customer or supplier: company_name, company_knownas and company_vat_no
  • People: contact_name, rep_name and assign_to_name, each with a …_knownas field
  • Addresses: address (delivery) and address_billing (Bill To), with their address codes
  • Entities: entity_registered (the registered entity’s code) and entity (the selected entity’s code)
  • Totals in the home currency: total (excluding VAT), vat and total_vat (including VAT)
  • Totals in the transaction’s currency: total_cur, vat_cur and total_vat_cur, with currency and the exchange rates
  • Totals in words: total_text, total_vat_text, total_cur_text and total_vat_cur_text
  • Outstanding amount: total_out and total_out_cur
  • Costs and profit: total_cost, gp_amt and gp_percent
  • Deposit: deposit_percent and deposit_amt
  • Withholding tax: wht_total, wht_total_1 to wht_total_6 (the total of each WHT section) and wht_total_text (the total in Thai words), blank without WHT
  • Stock locations: location_from and location_to
  • The transaction it was created from: created_from (Transaction Type code and number) and created_from_reference (its description)
  • Other: lang_name (the transaction’s language), userupdated and note_richtext (the note formatted for a Rich Text object)
• trans_customer: The customer on the transaction: name, known as, code, ID number, VAT number, phone numbers, email, website, credit limit, status, currency, billing method, groups, accounts contact and note. The addresses are in physical_address and postal_address (on several lines) and physical_address_flat and postal_address_flat (on one line). Returns no data on supplier transactions.
• trans_supplier: The supplier on the transaction, with the same fields as trans_customer. Returns no data on customer transactions.
• trans_contact: The contact person on the transaction: title, names, phone numbers, email and addresses.
• trans_rep: The rep on the transaction: title, names, phone numbers and email.
• trans_assign: The person the transaction is assigned to: title, names, phone numbers and email.
• trans_entity: The registered entity of the transaction: description, code, template_masthead_rtf (the masthead), template_image (the logo) and bank_detail.
• trans_entity_selected: The entity selected on the transaction (for example, a branch), with the same fields as trans_entity.
• trans_asset: The asset on the transaction: code, description, type (Vehicles, Equipment or Other), status, entity, contact, note and meter_reading (the latest meter reading).
• trans_allocations: All the transactions allocated to the transaction: Transaction Type, number, document number, reference, date, total, allocated amount and outstanding amount.

Transaction Items#

The following Data Pipelines can be used on transaction Output Templates.

• trans_items: All the items on the transaction, in order (one row per entry). Other quantities of an estimate are not included (they are in est_qty_inactive):
  • Item: code, description, note, unit, item_category, group_by, price_reference and price_barcode
  • Entry type: trans_item_type (CTM, ITM, EST, PAY or WHT) and resolve (for example, Purchase, Produce, Reserve or Issue)
  • Quantity and price: qty, unit_price, fixed_price, mu_per, disc_per and disc_amt
  • Totals in the home currency: total, vat, total_vat and total_unit (the total per unit)
  • Totals in the transaction’s currency: total_cur, vat_cur, total_vat_cur and total_unit_cur
  • Costs and profit: unit_cost, total_cost, gp_amt and gp_percent
  • Accounts: dr_account and cr_account
  • Stock: location, location_code, qty_stock, qty_count, tracking and date_expires
  • Booking: date_start, date_end, resource_description and resource_code (when the Transaction Type uses a calendar)
  • Withholding tax: wht_per, wht_amt and wht_section_text
  • Other: status, from_item (the Transaction Type code and number of the entry it was copied from) and note_richtext (the note formatted for a Rich Text object)
• trans_items_group: One row per Group By value of the items on the transaction, with the quantity and totals (home and transaction currency) of each group. Items without a group are added up under “Other”, which is listed last.
• trans_items_from: All the items on the transaction this transaction was created from, with the same fields as trans_items.
• trans_item_notes: The notes of the item linked to the current entry, in the transaction’s language. Each note has a text field, a caption field and a field formatted for a Rich Text object, for example, description_text, description_caption and description_richtext. The notes are applications, benefits, cautions, description, features, maintenance, note, options, performance, safety, specification, testimonials and warranty.
• trans_item_attachments: The attachments of the item linked to the current entry, in the transaction’s language. Each attachment has a link field and a caption field, for example, image_url and image_caption. The attachments are assembly, detail, elevation, exploded, image, installation, isometric, layout, orthographic, plan, schematic, section and specification.
• trans_item_artwork: The artwork of the current entry: description, url and status (Pending, Active or Expired). Returns data only when artwork is used.
• trans_artwork: All the artwork of the entries in the transaction’s flow, in order, with the same fields as trans_item_artwork. Returns data only when artwork is used.

Estimates#

The following Data Pipelines can be used on transaction Output Templates. They are linked to trans_items and return data only for entries with an estimate.

• est_qty: All the quantities of the current entry’s estimate, smallest first, with the price, totals, costs and profit of each. default_id is 1 for the quantity selected on the entry.
• est_qty_inactive: The other quantities of the current entry’s estimate (not the selected one), with the same fields as est_qty.
• est_description: The description rows of the current entry’s estimate, in order: caption and note.
• est_materials: All the materials of the current entry’s estimate, in order:
  • Material: description, item_code, item_description and unit
  • Part and group: est_part, est_item_desc and group
  • Quantities: qty, qty_total, qty_type and waste_percent
  • Costs: unit_cost, total_cost and supplier
  • Stock: location_code, status, qty_issued and qty_outstanding
• est_routing: All the tasks and outwork of the current entry’s estimate, in order:
  • Task: description, unit, status and provider (the supplier or Cost Centre)
  • Part and group: est_part, est_item_desc and group
  • Time: rate_hr, setup_hrs, speed_qty_per_hr and total_hrs
  • Quantities and costs: qty, qty_type, unit_cost, min_cost and total_cost
• est_items: The parts of the current entry’s estimate, by group and part: description, unit, quantity, note, group and total_cost (the materials and routing of the part).
• est_item_materials: The materials of the current part in est_items, with the same fields as est_materials (without the stock fields).
• est_item_routing: The tasks and outwork of the current part in est_items, with the same fields as est_routing.
• est_item_attributes: The attributes of the estimate, one field per attribute.

Proposals#

The following Data Pipelines can be used on transaction Output Templates. They return data only when the transaction has a proposal.

• proposal_root_item: The main item of the proposal: code, description, quantity and unit price (unit_price_exc, unit_price_vat and unit_price_inc), with the item’s notes and attachments.
• proposal_items: All the options in the proposal, by group: group_description, code, description, quantity, unit prices, notes and attachments. selected shows “Selected” for the options the customer chose.
• proposal_selected_items: Only the options the customer chose, with the same fields as proposal_items.
• proposal_root_item_attributes: The attributes of the main item that have a value: group_by (the attribute group), description and value.
• proposal_items_attributes: The attributes of the current option in proposal_items, with the same fields.
• proposal_selected_items_attributes: The attributes of the current option in proposal_selected_items, with the same fields.

The prices are the item’s sell prices in the transaction’s currency. The notes and attachments are in the same fields as in trans_item_notes and trans_item_attachments; the caption fields end in _note_caption and _attach_caption. The captions of the attributes are translated into the transaction’s language.

Attributes#

The following Data Pipelines can be used on transaction Output Templates. They return data only when attributes are used.

• trans_attributes: The transaction’s attributes.
• trans_company_attributes: The attributes of the customer or supplier on the transaction.
• trans_project_attributes: The attributes of the service linked to the transaction.
• trans_asset_attributes: The attributes of the asset on the transaction.

Each attribute pipeline has one row with one field per attribute, named after the attribute’s key. The fields depend on the attributes of the record you output. When the record has no attributes, the pipeline has one field, no_attributes.

Accounting#

Customer Age Analysis#

The following Data Pipelines can be used on Customer Age Analysis Output Templates, for example, statements.

• cus_ageing_rec: The selected customer’s age analysis:
  • Customer: company, code, status, currency, credit_limit, contact_acc (the accounts contact), contact_acc_email and contact_rep
  • Ageing: current_amount, days_30_60, days_60_90, days_90_120, days_120_150, days_150_180 and days_180_plus
  • Totals: unallocated, total_outstanding, wip, total_wip and overdrawn
• cus_ageing_customer: The customer’s details, with the same fields as trans_customer (addresses on several lines only).
• cus_ageing_contact: The customer’s accounts contact: title, names, phone numbers, email and addresses.
• cus_ageing_detailed_ledger: All the customer’s transactions in the selected period:
  • Transaction: trans_date, trans_type, trans_no, reference, currency and entity
  • Amounts: debit, credit and running_balance
  • Balances: opening_balance, movement and closing_balance
  • Allocation: total_allocated and total_outstanding
• cus_ageing_outstanding: The customer’s transactions that are not fully allocated: date, number, reference, currency, entity, total_allocated and total_outstanding.
• cus_ageing_filters: The filters selected on the screen: date_start and date_end, with entity and currency_local (Yes or No) when those filters are shown.
• entity: The registered entity selected in the filter (or the only one): description, code, masthead, logo and bank details.

Customer Detailed Ledger Output Templates (output from the Customer Detailed Ledger) use cus_ageing_detailed_ledger and entity, with the customer and filters selected on the Detailed Ledger.

Supplier Age Analysis#

The following Data Pipelines can be used on Supplier Age Analysis Output Templates, for example, remittance advices. They have the same fields as the Customer Age Analysis pipelines.

• sup_ageing_rec: The selected supplier’s age analysis.
• sup_ageing_supplier: The supplier’s details.
• sup_ageing_contact: The supplier’s accounts contact.
• sup_ageing_detailed_ledger: All the supplier’s transactions in the selected period.
• sup_ageing_outstanding: The supplier’s transactions that are not fully allocated.
• sup_ageing_filters: The filters selected on the screen.
• entity: The registered entity selected in the filter (or the only one).

Supplier Detailed Ledger Output Templates (output from the Supplier Detailed Ledger) use sup_ageing_detailed_ledger and entity.

Tax Reconciliation#

The following Data Pipelines can be used on VAT and WHT Tax Reconciliation Output Templates.

• tax_recon_header: The selected reconciliation: date_start, date_end, total_input, total_output, balance, status, status_note and username.
• tax_recon_items_all: All the entries in the reconciliation, by Transaction Type and date:
  • Transaction: trans_type, trans_no, doc_no, date_trans, reference and branch_code
  • Entry: description, account, vat_type, dr_account and cr_account
  • Amounts: debit, credit, total, vat and total_vat
• tax_recon_items_input: Only the input tax entries, with the same fields.
• tax_recon_items_output: Only the output tax entries, with the same fields.
• tax_recon_sum_transactions: The debit and credit totals per Transaction Type: description, debit and credit.
• tax_recon_sum_accounts: The debit and credit totals per account, with the same fields.
• tax_recon_entity: The registered entity of the reconciliation: description, code, masthead, logo and bank details.

Bank Reconciliation#

The following Data Pipelines can be used on Bank Reconciliation Output Templates.

• bank_recon_header: The selected reconciliation: account_code, account_lookup (the bank account), date_trans_start, date_trans_end, balance_bank, balance_ledger, variance, status, note and username.
• bank_recon_items: All the bank statement lines, by date: date_trans, description, amount, status, and the matched transaction’s trans_no, company, reference, trans_total and variance.
• bank_recon_entity: The first registered entity: description, code, masthead, logo and bank details.

Budgets vs Actuals#

The following Data Pipelines can be used on Budgets vs Actuals Output Templates. They use the Month To, entity and statement selected on the screen.

• budget_header: The selected budget:
  • Budget: description, budget_type (Accounting or Sales), version (Original, Revised or Forecast), status (Draft, Approved or Locked) and note
  • Period: date_start, date_end and period (for example, Mar 2026 - Aug 2026)
  • Month captions: month_1 to month_12 (for example, Mar 2026), for monthly templates
  • Filters: entity (All without an entity filter) and statement
• budget_items: The rows of the selected statement, in the order of the statement (Accounting budgets only):
  • Account: account_code, account_name, account_name_display (indented by level), level_id and report_id
  • Period: act_period (actual), bud_period (budget), var_amt, var_pct and fav_id (1 = favourable)
  • Year: bud_year, forecast and remaining
  • Months: act_1 to act_12 and bud_1 to bud_12
• budgets_accounting: The Statement of Profit or Loss rows, whatever the statement selected, with the same fields as budget_items (Accounting budgets only).
• budgets_sales: One row per budget line (Sales budgets only): tier1_description and tier2_description (for example, the customer or item), the same amount fields as budget_items, and qty_act_period and qty_bud_period. Sales that match no line are listed as “Unbudgeted / Other”.
• budget_entity: The entity selected in the filter, else the budget’s entity, else the first registered entity: description, code, masthead, logo and bank details.

Financial Statements#

The following Data Pipelines can be used on Financial Statements Output Templates. They use the entity and dates selected on the screen.

• fs_profit_loss_1: The top rows of the Statement of Profit or Loss.
• fs_profit_loss_2 to fs_profit_loss_5: The rows under the current row of the level above, for example, fs_profit_loss_2 lists the rows under the current fs_profit_loss_1 row.
• fs_financial_position_1 to fs_financial_position_5: The rows of the Statement of Financial Position, by level, in the same way.
• fs_trial_balance: One row per account on the Trial Balance: account_code, account_name, account_lookup, account_type, parent, debit_balance, credit_balance and balance.

The statement pipelines have these fields:

• Row: account_code, account_name, account_lookup, account_name_display (indented by level), level_id and report_id (0 = account, 3 = section, 4 = group, 5 = summary)
• Amounts: debit, credit and net_amount
• Months (with Fin Year (Months)): month_1 to month_12

Rows with a zero amount are left out unless Show All is checked (the summary rows are always included).

Cost of Sales#

The following Data Pipelines can be used on Cost of Sales Output Templates. They use the Transaction Type and filters selected on the screen. The amounts are in the local currency, excluding VAT.

• cos_trans: One row per transaction (the Transactions view):
  • Transaction: trans_no, date_trans, status, company, reference, rep and entity
  • Costs: cost_purchase, cost_issued, cost_returned, cost_timekeeping and cost_total
  • Profit: total, profit, gp_percent and mu_percent
• cos_items: One row per entry, or per estimate row of a production estimate entry (the Items view): the transaction fields, code, description, unit, qty, qty_actual, qty_variance, est_status, and the same cost and profit fields as cos_trans.
• cos_costs_all: All the cost entries of the current cos_items row: trans_no, date_trans, status, company, reference, resource, code, description, unit, qty, unit_cost and cost.
• cos_costs_purchases: Only the purchase entries, with the same fields.
• cos_costs_issued: Only the stock issued entries, with the same fields.
• cos_costs_returned: Only the stock returned entries, with the same fields.
• cos_costs_services: Only the timekeeping, with the same fields (qty is the hours).
• cos_entity: The entity selected in the filter, else the first registered entity: description, code, masthead, logo and bank details.

Point of Sale#

The following Data Pipelines can be used on Point of Sale (PoS) till session Output Templates.

• pos_session: The selected till session:
  • Till: till_no and status
  • Opening: date_opened, user_opened, counted_opening_float, expected_opening_float and variance_opening
  • Closing: date_closed, user_closed, counted_closing_balance, expected_closing_balance and variance_closing
  • Notes: notes and status_note
• pos_entity: The till’s entity: description, code, masthead, logo and bank details.
• pos_cash_opening: The cash counted when the session was opened, largest denomination first: denomination_value, quantity and subtotal.
• pos_cash_closing: The cash counted when the session was closed, with the same fields.
• pos_bank: The session’s bank transactions: trans_type, trans_no, description, amount and user_updated.
• pos_transacions: All the session’s transactions, by Transaction Type, with the same fields. The pipeline name is spelt this way in BOS.

Service Manager#

The following Data Pipelines can be used on service Output Templates.

• project_rec: The selected service:
  • Number: project_no and project_ver_no
  • Details: reference, class_description (the Service Type), category, status, status_note, severity_id and response_hrs (response hours)
  • Dates: date_due, date_remind, date_start_project and date_end_project
  • People: company_name, contact_name, assign_name and rep_name, each with a …_knownas field
  • Sales: sales_lead_source, sales_referred_by, sales_projected_one_off, sales_projected_annual and probability_percent
  • Other: entity, meter_reading (the latest meter reading) and userupdated
• project_customer: The customer on the service, with the same fields as trans_customer (addresses on several lines only).
• project_supplier: The supplier on the service, with the same fields.
• project_contact: The contact person on the service: title, names, phone numbers, email and addresses.
• project_entity: The service’s entity: description, code, masthead, logo and bank details.
• project_asset: The asset on the service: code, description, type (Vehicles or Equipment), status, entity, contact and note.
• project_notes: The service’s notes, newest first: reference, note and activity.
• project_tasks_level1: The service’s top-level tasks:
  • Task: description, note, status, tags, resource and predecessor
  • Dates: date_required, date_start and date_end
  • Time: duration_days and duration_hrs
• project_tasks_level2: The tasks under the current project_tasks_level1 task, with the same main fields.
• project_tasks_level3: The tasks under the current project_tasks_level2 task, with the same main fields.
• project_attributes: The service’s attributes.
• project_company_attributes: The attributes of the customer or supplier on the service.
• project_asset_attributes: The attributes of the asset on the service.

The attribute pipelines work in the same way as the transaction attribute pipelines (see Attributes above).

Custom Reports#

The following Data Pipelines can be used on Custom Reports Output Templates. Their fields are the columns of the report’s SQL, so they differ for each report.

• report_rec: The record selected on the report.
• report_all: All the records on the report.