Cin7 Core GrowthPath Analytics Tables Documentation

This page documents all the analytics tables and joins (the schema) available in the GrowthPath Cin7 Core Analytics connector. This page can be fed into an LLM as context to help generate SQL queries for analysis of Cin7 Sales, Purchases and Inventory data. This schema is generated dynamically based on the models used to generate the data in the Zoho Analytics feed and the PostgreSQL feed.

Jump to Example SQL Queries

MCP Server Available: For users with LLM tools that support Model Context Protocol (MCP), a dedicated MCP server is available at /mcp_analytics/. This allows your LLM to directly query the analytics database schema to design queries. Zoho Analytics has its own MCP server; the GrowthPath server has more knowledge about Dear (Cin7) specifics and more semantic knowledge. However, it is not designed to execute queries directly, just to help you build them (in SQL).

Table of Contents

avg_ex_rate

Monthly average exchange rates from the ECB.

Model: Avg_ex_rate

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
year_month_currency CharField Nullable Optional
in_reporting_currency DecimalField Historical monthly average exchange rate from ECB, multiple the from-currency by this to get to reporting currency Nullable Optional
value_date DateTimeField Always 15th of the month Nullable Optional
from_currency CharField Nullable Optional
to_currency CharField Nullable Optional

dear_customer

dear customer table

Model: Dear_customer

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
document_modified DateTimeField UTC timestamp when customer record was last modified in Dear Nullable Optional
customer_guid CharField Unique GUID identifier for the customer in Dear/Cin7 Core Unique Nullable
customer_name TextField Customer company or individual name as displayed in Dear Nullable Optional
status CharField Customer status: Active or Deprecated Nullable Optional
tags CharField Comma-separated tags assigned to the customer for filtering Nullable Optional
payment_term CharField Customer default payment term Nullable Optional
customer_sales_rep CharField Customer default sales rep Nullable Optional
price_tier CharField Customer default price tier Nullable Optional
location CharField Customer default location Nullable Optional
discount CharField Customer default discount Nullable Optional
comments CharField Customer comments (2000 chars) Nullable Optional
carrier CharField Customer default carrier Nullable Optional
credit_limit IntegerField value of 0 means no credit limit Nullable Optional
is_on_credit_hold BooleanField True if customer is on credit hold
tax_rule CharField Default tax rule applied to sales for this customer Nullable Optional
tax_number CharField Customer's tax registration number (ABN, VAT, GST, etc.) Nullable Optional
source_dear ForeignKey
FK to dear_metadata
attribute_set CharField Name of the custom attribute template applied to this customer Nullable Optional
cust_attribute1 CharField Custom customer attribute 1 value; field name configured in Dear Settings Nullable Optional
cust_attribute2 CharField Custom customer attribute 2 value; field name configured in Dear Settings Nullable Optional
cust_attribute3 CharField Custom customer attribute 3 value; field name configured in Dear Settings Nullable Optional
cust_attribute4 CharField Custom customer attribute 4 value; field name configured in Dear Settings Nullable Optional
cust_attribute5 CharField Custom customer attribute 5 value; field name configured in Dear Settings Nullable Optional
cust_attribute6 CharField Custom customer attribute 6 value; field name configured in Dear Settings Nullable Optional
cust_attribute7 CharField Custom customer attribute 7 value; field name configured in Dear Settings Nullable Optional
cust_attribute8 CharField Custom customer attribute 8 value; field name configured in Dear Settings Nullable Optional
cust_attribute9 CharField Custom customer attribute 9 value; field name configured in Dear Settings Nullable Optional
cust_attribute10 CharField Custom customer attribute 10 value; field name configured in Dear Settings Nullable Optional

dear_customer_addresses

Addresses associated with Customers

Model: Dear_customer_addresses

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
source_dear ForeignKey
FK to dear_metadata
address_guid CharField Unique GUID identifier for this address in Dear Nullable Optional
customer_guid ForeignKey
FK to dear_customer 		Nullable Optional
line1 CharField Street address line 1 Nullable Optional
line2 CharField Street address line 2 (suite, unit, etc.) Nullable Optional
city CharField City/town name Nullable Optional
state CharField State/province/region Nullable Optional
postcode CharField Postal/ZIP code Nullable Optional
country CharField Must be an ISO standard country name Nullable Optional
type CharField Address type: Billing or Shipping Nullable Optional
default_for_type BooleanField True if this is the default address for its type

dear_customer_contacts

Contacts associated with Customers

Model: Dear_customer_contacts

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
source_dear ForeignKey
FK to dear_metadata
contact_guid CharField Nullable Optional
customer_guid ForeignKey
FK to dear_customer 		Nullable Optional
name CharField Nullable Optional
phone CharField Nullable Optional
mobile_phone CharField Nullable Optional
fax CharField Nullable Optional
email CharField Nullable Optional
website CharField Nullable Optional
comment CharField Nullable Optional
default BooleanField
include_in_email BooleanField

dear_customer_tag

Tags associated with Customers

Model: Dear_customer_tag

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
document_modified DateTimeField Nullable Optional
customer_guid ForeignKey
FK to dear_customer 		Nullable Optional
source_dear ForeignKey
FK to dear_metadata 		Nullable
tag_value CharField Nullable Optional

dear_financial_transactions

Financial transactions from Cin7 Core (Dear). These are not auto updated.

Model: Financial_transactions

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
id_guid CharField Unique
task_guid CharField Nullable Optional
source_dear ForeignKey
FK to dear_metadata
reference CharField Nullable Optional
debit_account_code CharField Nullable Optional
credit_account_code CharField Nullable Optional
amount FloatField Nullable Optional
original_amount FloatField Nullable Optional
currency_rate FloatField Nullable Optional
effective_date DateField Payment date, no timezone Nullable Optional
last_modified DateTimeField This is not from Dear, it is a experimental helper field for sync to analytics backend.
transaction CharField Nullable Optional
type CharField Transaction Type. Available values are Purchase,Sale,MoneySpend,MoneyReceive,BankTransfer,ExpenseClaimTask,FinishedGoods,InventoryWriteOff,StockTake,StockAdjustment,Journal,Disassembly,Depreciation Nullable Optional

dear_finished_goods_header

Finished Goods Header (Not fully implemented)

Model: Dear_fingoods_header

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
source_dear ForeignKey
FK to dear_metadata

dear_location

Dear Locations (that is, warehouses, not bins)

Model: Dear_location

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Unique
source_dear ForeignKey
FK to dear_metadata
location_guid CharField Primary Key Unique
location_name CharField Nullable Optional
deprecated BooleanField True means Deprecated (not active)

dear_metadata

Dear Company Name: this table is useful if you have multiple Dear instances in the GrowthPath Analytics Connector

Model: Dear_metadata

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
source_dear CharField Link to Dear instance which is the source of the data Unique
dear_company_name CharField Unique Nullable Optional
last_update DateTimeField Nullable Optional
base_currency_code CharField Three character currency code Nullable Optional

dear_po_header

Purchase Order header

Model: Dear_po_header

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
source_dear ForeignKey
FK to dear_metadata
order_guid CharField Unique
drop_ship_sale_guid ForeignKey
FK to dear_sales_header 		Nullable
global_purchase_status CharField PO Header status Nullable Optional
combined_receiving_status CharField Nullable Optional
combined_invoice_status CharField Nullable Optional
combined_payment_status CharField Nullable Optional
order_number CharField Nullable Optional
order_date DateField Date field when PO was created, no timezone Nullable Optional
order_memo TextField Nullable Optional
order_type CharField one of Simple Purchase, Advanced Purchase , Service Purchase Nullable Optional
blind_receipt BooleanField Nullable Optional
dear_url CharField This URL takes you to the Dear purchase order, as long as you are logged into Dear Nullable Optional
po_note TextField Nullable Optional
required_by_date DateField Date field, no timezone Nullable Optional
document_modified DateTimeField Nullable Optional
supplier_guid ForeignKey
FK to dear_supplier 		Nullable Optional
document_currency CharField Nullable Optional
terms CharField Nullable Optional
contact CharField Nullable Optional
document_exrate FloatField Nullable Optional
base_currency CharField Nullable Optional
warehouse CharField Nullable Optional
attribute_set CharField Nullable Optional
attribute1 CharField Nullable Optional
attribute2 CharField Nullable Optional
attribute3 CharField Nullable Optional
attribute4 CharField Nullable Optional
attribute5 CharField Nullable Optional
attribute6 CharField Nullable Optional
attribute7 CharField Nullable Optional
attribute8 CharField Nullable Optional
attribute9 CharField Nullable Optional
attribute10 CharField Nullable Optional
ordered_total_ex_tax_base DecimalField Translated at document exrate, not payment exrate Nullable Optional
ordered_total_incl_tax_base DecimalField Translated at document exrate, not payment exrate Nullable Optional
invoiced_total_ex_tax_base DecimalField Translated at document exrate, not payment exrate Nullable Optional
invoiced_total_incl_tax_base DecimalField Translated at document exrate, not payment exrate Nullable Optional
credited_total_ex_tax_base DecimalField Translated at document exrate, not payment exrate Nullable Optional
credited_total_incl_tax_base DecimalField Translated at document exrate, not payment exrate Nullable Optional
paid_total_base DecimalField This includes tax. Translated at document exrate, not payment exrate Nullable Optional
deposits_total_base DecimalField This includes tax. Translated at document exrate, not payment exrate Nullable Optional
deposits_applied_base DecimalField This includes tax. Translated at document exrate, not payment exrate Nullable Optional
refunded_total_base DecimalField includes tax. Translated at document exrate, not payment exrate Nullable Optional
received_total_ex_tax_base DecimalField Received and Put Away. Translated at document exrate, not payment exrate Nullable Optional
never_to_be_received_total_ex_tax_base DecimalField If PO is marked as received, this is what was cancelled. Translated at document exrate, not payment exrate Nullable Optional
ordered_total_ex_tax DecimalField Nullable Optional
ordered_total_incl_tax DecimalField Nullable Optional
invoiced_total_ex_tax DecimalField Nullable Optional
invoiced_total_incl_tax DecimalField Nullable Optional
credited_total_ex_tax DecimalField Nullable Optional
credited_total_incl_tax DecimalField Nullable Optional
paid_total DecimalField This includes tax Nullable Optional
deposits_total DecimalField This includes tax Nullable Optional
deposits_applied DecimalField This includes tax Nullable Optional
refunded_total DecimalField include tax Nullable Optional
received_total_ex_tax DecimalField Received and Put Away Nullable Optional
never_to_be_received_total_ex_tax DecimalField If PO is marked as received, this is what was cancelled Nullable Optional
shipping_company_name CharField Nullable Optional
shipping_addr1 CharField Nullable Optional
shipping_addr2 CharField Nullable Optional
shipping_postalcode CharField Nullable Optional
shipping_region CharField Nullable Optional
shipping_town CharField Nullable Optional
shipping_country CharField Nullable Optional

dear_product

Product details from Cin7 Core (Dear)

Model: Dear_product

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
document_modified DateTimeField UTC timestamp when product record was last modified in Dear Nullable Optional
source_dear ForeignKey
FK to dear_metadata
product_guid CharField Unique GUID identifier for the product in Dear/Cin7 Core Unique
type CharField Product type: Stock, Non-Inventory, or Service Nullable Optional
sku CharField Stock Keeping Unit - unique product identifier code Nullable Optional
product_name CharField Product name as displayed in Dear and on documents Nullable Optional
product_category CharField Product category for grouping and reporting Nullable Optional
brand CharField Product brand/manufacturer name Nullable Optional
average_cost DecimalField Current average cost in base currency (FIFO/weighted avg) Nullable Optional
tags CharField Comma-separated tags assigned to the product for filtering Nullable Optional
uom CharField Unit of measure (e.g., Each, Box, Kg, Litre) Nullable Optional
status CharField same value as Dear Status Nullable Optional
default_supplier_guid ForeignKey
FK to dear_supplier 		Default supplier Nullable Optional
costing_method CharField Inventory costing method: FIFO, Special, etc. Nullable Optional
default_location CharField Default warehouse/location for this product Nullable Optional
barcode CharField Primary barcode (EAN, UPC, etc.) for scanning Nullable Optional
stock_locator CharField Bin/shelf location code within the warehouse Nullable Optional
purchase_tax_rule CharField Tax rule applied when purchasing this product Nullable Optional
sale_tax_rule CharField Tax rule applied when selling this product Nullable Optional
weight FloatField Product weight in the units specified by weight_units Nullable Optional
weight_units CharField Weight unit of measure (kg, lb, oz, g) Nullable Optional
attribute_set CharField Name of the custom attribute template applied to this product Nullable Optional
prod_attribute1 CharField Custom product attribute 1 value; field name configured in Dear Settings Nullable Optional
prod_attribute2 CharField Custom product attribute 2 value; field name configured in Dear Settings Nullable Optional
prod_attribute3 CharField Custom product attribute 3 value; field name configured in Dear Settings Nullable Optional
prod_attribute4 CharField Custom product attribute 4 value; field name configured in Dear Settings Nullable Optional
prod_attribute5 CharField Custom product attribute 5 value; field name configured in Dear Settings Nullable Optional
prod_attribute6 CharField Custom product attribute 6 value; field name configured in Dear Settings Nullable Optional
prod_attribute7 CharField Custom product attribute 7 value; field name configured in Dear Settings Nullable Optional
prod_attribute8 CharField Custom product attribute 8 value; field name configured in Dear Settings Nullable Optional
prod_attribute9 CharField Custom product attribute 9 value; field name configured in Dear Settings Nullable Optional
prod_attribute10 CharField Custom product attribute 10 value; field name configured in Dear Settings Nullable Optional
price_tier1 DecimalField Selling price for price tier 1 (tier names configured in Dear Settings) Nullable Optional
price_tier2 DecimalField Selling price for price tier 2 Nullable Optional
price_tier3 DecimalField Selling price for price tier 3 Nullable Optional
price_tier4 DecimalField Selling price for price tier 4 Nullable Optional
price_tier5 DecimalField Selling price for price tier 5 Nullable Optional
price_tier6 DecimalField Selling price for price tier 6 Nullable Optional
price_tier7 DecimalField Selling price for price tier 7 Nullable Optional
price_tier8 DecimalField Selling price for price tier 8 Nullable Optional
price_tier9 DecimalField Selling price for price tier 9 Nullable Optional
price_tier10 DecimalField Selling price for price tier 10 Nullable Optional
carton_length DecimalField Outer carton length dimension for shipping calculations Nullable Optional
carton_width DecimalField Outer carton width dimension for shipping calculations Nullable Optional
carton_height DecimalField Outer carton height dimension for shipping calculations Nullable Optional
carton_quantity DecimalField Number of units per outer carton Nullable Optional
carton_inner_quantity DecimalField Number of units per inner carton (if using inner packs) Nullable Optional
drop_ship_mode CharField No Drop Ship, Optional Drop Ship, Always Drop Ship Nullable Optional
sellable BooleanField True if product is available for sale (not just for purchasing)
hscode CharField Harmonized System code for customs/international trade classification Nullable Optional
country_of_origin CharField Country name where the product was manufactured Nullable Optional
country_of_origin_code CharField ISO country code for country of origin (e.g., AU, US, CN) Nullable Optional
bom_type CharField read only. Assembly, Production, Make to Order, None Nullable Optional
auto_assembly BooleanField True if BOM products are auto-assembled when sold
auto_disassembly BooleanField True if assembled products auto-disassemble when returned
qty_to_produce DecimalField Default quantity to produce for BOM/assembly products Nullable Optional
cogs_account CharField GL account code for Cost of Goods Sold Nullable Optional
revenue_account CharField GL account code for revenue when this product is sold Nullable Optional
expense_account CharField GL expense account for non-inventory purchases Nullable Optional
inventory_account CharField GL asset account for inventory value Nullable Optional

dear_product_availability

Product availability from Dear, plus hard allocations (qty reserved for a specfic order)

Model: Dear_product_availability

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Unique
id_product_availability TextField Primary Key Unique
source_dear ForeignKey
FK to dear_metadata
product_guid ForeignKey
FK to dear_product 		Nullable Optional
sku CharField Nullable Optional
name CharField Nullable Optional
barcode CharField Nullable Optional
location CharField This is the location name, forced to uppercase Nullable Optional
bin CharField Nullable Optional
batch CharField Nullable Optional
expiry_date DateField A Date field (no timezone) Nullable Optional
onhand FloatField Quantity on hand Nullable Optional
allocated FloatField Quantity allocated Nullable Optional
hard_allocated FloatField Quantity locked to an authorised pick Nullable Optional
available FloatField Quantity available Nullable Optional
in_transit FloatField In-transit (between locations) Nullable Optional
onorder FloatField Quantity on an authorised PO Nullable Optional
stockonhand_base_currency DecimalField value of stock on hand, base currency Nullable Optional

dear_product_custom_prices

Custom prices for products per customer.

Model: Dear_product_custom_prices

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
source_dear ForeignKey
FK to dear_metadata
product_guid ForeignKey
FK to dear_product 		Nullable Optional
customer_guid ForeignKey
FK to dear_customer 		Nullable Optional
price DecimalField The custom price Nullable Optional

dear_product_family

Product Family details

Model: Dear_product_family

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
product_family_guid CharField Nullable Optional
document_modified DateTimeField Nullable Optional
source_dear ForeignKey
FK to dear_metadata
product_guid ForeignKey
FK to dear_product 		Nullable Optional
family_name CharField Nullable Optional
short_description CharField Nullable Optional
family_option1_name CharField Nullable Optional
family_option1_value CharField Nullable Optional
family_option2_name CharField Nullable Optional
family_option2_value CharField Nullable Optional
family_option3_name CharField Nullable Optional
family_option3_value CharField Nullable Optional

dear_product_tag

Tags associated with Products

Model: Dear_product_tag

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
document_modified DateTimeField Nullable Optional
product_guid ForeignKey
FK to dear_product 		Nullable Optional
source_dear ForeignKey
FK to dear_metadata
tag_value CharField Nullable Optional

dear_sales_header

Sales Header data

Model: Dear_sales_header

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Unique
source_dear ForeignKey
FK to dear_metadata
order_guid CharField Unique GUID identifier for the sales order in Dear/Cin7 Core Primary Key Unique
global_sale_status CharField SO Header Sale Status, see Available Sale Statues https://dearinventory.docs.apiary.io/#reference/sale Nullable Optional
order_status CharField Sale Order status, one of NOT AVAILABLE,DRAFT,AUTHORISED,VOIDED,AUTH_NO_ALLOC,FULFILLED,CLOSED. See Available Order Statuses at https://dearinventory.docs.apiary.io/#reference/sale Nullable Optional
combined_picking_status CharField Possible Values are VOIDED, NOT AVAILABLE, PICKED, PICKING , NOT PICKED , PARTIALLY PICKED Nullable Optional
combined_packing_status CharField Possible Values are VOIDED, NOT AVAILABLE, PACKED, PACKING, NOT PACKED, PARTIALLY PACKED Nullable Optional
combined_shipping_status CharField Possible Values are VOIDED, NOT AVAILABLE, SHIPPED, SHIPPING , NOT SHIPPED , PARTIALLY SHIPPED Nullable Optional
fulfillment_status CharField Possible Values are FULFILLED, PARTIALLY FULFILLED, NOT AVAILABLE, NOT FULFILLED, VOIDED Nullable Optional
combined_invoice_status CharField Possible Values are VOIDED, DRAFT, AUTHORISED, NOT AVAILABLE, PAID Nullable Optional
combined_payment_status CharField NOT REFUNDED, PREPAID, PARTIALLY PAID, UNPAID, PAID, VOIDED Nullable Optional
combined_tracking_numbers CharField Comma-separated list of all tracking numbers across all shipments for this order Nullable Optional
order_reference CharField Sale Order Number Nullable Optional
sale_order_date DateField Date when order was created (no timezone) Nullable Optional
document_modified DateTimeField UTC timestamp when this record was last modified in Dear; used for incremental sync Nullable Optional
customer_guid ForeignKey
FK to dear_customer 		Nullable Optional
sales_rep CharField Sales representative name assigned to this order, often used to record sales channels or originating Shopify store Nullable Optional
source_channel CharField Source of the order, such as B2B, POS, Shopify. Is empty for orders entered via the DEAR/Cin7 Web interface. Read only Nullable Optional
document_currency CharField Currency code (e.g., USD, AUD) for amounts on this order Nullable Optional
document_exrate FloatField Exchange rate from document currency to base currency at order time Nullable Optional
base_currency CharField Dear instance's base currency code for _base suffix columns Nullable Optional
warehouse CharField Default warehouse/location name for this order's fulfillment Nullable Optional
order_memo TextField Internal memo visible in Dear UI, not printed on documents Nullable Optional
sale_note TextField Customer-facing note that can appear on printed documents Nullable Optional
sale_contact CharField Contact person name for delivery coordination Nullable Optional
sale_contact_phone CharField Contact phone number for delivery coordination Nullable Optional
sale_contact_email CharField Contact email address for delivery coordination Nullable Optional
order_type CharField Type of sale: Simple Sale or Advanced Sale, Service Sale Nullable Optional
attribute_set CharField Name of the custom attribute template applied to this sale order Nullable Optional
attribute1 CharField Custom sale attribute 1 value; field name configured in Dear Settings Nullable Optional
attribute2 CharField Custom sale attribute 2 value; field name configured in Dear Settings Nullable Optional
attribute3 CharField Custom sale attribute 3 value; field name configured in Dear Settings Nullable Optional
attribute4 CharField Custom sale attribute 4 value; field name configured in Dear Settings Nullable Optional
attribute5 CharField Custom sale attribute 5 value; field name configured in Dear Settings Nullable Optional
attribute6 CharField Custom sale attribute 6 value; field name configured in Dear Settings Nullable Optional
attribute7 CharField Custom sale attribute 7 value; field name configured in Dear Settings Nullable Optional
attribute8 CharField Custom sale attribute 8 value; field name configured in Dear Settings Nullable Optional
attribute9 CharField Custom sale attribute 9 value; field name configured in Dear Settings Nullable Optional
attribute10 CharField Custom sale attribute 10 value; field name configured in Dear Settings Nullable Optional
ship_by DateField Date, no timezone provided Nullable Optional
customer_reference CharField Customer's PO number or external reference (e.g., Shopify order name like #1234) Nullable Optional
quoted_total_ex_tax_base DecimalField Includes everything Nullable Optional
quoted_total_incl_tax_base DecimalField Includes everything Nullable Optional
ordered_total_ex_tax_base DecimalField Includes everything on the order Nullable Optional
ordered_total_incl_tax_base DecimalField Includes everything on the order Nullable Optional
ordered_stock_ex_tax_base DecimalField Only stock items are included, no additional charges, no non-inventory items, but drop ship items are included Nullable Optional
ordered_stock_incl_tax_base DecimalField Only stock items are included, no additional charges, no non-inventory items, but drop ship items are included Nullable Optional
invoiced_total_ex_tax_base DecimalField Includes everything on the invoice Nullable Optional
invoiced_total_incl_tax_base DecimalField Includes everything on the invoice Nullable Optional
credited_total_ex_tax_base DecimalField Includes everything on the CN Nullable Optional
credited_total_incl_tax_base DecimalField Includes everything on the CN Nullable Optional
paid_total_base DecimalField Payments can't be split by item type, so payment will include additional charges and non-inventory items Nullable Optional
paid_total_ex_tax_base DecimalField Estimated tax is excluded from this total to make it more comparable with the other values, but it is total payment including for non-inventory items and additional charges and including tax Nullable Optional
shipped_ex_tax_base DecimalField At selling price, not cogs, so it is comparable with the other columns. Drop shipped items are valued as fully shipped Nullable Optional
refunded_total_base DecimalField Total refunds paid back to customer in base currency (incl tax) Nullable Optional
refunded_ex_tax_base DecimalField Total refunds in base currency with estimated tax removed Nullable Optional
prepayment_total_incl_tax_base DecimalField Deposit paid on the quote (incl tax). Nullable Optional
prepayment_total_ex_tax_base DecimalField Deposit paid on the quote (estimated tax removed for comaparison) Nullable Optional
prepayment_applied_incl_tax_base DecimalField If the customer credit accounts is setup in Dear Instance Settings, this records payments made using customer credits, including deposits Nullable Optional
prepayment_applied_ex_tax_base DecimalField Records payments made using customer credits, including deposits (estimated tax removed for comparison) Nullable Optional
in_fulfilment_ex_tax_base DecimalField Covers orders with an authorised pick (or pack) but not yet shipped. Only stock items are included, no additional charges, no non-inventory items Nullable Optional
dear_url CharField This URL takes you to the Dear order, as long as you are logged into Dear Nullable Optional
backorder_analysis_url CharField This URL takes you a GrowthPath backorder and fulfilment summary of the order Nullable Optional
shipping_company_name CharField Company/business name at the shipping destination Nullable Optional
shipping_addr1 CharField Street address line 1 for shipping destination Nullable Optional
shipping_addr2 CharField Street address line 2 for shipping destination (suite, unit, etc.) Nullable Optional
shipping_postalcode CharField Postal/ZIP code for shipping destination Nullable Optional
shipping_region CharField State/province/region for shipping destination Nullable Optional
shipping_town CharField City/town for shipping destination Nullable Optional
shipping_country CharField Country name for shipping destination Nullable Optional
shipping_notes CharField Delivery instructions or special notes for shipping Nullable Optional
sale_carrier CharField Carrier/shipping method name configured in Dear (e.g., DHL, FedEx) Nullable Optional
billing_addr1 CharField Street address line 1 for billing address Nullable Optional
billing_addr2 CharField Street address line 2 for billing address Nullable Optional
billing_postalcode CharField Postal/ZIP code for billing address Nullable Optional
billing_region CharField State/province/region for billing address Nullable Optional
billing_town CharField City/town for billing address Nullable Optional
billing_country CharField Country name for billing address Nullable Optional
financial_hash CharField hash value of financial transactions Nullable Optional

dear_sales_invoice

Sales Invoices and Credit notes header: invoices and credit notes.

Model: Dear_invoice_header

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Unique
task_id CharField Unique invoice id, this is present in sales_fact_table for joining lines but is not set up as a foreign key yet. Note that sometimes invoice and credit notes have the same task ID, for sure if the sale started as a simple sale Primary Key Unique
source_dear ForeignKey
FK to dear_metadata
order_guid ForeignKey
FK to dear_sales_header 		Nullable
invoice_number CharField invoice or credit note number Nullable Optional
sales_order_number CharField here as a convenience. To join to sales order header, use order_guid Nullable Optional
memo CharField Invoice memo Nullable Optional
invoice_status CharField Invoice status, one of NOT AVAILABLE,DRAFT,AUTHORISED,VOIDED,PAID see https://dearinventory.docs.apiary.io/#reference/sale Nullable Optional
invoice_date DateField Serves as the transaction date for the invoice or credit note. There is no timezone for this date Nullable Optional
invoice_due_date DateField There is no timezone for this date Nullable Optional
document_modified DateTimeField Nullable Optional
customer_guid ForeignKey
FK to dear_customer 		Nullable Optional
document_currency CharField Nullable Optional
document_exrate FloatField Nullable Optional
base_currency CharField Nullable Optional
linked_fulfilments CharField only present if this invoice was linked to one or more fulfilments Nullable Optional

dear_stock_adjustment_header

Stock Adjustment header

Model: Dear_stock_adjustment_header

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Unique
source_dear ForeignKey
FK to dear_metadata
task_guid CharField Primary Key Unique
stocktake_number CharField Nullable Optional
status CharField
effective_date DateTimeField Nullable Optional
reference CharField Nullable Optional
document_modified DateTimeField Nullable Optional
account CharField Nullable Optional

dear_stock_adjustment_lines

Stock Adjustment Lines

Model: Dear_stock_adjustment_lines

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
source_dear ForeignKey
FK to dear_metadata
task_guid ForeignKey
FK to dear_stock_adjustment_header
product_guid ForeignKey
FK to dear_product 		Nullable Optional
line_type CharField new or existing Nullable Optional
quantity_on_hand FloatField before transfer Nullable Optional
quantity_available FloatField before transfer Nullable Optional
adjustment_quantity FloatField before transfer Nullable Optional
unit_cost DecimalField only used in New Stock Lines Nullable Optional
location CharField Nullable Optional
location_bin CharField Nullable Optional
batch_sn CharField Nullable Optional
expiry_date DateTimeField Nullable Optional
comments CharField Nullable Optional
document_modified DateTimeField Nullable Optional

dear_stock_transfer_header

Stock Transfer

Model: Dear_stock_transfer_header

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Unique
source_dear ForeignKey
FK to dear_metadata
task_guid CharField Primary Key Unique
from_location CharField Nullable Optional
from_location_bin CharField Nullable Optional
to_location CharField Nullable Optional
to_location_bin CharField Nullable Optional
status CharField
number CharField Nullable Optional
cost_distribution_type CharField
in_transit_account CharField Nullable Optional
departure_date DateTimeField Nullable Optional
completion_date DateTimeField Nullable Optional
required_by_date DateTimeField Nullable Optional
reference CharField Nullable Optional
skip_order BooleanField
document_modified DateTimeField Nullable Optional

dear_stock_transfer_lines

Stock Transfer Lines

Model: Dear_stock_transfer_lines

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
source_dear ForeignKey
FK to dear_metadata
task_guid ForeignKey
FK to dear_stock_transfer_header
product_guid ForeignKey
FK to dear_product 		Nullable Optional
quantity_on_hand FloatField before transfer Nullable Optional
quantity_available FloatField before transfer Nullable Optional
transfer_quantity FloatField Nullable Optional
batch_sn CharField Nullable Optional
expiry_date DateTimeField Nullable Optional
comments CharField Nullable Optional
document_modified DateTimeField Nullable Optional

dear_supplier

Supplier details from Cin7 Core (Dear)

Model: Dear_supplier

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
document_modified DateTimeField Nullable Optional
supplier_guid CharField Unique
supplier_name CharField Nullable Optional
status CharField Nullable Optional
tags CharField Nullable Optional
source_dear ForeignKey
FK to dear_metadata
attribute_set CharField Nullable Optional
supplier_attribute1 CharField Nullable Optional
supplier_attribute2 CharField Nullable Optional
supplier_attribute3 CharField Nullable Optional
supplier_attribute4 CharField Nullable Optional
supplier_attribute5 CharField Nullable Optional
supplier_attribute6 CharField Nullable Optional
supplier_attribute7 CharField Nullable Optional
supplier_attribute8 CharField Nullable Optional
supplier_attribute9 CharField Nullable Optional
supplier_attribute10 CharField Nullable Optional

dear_supplier_product

Products linked to Suppliers

Model: Dear_supplier_product

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
source_dear ForeignKey
FK to dear_metadata 		Nullable
product_guid ForeignKey
FK to dear_product 		Nullable Optional
supplier_guid ForeignKey
FK to dear_supplier 		Nullable Optional
supplier_sku CharField Nullable Optional
supplier_product_name CharField Nullable Optional
currency CharField Nullable Optional
latest_purchase_cost DecimalField Nullable Optional
fixed_cost DecimalField Nullable Optional
last_supplied DateTimeField Nullable Optional

mainfreight_events

Mainfreight events

Model: Mainfreight_events

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
unique_key CharField The ID field
event_type CharField
event_datetime DateTimeField
last_modified DateTimeField
order_reference CharField Dear Sales Number Nullable Optional
fulfilment_number IntegerField
event_text TextField Nullable Optional
source_dear ForeignKey
FK to dear_metadata

product_movements

All stock movements with quantity and date

Model: Product_movements

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
product_guid ForeignKey
FK to dear_product 		Nullable Optional
document_modified DateTimeField Nullable Optional
source_dear ForeignKey
FK to dear_metadata
movement_type CharField Nullable Optional
movement_taskid CharField Link to the source documents, such as a PO Nullable Optional
movement_date DateField No timezone Nullable Optional
movement_number CharField A document reference, such as a sales order number Nullable Optional
movement_status CharField Currently documented in the API but not used by Dear Nullable Optional
quantity FloatField This is a signed number, > 0 means increase in SOH Nullable Optional
location CharField Name of the location, not the ID Nullable Optional
batch_sn CharField Nullable Optional
expiry_date DateField No timezone Nullable Optional
from_to CharField Nullable Optional
amount DecimalField Base currency value of the transaction Nullable Optional

purchase_order_lines

PO lines includng draft and not yet invoiced POs

Model: Purchase_order_lines

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
order_guid ForeignKey
FK to dear_po_header
order_reference CharField Nullable Optional
source_dear ForeignKey
FK to dear_metadata
line_type CharField ITEM or ADDITIONAL_CHARGE Nullable Optional
comment CharField Nullable Optional
product_guid ForeignKey
FK to dear_product 		will be empty for adhoc service items Nullable Optional
description CharField Nullable Optional
order_quantity FloatField Nullable Optional
unit_price_before_discount DecimalField unit price before row discount, document currency Nullable Optional
unit_price_net DecimalField Unit price with tax removed, even if tax inclusive, document currency Nullable Optional
unit_tax DecimalField unit tax, document currency Nullable Optional
unit_price_before_discount_base_cur DecimalField unit price before row discount, base currency Nullable Optional
unit_price_net_base_cur DecimalField Net unit price in base currency Nullable Optional
document_exrate FloatField From PO Header Nullable Optional
document_currency CharField Nullable Optional
base_currency CharField Nullable Optional
line_total DecimalField Line total, document currency. Includes tax if order is tax inclusive Nullable Optional
line_tax_total DecimalField Line tax total, document currency Nullable Optional
line_total_base_cur DecimalField Line total, base currency. Includes tax if order is tax inclusive Nullable Optional
line_tax_total_base_cur DecimalField Line tax total, base currency Nullable Optional
line_tax_rule CharField Nullable Optional

purchase_order_payments

Payments relating to POs, including deposits and invoice payments

Model: Purchase_order_payments

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
source_dear ForeignKey
FK to dear_metadata
order_guid ForeignKey
FK to dear_po_header
order_reference CharField Nullable Optional
doc_guid CharField GUID of the Task, ie the Invoice or Credit Note Nullable Optional
doc_number CharField Document number of the Task, ie the Invoice or Credit Note Nullable Optional
payment_guid CharField Nullable Optional
payment_type CharField Prepayment,InvoicePayment,Refund Nullable Optional
payment_reference CharField Nullable Optional
payment_amount DecimalField Nullable Optional
payment_date DateField Payment date, no timezone Nullable Optional
payment_account CharField Nullable Optional
payment_currency CharField Nullable Optional
base_currency CharField Nullable Optional
payment_exrate FloatField Nullable Optional

purchases_fact

based on authorised PO invoice lines

Model: Purchases_fact

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
order_guid ForeignKey
FK to dear_po_header 		links to dear_po_header table
source_dear ForeignKey
FK to dear_metadata 		links to Dear company name
document_modified DateTimeField Nullable Optional
supplier_guid ForeignKey
FK to dear_supplier 		Nullable Optional
order_date DateField A Date field with no timezone, date when PO was created Nullable Optional
transaction_date DateField Invoice data, a Date field with no timezone Nullable Optional
inv_due_date DateField Invoice due date, a Date with no timezone Nullable Optional
product_guid ForeignKey
FK to dear_product 		For adhoc service lines, there is no product and this is empty Nullable Optional
comment CharField Nullable Optional
line_description CharField Nullable Optional
document_exrate FloatField Nullable Optional
document_currency CharField Nullable Optional
base_currency CharField Nullable Optional
invoice_quantity FloatField qty associated with revenue, such as invoice or credit note Nullable Optional
movement_quantity FloatField quantity associated with stock movement, >0 for receipt Nullable Optional
transaction_type CharField Nullable Optional
global_purchase_status CharField Refer to Dear API documentation Nullable Optional
order_number CharField Nullable Optional
invoice_number CharField Nullable Optional
discount_percentage FloatField Percentage discount Nullable Optional
unit_before_discount DecimalField in document currency Nullable Optional
unit_net DecimalField Unit price with tax removed, even if tax inclusive, document currency Nullable Optional
unit_tax DecimalField unit tax, document currency Nullable Optional
warehouse CharField location from PO header Nullable Optional
account CharField Nullable Optional
cogs_account CharField Nullable Optional
attribute1 CharField Nullable Optional
attribute2 CharField Nullable Optional
attribute3 CharField Nullable Optional
attribute4 CharField Nullable Optional
attribute5 CharField Nullable Optional
attribute6 CharField Nullable Optional
attribute7 CharField Nullable Optional
attribute8 CharField Nullable Optional
attribute9 CharField Nullable Optional
attribute10 CharField Nullable Optional
year_month_currency CharField Used to find average monthly exrate Nullable Optional
line_invoice DecimalField Line total, document currency. Always without tax, even if the order is tax inclusive Nullable Optional
line_tax DecimalField Tax in the line, document currency Nullable Optional
line_invoice_base_currency DecimalField Line total, base currency. Always without tax, even if the order is tax inclusive Nullable Optional
line_tax_base_currency DecimalField Tax in the line, base currency Nullable Optional

purchases_lines_lifecycle

Shows the status of a PO line (what's been invoiced, received etc)

Model: Purchases_lines_lifecycle

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
order_guid ForeignKey
FK to dear_po_header
order_reference CharField Nullable Optional
source_dear ForeignKey
FK to dear_metadata
line_type CharField Nullable Optional
product_guid ForeignKey
FK to dear_product 		Nullable Optional
sku CharField Nullable Optional
ordered_qty FloatField In the case of a Blind Receipt, this is the invoice qty Nullable Optional
received_and_putaway_qty FloatField Total received or putaway Nullable Optional
not_yet_received_qty FloatField Qty which has not been received or putaway, that is, stock which has not arrived at all Nullable Optional
never_to_be_received_qty FloatField If an order is marked as Received, then this is the quantity ordered but which is now cancelled Nullable Optional
invoiced_qty FloatField Nullable Optional
credited_qty FloatField Nullable Optional
returned_qty FloatField Nullable Optional
avg_unit_price_base DecimalField Nullable Optional
ordered_basecur DecimalField Nullable Optional
received_and_putaway_basecur DecimalField Nullable Optional
not_yet_received_basecur DecimalField Nullable Optional
never_to_be_received_basecur DecimalField Nullable Optional
invoiced_basecur DecimalField Nullable Optional
credited_basecur DecimalField Nullable Optional
returned_basecur DecimalField Nullable Optional

sales_fact

Based on authorised invoice lines; the revenue figures in this table should agree with the data in Xero or QBO and are the same as the Cin7 Core Report 'Sale Invoices & Credit Notes' (they should be the same to the cent). Margins in this table are for management accounting purposes: Actual COGS from shipments is used if available, but if it not available (a line on the invoice which has not yet shipped), the margin figure falls back to average cost or supplier price as a place holder margin. Each row shows the COGS source.

Model: Sales_fact

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
order_guid ForeignKey
FK to dear_sales_header 		Nullable Optional
source_dear ForeignKey
FK to dear_metadata
task_id ForeignKey
FK to dear_sales_invoice 		Invoice task id (guid unique to invoice header) Nullable Optional
document_modified DateTimeField UTC timestamp when parent sale was last modified in Dear Nullable Optional
customer_guid ForeignKey
FK to dear_customer 		Nullable Optional
transaction_date DateField invoice date. A date with no timezone Nullable Optional
inv_due_date DateField Invoice due date, A date with no timezone Nullable Optional
product_guid ForeignKey
FK to dear_product 		Note: This field will show the line description for ad-hoc additional charges (which do not have a real product ID) Nullable Optional
line_description CharField Product name or ad-hoc description from the invoice line Nullable Optional
revenue_quantity FloatField quantity which affects revenue accounts (invoice or credited amount) Nullable Optional
dropship_quantity FloatField this is included in revenue quantity, not in addition to it Nullable Optional
movement_quantity FloatField quantity which affects inventory accounts (shipments or returns) Nullable Optional
transaction_type CharField Type of transaction: Invoice or Credit Note Nullable Optional
order_reference CharField sale order number Nullable Optional
global_sale_status CharField see Dear API reference Nullable Optional
document_reference CharField associated document number, e.g. invoice number Nullable Optional
document_currency CharField Currency code of the invoice (e.g., USD, AUD) Nullable Optional
invoice_section CharField Product or Additional Charge: Whether this is in the product line part of the invoice or in additional charges Nullable Optional
unit_before_discount DecimalField unit price in document currency before discount, , always tax exclusive Nullable Optional
unit_net DecimalField unit price in document currency after discount, always tax exclusive Nullable Optional
tax_rule CharField inclusive or exclusive on the order, but tax is never included in revenue or unit prices Nullable Optional
unit_tax DecimalField Unit tax (document currency) Nullable Optional
warehouse CharField order header location, not the location of the pick Nullable Optional
sales_rep CharField sales rep, from the order header Nullable Optional
unit_cogs_average_cost DecimalField average cost price in base currency at the time of the sales order Nullable Optional
unit_cogs_actual DecimalField actual cost price in base currency. Only available if there is a shipment Nullable Optional
unit_cogs_best_available DecimalField is actual cost if available, else average cost Nullable Optional
ad_chg_spread DecimalField Document currency. Spreads invoice additional charges if they are < 0 to product lines to apportion discounts to product lines; this value is not part of margin fields in this table, you must manually use this value to adjust the line margin Nullable Optional
document_exrate FloatField Exchange rate from document currency to base currency at invoice time Nullable Optional
revenue_account CharField GL account code for revenue from this line item Nullable Optional
cogs_account CharField Not available at present Nullable Optional
base_currency CharField Dear instance's base currency code for _base_currency suffix columns Nullable Optional
attribute1 CharField Sale order custom attribute 1 value (copied from order header) Nullable Optional
attribute2 CharField Sale order custom attribute 2 value (copied from order header) Nullable Optional
attribute3 CharField Sale order custom attribute 3 value (copied from order header) Nullable Optional
attribute4 CharField Sale order custom attribute 4 value (copied from order header) Nullable Optional
attribute5 CharField Sale order custom attribute 5 value (copied from order header) Nullable Optional
attribute6 CharField Sale order custom attribute 6 value (copied from order header) Nullable Optional
attribute7 CharField Sale order custom attribute 7 value (copied from order header) Nullable Optional
attribute8 CharField Sale order custom attribute 8 value (copied from order header) Nullable Optional
attribute9 CharField Sale order custom attribute 9 value (copied from order header) Nullable Optional
attribute10 CharField Sale order custom attribute 10 value (copied from order header) Nullable Optional
shipping_company_name CharField Company/business name at the shipping destination Nullable Optional
shipping_addr1 CharField Street address line 1 for shipping destination Nullable Optional
shipping_addr2 CharField Street address line 2 for shipping destination Nullable Optional
shipping_postalcode CharField Postal/ZIP code for shipping destination (GeoCharField for analytics) Nullable Optional
shipping_region CharField State/province/region for shipping destination Nullable Optional
shipping_town CharField City/town for shipping destination (GeoCharField for analytics) Nullable Optional
shipping_country CharField Country name for shipping destination (GeoCharField for analytics) Nullable Optional
year_month_currency CharField YYYY-MM format with currency code for time-series grouping (e.g., 2024-01-USD) Nullable Optional
line_revenue DecimalField Line revenue in document currency, without tax Nullable Optional
line_revenue_base_currency DecimalField Line revenue in base currency, without tax Nullable Optional
line_cogs_base_currency DecimalField Actual cost if available else average cost Nullable Optional
line_margin_base_currency DecimalField Line margin in base currency, based on actual cogs if available, else average cogs. Nullable Optional
line_estimated_invoice_margin DecimalField Deprecated field. Use line_margin_base_currency instead. Nullable Optional
cogs_source CharField Source of margin: AverageCost or Actual Nullable Optional

sales_lines_lifecycle

Shows the lifecycle status of lines on the sales order: quantity and value picked, shipped, invoiced etc

Model: Sales_lines_lifecycle

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
order_guid ForeignKey
FK to dear_sales_header
order_reference CharField Sale Order Number (e.g., SO-12345). Use for display; join via order_guid for relationships Nullable Optional
source_dear ForeignKey
FK to dear_metadata
line_type CharField Product type, one of Stock, Non Inventory or Service Nullable Optional
product_guid ForeignKey
FK to dear_product 		Nullable Optional
sku CharField DEAR SKU. Case insensitive. Join to dear_product via product_guid for full details Nullable Optional
quoted_qty FloatField Quantity on the original quote. May differ from ordered_qty if quote was modified Nullable Optional
ordered_qty FloatField Quantity ordered by customer on the sales order Nullable Optional
shipped_qty FloatField dropships are included in this total Nullable Optional
shipped_with_tracking_qty FloatField Quantity shipped with tracking information attached Nullable Optional
in_fulfilment_qty FloatField The qty is in auth picking but not yet auth shipped Nullable Optional
not_yet_processed_qty FloatField Has not entered auth picking Nullable Optional
not_yet_shipped_qty FloatField not yet shipped or dropshipped Nullable Optional
never_to_be_fulfilled_qty FloatField If a back order is cancelled because the order is marked as Fulfilled Nullable Optional
invoiced_qty FloatField Quantity that has been invoiced (appears on an authorised invoice) Nullable Optional
drop_ship_qty FloatField Quantity fulfilled via drop shipping (shipped directly from supplier to customer, no goods movement in DEAR) Nullable Optional
credited_qty FloatField Quantity credited via credit notes Nullable Optional
returned_qty FloatField Not implemented yet Nullable Optional
avg_unit_price_base DecimalField Average unit selling price in base currency, calculated from order line total / quantity Nullable Optional
quoted_basecur DecimalField Total quoted value in base currency (quoted_qty × avg_unit_price_base) Nullable Optional
ordered_basecur DecimalField Total ordered value in base currency (ordered_qty × avg_unit_price_base) Nullable Optional
shipped_basecur DecimalField valued at order price, not cogs, so that is is comparable Nullable Optional
shipped_with_tracking_basecur DecimalField Value of shipments with tracking in base currency Nullable Optional
in_fulfilment_basecur DecimalField Value in picking but not yet shipped, in base currency Nullable Optional
not_yet_processed_basecur DecimalField quantity which has not even started fulfilment Nullable Optional
not_yet_shipped_basecur DecimalField The counts drop-shipped qty as shipped Nullable Optional
never_to_be_fulfilled_basecur DecimalField Will not be fulfilled as the order is marked as closed Nullable Optional
invoiced_basecur DecimalField Total invoiced value in base currency Nullable Optional
drop_ship_basecur DecimalField Value of drop-shipped items in base currency Nullable Optional
actual_cogs_per_unit DecimalField Actual COGS per unit from shipment batches. Only populated for shipped items with batch tracking Nullable Optional
avg_cost_per_unit DecimalField At the time this row was last updated: this nbr can change Nullable Optional
unit_cogs_best_available DecimalField Actual COGS if available, otherwise average cost Nullable Optional
cogs_source CharField Source of margin: AverageCost or Actual Nullable Optional
credited_basecur DecimalField Value of credited items in base currency Nullable Optional
returned_basecur DecimalField Not implemented yet Nullable Optional

sales_order_inventory_transactions

Inventory movements by date and value associated with the sales order, for detailed revenue and cogs analysis. Covers orders, shipments, credit notes and restocks

Model: Sales_order_inventory_transactions

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
order_guid ForeignKey
FK to dear_sales_header
order_reference CharField sale order number Nullable Optional
document_reference CharField invoice or credit note number Nullable Optional
source_dear ForeignKey
FK to dear_metadata
transaction_type CharField Nullable Optional
transaction_date DateField Date, No timezone Nullable Optional
product_guid ForeignKey
FK to dear_product 		Nullable Optional
account CharField Nullable Optional
quantity FloatField Nullable Optional
signed_revenue_quantity FloatField positive means invoice, negative is a credit, Nullable Optional
signed_inventory_quantity FloatField positive means shipment (decrease in inventory value), negative is a return. Adjustments such as landed costs are included too. The quantity is not a physical movement qty, but an accounting quantity. Nullable Optional
unit_value_base DecimalField Nullable Optional
rev_unit_value_base DecimalField The unit cost if this transaction affects a revenue-style/AR account Nullable Optional
cogs_unit_value_base DecimalField The unit costs if this transaction affects the value of inventory/cogs Nullable Optional
rev_line_value_base DecimalField The line cost if this transaction affects a revenue-style/AR account Nullable Optional
cogs_line_value_base DecimalField The line costs if this transaction affects the value of inventory/cogs Nullable Optional

sales_order_lines

Sales order lines, including for draft orders and orders which are not yet invoiced

Model: Sales_order_lines

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
order_guid ForeignKey
FK to dear_sales_header 		Nullable
order_reference CharField Nullable Optional
document_exrate FloatField From PO Header Nullable Optional
document_modified DateTimeField Nullable Optional
document_currency CharField Nullable Optional
base_currency CharField Nullable Optional
source_dear ForeignKey
FK to dear_metadata
line_type CharField Nullable Optional
product_guid ForeignKey
FK to dear_product 		Nullable Optional
description CharField Nullable Optional
order_quantity FloatField Nullable Optional
comment CharField Nullable Optional
backorder_quantity FloatField Nullable Optional
line_average_cost DecimalField Average cost (doc currency) used by Dear to estimate margin before picking Nullable Optional
unit_price_before_discount DecimalField Nullable Optional
unit_price_net DecimalField Nullable Optional
unit_actual_cogs_base_cur DecimalField Actual cogs (base cur) based on shipment, averaged across all shipments, if no shipments, then 0 Nullable Optional
unit_average_cost_base_cur DecimalField Average cost (base cur) Nullable Optional
unit_cogs_best_available DecimalField Actual COGS if available, otherwise average cost Nullable Optional
cogs_source CharField Source of margin: AverageCost or Actual Nullable Optional
line_total DecimalField Nullable Optional
line_tax_total DecimalField Nullable Optional
unit_price_before_discount_base_cur DecimalField Nullable Optional
unit_price_net_base_cur DecimalField Nullable Optional
line_total_base_cur DecimalField Nullable Optional
line_tax_total_base_cur DecimalField Nullable Optional
line_estimated_order_margin_base_cur DecimalField Nullable Optional
line_tax_rule CharField Nullable Optional
dropship BooleanField

sales_order_payments

Payments and deposits (prepayments) known to Dear for sales orders

Model: Sales_order_payments

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
source_dear ForeignKey
FK to dear_metadata
order_guid ForeignKey
FK to dear_sales_header
order_reference CharField Nullable Optional
doc_guid CharField GUID of the Task, ie the Invoice or Credit Note Nullable Optional
doc_number CharField Document number of the Task, ie the Invoice or Credit Note Nullable Optional
payment_guid CharField Nullable Optional
payment_type CharField Prepayment,InvoicePayment,Refund Nullable Optional
payment_reference CharField Nullable Optional
payment_amount DecimalField Always positive even for refunds Nullable Optional
payment_amount_signed DecimalField negative for refunds Nullable Optional
payment_date DateField No timezone Nullable Optional
payment_account CharField Nullable Optional
base_currency CharField Nullable Optional
payment_currency CharField Nullable Optional
payment_exrate FloatField Nullable Optional

sales_shipments

Information specifically about shipments, similar to the sales_order_inventory_transactions table, but only for shipments

Model: Sales_shipments

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
order_guid ForeignKey
FK to dear_sales_header
order_reference CharField Nullable Optional
source_dear ForeignKey
FK to dear_metadata
shipment_date DateField DateTime, no timezone Nullable Optional
shipment_carrier CharField The carrier used on the shipment line Nullable Optional
shipping_notes CharField Nullable Optional
product_guid ForeignKey
FK to dear_product 		Nullable Optional
warehouse CharField Nullable Optional
warehouse_guid CharField Nullable Optional
fulfilment_nbr IntegerField Nullable Optional
box CharField Nullable Optional
shipment_quantity FloatField Nullable Optional
cogs DecimalField Nullable Optional
cogs_date DateField Date field, no timezone Nullable Optional
shipped_value_base DecimalField Nullable Optional
task_id ForeignKey
FK to dear_sales_invoice 		Invoice task id (guid unique to invoice header) Nullable Optional
base_currency CharField Nullable Optional
tracking_nbr CharField tracking number Nullable Optional
tracking_url CharField tracking url Nullable Optional

shipping_subscriptions

OceanInsights Project44 data for containers on ships

Model: Shipping_Subscriptions

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
subscription_id CharField Unique
origin_name CharField Nullable Optional
destination_name CharField Nullable Optional
origin_latitude FloatField Nullable Optional
origin_longitude FloatField Nullable Optional
destination_latitude FloatField Nullable Optional
destination_longitude FloatField Nullable Optional
latitude FloatField Nullable Optional
longitude FloatField Nullable Optional
eta DateTimeField Nullable Optional
url CharField Nullable Optional
container_details CharField Nullable Optional
status CharField Nullable Optional
shipping_reference CharField Nullable Optional
subscription_name CharField Nullable Optional

shippit_transactions

This table supports Shippit transactional data, but this must be separately enabled.

Model: Shippit_transactions

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
order_guid IntegerField Nullable Optional
store CharField Nullable Optional
shippit_reference CharField Nullable Optional
receiver_name CharField Nullable Optional
recipient_contact_number CharField Nullable Optional
recipient_email CharField Nullable Optional
carrier CharField Nullable Optional
manifest_id CharField Nullable Optional
carrier_job_id CharField Nullable Optional
retailer_invoice CharField Nullable Optional
destination_address CharField Nullable Optional
destination_suburb CharField Nullable Optional
destination_postcode CharField Nullable Optional
destination_state CharField Nullable Optional
destination_country CharField Nullable Optional
destination_latitude FloatField Nullable Optional
destination_longitude FloatField Nullable Optional
special_instructions CharField Nullable Optional
atl CharField Nullable Optional
shipping_price DecimalField Nullable Optional
billed_by CharField Nullable Optional
count FloatField Nullable Optional
length FloatField Nullable Optional
width FloatField Nullable Optional
depth FloatField Nullable Optional
weight FloatField Nullable Optional
current_state CharField Nullable Optional
delivery_country_code CharField Nullable Optional
service_level CharField Nullable Optional
last_update DateTimeField Nullable Optional
order_placed DateTimeField Nullable Optional
despatch_in_progress DateTimeField Nullable Optional
ready_for_pickup DateTimeField Nullable Optional
in_transit DateTimeField Nullable Optional
with_driver DateTimeField Nullable Optional
delivery_attempted DateTimeField Nullable Optional
awaiting_collection DateTimeField Nullable Optional
completed DateTimeField Nullable Optional
expected_delivery_date DateTimeField Nullable Optional
carrier_quotes CharField Nullable Optional
rating CharField Nullable Optional
comment CharField Nullable Optional
case CharField Nullable Optional

table_update_status

During a data update with the Zoho connector, this table will show that a data update is in progress

Model: Table_update_status

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
source_dear CharField Nullable Optional
zoho_table_name CharField Nullable Optional
comment CharField Nullable Optional
time_of_comment_utc DateTimeField Nullable Optional
schema_version IntegerField Reserved for future use Nullable Optional

threepl_stock_analysis

Stores information about the stock levels of 3PLs. The table is unique by source_dear, stock_date, dear_location_od and product_guid

Model: ThreePL_stock_analysis

Fields

Field Name Type Description Properties
id AutoField Primary Key Unique Optional
analyticstable_ptr OneToOneField Primary Key Unique
source_dear ForeignKey
FK to dear_metadata
three_pl_name CharField Nullable Optional
dear_location_guid ForeignKey
FK to dear_location
threepl_location_name CharField Nullable Optional
product_guid ForeignKey
FK to dear_product 		Nullable Optional
stock_date DateField This is the date when the report is run. Nullable Optional
dear_timestamp DateTimeField time when the Dear stock level was recorded Nullable Optional
dear_stock_oh FloatField Dear Stock On Hand Nullable Optional
threepl_timestamp DateTimeField time when the 3PL stock level was recorded Nullable Optional
threepl_stock_oh FloatField 3PL Stock On Hand Nullable Optional
dear_available FloatField Dear Available stock Nullable Optional
dear_hard_available FloatField Dear Hard Available stock (on hand less hard allocation) Nullable Optional
threepl_available FloatField 3PL Available stock Nullable Optional
comment CharField Nullable Optional
Back to Top

Example SQL Queries

SQL Dialect Note: All example queries below use Zoho Analytics SQL syntax. For PostgreSQL, translate using these key differences:
  • DATETRUNC(column, 'month') → DATE_TRUNC('month', column)
  • DATEADD(date, interval, 'day') → date + INTERVAL 'N days'
  • CURRENT_DATE() → CURRENT_DATE
  • DATEDIFF(date1, date2, 'day') → date1 - date2
  • IFNULL(col, default) → COALESCE(col, default)

Topics

Sales

Sales orders, invoices, and revenue analysis from the Dear/Cin7 Core ERP

Related Tables: sales_fact, dear_customer, dear_product, dear_metadata, avg_ex_rate

Sales Line Items with Product Details

Basic sales data joining customer, product, and company metadata

SELECT
    "dear_metadata"."dear_company_name",
    "sales_fact"."order_number",
    "sales_fact"."transaction_date",
    "dear_customer"."customer_name",
    "dear_product"."sku",
    "dear_product"."product_category",
    "sales_fact"."line_revenue_base_currency"
FROM "sales_fact"
LEFT OUTER JOIN "dear_product" ON "dear_product"."product_guid" = "sales_fact"."product_guid"
LEFT OUTER JOIN "dear_customer" ON "dear_customer"."customer_guid" = "sales_fact"."customer_guid"
LEFT OUTER JOIN "dear_metadata" ON "dear_metadata"."id" = "sales_fact"."source_dear"
Notes: Multi-tenancy: Join to dear_metadata via source_dear to get company name. Use LEFT OUTER JOIN to include rows even when dimension data is missing. Filter by dear_company_name in WHERE clause if needed for specific tenant.
Monthly Sales Trend by Company

Sales revenue aggregated by month and company for trend analysis

SELECT
    "dear_metadata"."dear_company_name",
    DATETRUNC("sales_fact"."transaction_date", 'month') AS month,
    COUNT(DISTINCT "sales_fact"."order_number") AS order_count,
    SUM("sales_fact"."line_revenue_base_currency") AS revenue
FROM "sales_fact"
LEFT OUTER JOIN "dear_metadata" ON "dear_metadata"."id" = "sales_fact"."source_dear"
WHERE "sales_fact"."transaction_date" >= DATEADD(CURRENT_DATE(), -12, 'month')
GROUP BY "dear_metadata"."dear_company_name", DATETRUNC("sales_fact"."transaction_date", 'month')
ORDER BY "dear_metadata"."dear_company_name", month
Notes: Zoho Analytics syntax: DATETRUNC(column, 'month') for date truncation. Use DATEADD(CURRENT_DATE(), -12, 'month') for date arithmetic. Group by company name for multi-tenant comparison.
Top Selling Products by Category

Products ranked by revenue within each category

SELECT
    "dear_metadata"."dear_company_name",
    "dear_product"."product_category",
    "dear_product"."sku",
    SUM("sales_fact"."line_quantity") AS units_sold,
    SUM("sales_fact"."line_revenue_base_currency") AS revenue
FROM "sales_fact"
LEFT OUTER JOIN "dear_product" ON "dear_product"."product_guid" = "sales_fact"."product_guid"
LEFT OUTER JOIN "dear_metadata" ON "dear_metadata"."id" = "sales_fact"."source_dear"
WHERE "sales_fact"."transaction_date" >= DATEADD(CURRENT_DATE(), -30, 'day')
GROUP BY "dear_metadata"."dear_company_name", "dear_product"."product_category", "dear_product"."sku"
ORDER BY "dear_product"."product_category", revenue DESC
Notes: Join to dear_product for SKU and category details. line_quantity and line_revenue_base_currency are per-line values in sales_fact.
Sales with Currency Conversion

Convert base currency amounts to reporting currency using exchange rates

SELECT
    "sales_fact".*,
    mod(12 + month("sales_fact"."transaction_date") - month(current_date()), 12) AS sort_month,
    "avg_ex_rate"."in_reporting_currency",
    "sales_fact"."line_revenue_base_currency" * "in_reporting_currency" AS 'LineRev in ReportingCurrency',
    "sales_fact"."line_cogs_base_currency" * "in_reporting_currency" AS 'LineCOGS in ReportingCurrency',
    "sales_fact"."line_margin_base_currency" * "in_reporting_currency" AS 'LineMargin in ReportingCurrency',
    "MTDSalesVsLTMTD" * "in_reporting_currency" AS 'MTD_vs_HistoricalMTD',
    "revenue_quantity" / 52 AS 'Weekly ROS',
    ("revenue_quantity" / 52) * 4.33 AS 'Forecast'
FROM "sales_fact"
LEFT OUTER JOIN "avg_ex_rate" ON "avg_ex_rate"."year_month_currency" = "sales_fact"."year_month_currency"
Notes: Currency conversion: Join to avg_ex_rate on year_month_currency composite key. Multiply base currency columns by in_reporting_currency rate. sort_month uses mod() to create rolling 12-month sort order. Single quotes for column aliases with spaces in Zoho Analytics.

Purchasing

Purchase orders, supplier transactions, and procurement analysis

Related Tables: purchase_fact, purchase_line, dear_supplier, dear_metadata

Purchase Orders by Supplier

Total purchase value grouped by supplier

SELECT
    s."name" AS supplier_name,
    COUNT(DISTINCT pf."purchase_guid") AS po_count,
    SUM(pf."total") AS total_purchased
FROM "purchase_fact" pf
JOIN "dear_supplier" s ON pf."supplier_guid" = s."guid"
WHERE pf."source_dear" = 'your_entity'
  AND pf."order_date" >= DATEADD(CURRENT_DATE(), -90, 'day')
GROUP BY s."name"
ORDER BY total_purchased DESC
Notes: The purchase_guid links to purchase_line for line-item details.
Outstanding Purchase Orders

Purchase orders that are not yet fully received

SELECT
    pf."order_number",
    s."name" AS supplier_name,
    pf."order_date",
    pf."total",
    pf."status"
FROM "purchase_fact" pf
JOIN "dear_supplier" s ON pf."supplier_guid" = s."guid"
WHERE pf."source_dear" = 'your_entity'
  AND pf."status" NOT IN ('COMPLETED', 'VOIDED')
ORDER BY pf."order_date"
Notes: Check the actual status values in your data as they may vary by entity.
Supplier Lead Time Analysis

Average time between order and receipt by supplier

SELECT
    s."name" AS supplier_name,
    COUNT(*) AS completed_orders,
    AVG(DATEDIFF(pf."received_date", pf."order_date", 'day')) AS avg_lead_days
FROM "purchase_fact" pf
JOIN "dear_supplier" s ON pf."supplier_guid" = s."guid"
WHERE pf."source_dear" = 'your_entity'
  AND pf."received_date" IS NOT NULL
  AND pf."order_date" >= DATEADD(CURRENT_DATE(), -180, 'day')
GROUP BY s."name"
HAVING COUNT(*) >= 3
ORDER BY avg_lead_days
Notes: Zoho Analytics syntax: DATEDIFF(date1, date2, 'day') for date difference. Filter to suppliers with sufficient order history for meaningful averages.

Product_Movement

Inventory availability, stock levels by location, and warehouse operations

Related Tables: dear_product_availability, dear_product, dear_location, dear_metadata

Stock On Hand Pivot by Location

Pivot stock availability from rows to columns by warehouse location

SELECT
    dt1.sku AS 'SKU',
    sum(dt1."Australia") AS 'Australia',
    sum(dt1."Netherlands") AS 'Netherlands',
    sum(dt1."US") AS 'US',
    sum(dt1."Allure Labs") AS 'Allure',
    sum(dt1."Mona Vale Warehouse") AS 'Mona Vale'
FROM (
    SELECT
        "sku",
        if(location = 'AUSTRALIA AVAILABLE', "onhand", 0) AS 'Australia',
        if(location = 'NETHERLANDS AVAILABLE', "onhand", 0) AS 'Netherlands',
        if(location = 'US AVAILABLE', "onhand", 0) AS 'US',
        if(location = 'ALLURE LABS', "onhand", 0) AS 'Allure Labs',
        if(location = 'MV WAREHOUSE', "onhand", 0) AS 'Mona Vale Warehouse'
    FROM "dear_product_availability"
) AS dt1
GROUP BY dt1.sku
Notes: Pivot pattern: Use IF() in subquery to convert location rows to columns. dear_product_availability has sku, location, and onhand columns. Location values are uppercase strings like 'AUSTRALIA AVAILABLE'. Adjust location names and column aliases for your specific warehouses.
Product Availability by Location

Simple stock levels query from the availability table

SELECT
    "dear_product_availability"."sku",
    "dear_product"."product_name",
    "dear_product"."product_category",
    "dear_product_availability"."location",
    "dear_product_availability"."onhand"
FROM "dear_product_availability"
LEFT OUTER JOIN "dear_product" ON "dear_product"."sku" = "dear_product_availability"."sku"
WHERE "dear_product_availability"."onhand" > 0
ORDER BY "dear_product_availability"."sku", "dear_product_availability"."location"
Notes: dear_product_availability provides current stock snapshots. Join to dear_product for product details like name and category. Filter onhand > 0 to show only items in stock.
Low Stock Alert by Location

Products below reorder threshold at each location

SELECT
    "dear_metadata"."dear_company_name",
    "dear_product_availability"."sku",
    "dear_product"."product_name",
    "dear_product_availability"."location",
    "dear_product_availability"."onhand",
    "dear_product"."reorder_level",
    "dear_product"."reorder_level" - "dear_product_availability"."onhand" AS shortage
FROM "dear_product_availability"
LEFT OUTER JOIN "dear_product" ON "dear_product"."sku" = "dear_product_availability"."sku"
LEFT OUTER JOIN "dear_metadata" ON "dear_metadata"."id" = "dear_product_availability"."source_dear"
WHERE "dear_product_availability"."onhand" < "dear_product"."reorder_level"
  AND "dear_product"."reorder_level" > 0
ORDER BY shortage DESC
Notes: Compare onhand to reorder_level from dear_product. Filter reorder_level > 0 to exclude products without reorder settings.

Transactions

Inventory transactions, revenue vs COGS reconciliation, and order fulfillment tracking

Related Tables: sales_order_inventory_transactions, dear_product, dear_sales_header, SpecialReportParameters, dear_metadata

Revenue vs Inventory Quantity Mismatch

Find orders where revenue quantity differs from inventory quantity (reconciliation report)

SELECT
    "SpecialReportParameters"."Date1" AS "CutoffDate",
    "sales_order_inventory_transactions"."order_guid" AS order_guid,
    "sales_order_inventory_transactions"."order_number" AS order_reference,
    "sku",
    sum("signed_revenue_quantity") AS RevenueQty,
    sum("signed_inventory_quantity") AS InventoryQty,
    avg("rev_unit_value_base") AS "BaseCurRevenuePerUnit",
    avg("cogs_unit_value_base") AS "BaseCurCOGSPerUnit",
    if(sum("signed_revenue_quantity") - sum("signed_inventory_quantity") > 0,
       sum("signed_revenue_quantity") - sum("signed_inventory_quantity"), 0) AS ExcessRevQty,
    if(sum("signed_revenue_quantity") - sum("signed_inventory_quantity") < 0,
       -1 * (sum("signed_revenue_quantity") - sum("signed_inventory_quantity")), 0) AS ExcessCOGSQty,
    if(sum("signed_revenue_quantity") - sum("signed_inventory_quantity") > 0,
       (sum("signed_revenue_quantity") - sum("signed_inventory_quantity")) * avg("rev_unit_value_base"), 0) AS ExcessRevValue,
    if(sum("signed_revenue_quantity") - sum("signed_inventory_quantity") < 0,
       (sum("signed_revenue_quantity") - sum("signed_inventory_quantity")) * avg("cogs_unit_value_base"), 0) AS ExcessCOGSValue
FROM "sales_order_inventory_transactions"
JOIN "dear_product" ON "dear_product"."product_guid" = "sales_order_inventory_transactions"."product_guid"
JOIN "SpecialReportParameters" ON "SpecialReportParameters"."ReportName" = 'UnequalRevenueAndCogs'
JOIN "dear_sales_header" ON "dear_sales_header"."order_guid" = "sales_order_inventory_transactions"."order_guid"
WHERE "sales_order_inventory_transactions"."transaction_date" < "SpecialReportParameters"."Date1"
  AND "dear_product"."type" = 'Stock'
GROUP BY "SpecialReportParameters"."Date1",
         "sales_order_inventory_transactions"."order_guid",
         "sales_order_inventory_transactions"."order_number",
         "sku"
HAVING sum("signed_revenue_quantity") - sum("signed_inventory_quantity") != 0
Notes: Reconciliation report: finds orders with unequal revenue vs inventory quantities. Uses SpecialReportParameters table for configurable cutoff date. IF(condition, true_value, false_value) for conditional logic in Zoho Analytics. HAVING clause filters to only mismatched records after aggregation.
Inventory Transactions by Order

Summarize inventory movements grouped by sales order

SELECT
    "dear_metadata"."dear_company_name",
    "sales_order_inventory_transactions"."order_number",
    "sales_order_inventory_transactions"."transaction_date",
    "dear_product"."sku",
    "dear_product"."product_name",
    sum("signed_inventory_quantity") AS total_inventory_qty,
    sum("signed_revenue_quantity") AS total_revenue_qty,
    sum("signed_inventory_quantity" * "cogs_unit_value_base") AS total_cogs
FROM "sales_order_inventory_transactions"
LEFT OUTER JOIN "dear_product" ON "dear_product"."product_guid" = "sales_order_inventory_transactions"."product_guid"
LEFT OUTER JOIN "dear_metadata" ON "dear_metadata"."id" = "sales_order_inventory_transactions"."source_dear"
WHERE "sales_order_inventory_transactions"."transaction_date" >= DATEADD(CURRENT_DATE(), -30, 'day')
GROUP BY "dear_metadata"."dear_company_name",
         "sales_order_inventory_transactions"."order_number",
         "sales_order_inventory_transactions"."transaction_date",
         "dear_product"."sku",
         "dear_product"."product_name"
ORDER BY "sales_order_inventory_transactions"."transaction_date" DESC
Notes: sales_order_inventory_transactions tracks both revenue and inventory movements. signed_inventory_quantity and signed_revenue_quantity can be positive or negative. cogs_unit_value_base is the cost per unit for margin calculations.
Stock Product Transactions Only

Filter transactions to physical stock items (exclude services)

SELECT
    "sales_order_inventory_transactions"."order_number",
    "dear_product"."sku",
    "dear_product"."type",
    "dear_sales_header"."order_status",
    sum("signed_inventory_quantity") AS inventory_qty,
    sum("signed_revenue_quantity") AS revenue_qty
FROM "sales_order_inventory_transactions"
JOIN "dear_product" ON "dear_product"."product_guid" = "sales_order_inventory_transactions"."product_guid"
JOIN "dear_sales_header" ON "dear_sales_header"."order_guid" = "sales_order_inventory_transactions"."order_guid"
WHERE "dear_product"."type" = 'Stock'
  AND "dear_sales_header"."order_status" NOT IN ('CLOSED', 'VOIDED')
GROUP BY "sales_order_inventory_transactions"."order_number",
         "dear_product"."sku",
         "dear_product"."type",
         "dear_sales_header"."order_status"
Notes: dear_product.type filters to 'Stock' items (physical inventory). dear_sales_header.order_status filters out closed/voided orders. Use JOIN (not LEFT OUTER) when filtering on dimension attributes.
Back to Top