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.

There are query prompts for LLMs at the end of the document.

avg_ex_rate - Monthly average exchange rates from the ECB.
dear_customer - dear customer table
dear_customer_addresses - Addresses associated with Customers
dear_customer_contacts - Contacts associated with Customers
dear_customer_tag - Tags associated with Customers
dear_financial_transactions - Financial transactions from Cin7 Core (Dear). These are not auto updated.
dear_finished_goods_header - Finished Goods Header (Not fully implemented)
dear_location - Dear Locations (that is, warehouses, not bins)
dear_metadata - Dear Company Name: this table is useful if you have multiple Dear instances in the GrowthPath Analy…
dear_po_header - Purchase Order header
dear_product - Product details from Cin7 Core (Dear)
dear_product_availability - Product availability from Dear, plus hard allocations (qty reserved for a specfic order)
dear_product_custom_prices - Custom prices for products per customer.
dear_product_family - Product Family details
dear_product_tag - Tags associated with Products
dear_sales_header - Sales Header data
dear_sales_invoice - Sales Invoices and Credit notes header: invoices and credit notes.
dear_stock_adjustment_header - Stock Adjustment header
dear_stock_adjustment_lines - Stock Adjustment Lines
dear_stock_transfer_header - Stock Transfer
dear_stock_transfer_lines - Stock Transfer Lines
dear_supplier - Supplier details from Cin7 Core (Dear)
dear_supplier_product - Products linked to Suppliers
mainfreight_events - Mainfreight events
product_movements - All stock movements with quantity and date
purchase_order_lines - PO lines includng draft and not yet invoiced POs
purchase_order_payments - Payments relating to POs, including deposits and invoice payments
purchases_fact - based on authorised PO invoice lines
purchases_lines_lifecycle - Shows the status of a PO line (what's been invoiced, received etc)
sales_fact - Based on authorised invoice lines; the revenue figures in this table should agree with the data in …
sales_lines_lifecycle - Shows the lifecycle status of lines on the sales order: quantity and value picked, shipped, invoice…
sales_order_inventory_transactions - Inventory movements by date and value associated with the sales order, for detailed revenue and cog…
sales_order_lines - Sales order lines, including for draft orders and orders which are not yet invoiced
sales_order_payments - Payments and deposits (prepayments) known to Dear for sales orders
sales_shipments - Information specifically about shipments, similar to the sales_order_inventory_transactions table, …
shipping_subscriptions - OceanInsights Project44 data for containers on ships
shippit_transactions - This table supports Shippit transactional data, but this must be separately enabled.
table_update_status - During a data update with the Zoho connector, this table will show that a data update is in progress
threepl_stock_analysis - Stores information about the stock levels of 3PLs. The table is unique by source_dear, stock_date, …

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		Nullable Optional
customer_guid	CharField		Unique Nullable
customer_name	CharField		Nullable Optional
status	CharField		Nullable Optional
tags	CharField		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		Nullable Optional
tax_number	CharField		Nullable Optional
source_dear	ForeignKey FK to dear_metadata
attribute_set	CharField		Nullable Optional
cust_attribute1	CharField		Nullable Optional
cust_attribute2	CharField		Nullable Optional
cust_attribute3	CharField		Nullable Optional
cust_attribute4	CharField		Nullable Optional
cust_attribute5	CharField		Nullable Optional
cust_attribute6	CharField		Nullable Optional
cust_attribute7	CharField		Nullable Optional
cust_attribute8	CharField		Nullable Optional
cust_attribute9	CharField		Nullable Optional
cust_attribute10	CharField		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		Nullable Optional
customer_guid	ForeignKey FK to dear_customer		Nullable Optional
line1	CharField		Nullable Optional
line2	CharField		Nullable Optional
city	CharField		Nullable Optional
state	CharField		Nullable Optional
postcode	CharField		Nullable Optional
country	CharField	Must be an ISO standard country name	Nullable Optional
type	CharField		Nullable Optional
default_for_type	BooleanField

dear_customer_contacts

Contacts associated with Customers

Model: Dear_customer_contacts

Fields

Field Name	Type	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	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	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		Nullable Optional
source_dear	ForeignKey FK to dear_metadata
product_guid	CharField		Unique
type	CharField		Nullable Optional
sku	CharField		Nullable Optional
product_name	CharField		Nullable Optional
product_category	CharField		Nullable Optional
brand	CharField		Nullable Optional
average_cost	DecimalField		Nullable Optional
tags	CharField		Nullable Optional
uom	CharField		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		Nullable Optional
default_location	CharField		Nullable Optional
barcode	CharField		Nullable Optional
stock_locator	CharField		Nullable Optional
purchase_tax_rule	CharField		Nullable Optional
sale_tax_rule	CharField		Nullable Optional
weight	FloatField		Nullable Optional
weight_units	CharField		Nullable Optional
attribute_set	CharField		Nullable Optional
prod_attribute1	CharField		Nullable Optional
prod_attribute2	CharField		Nullable Optional
prod_attribute3	CharField		Nullable Optional
prod_attribute4	CharField		Nullable Optional
prod_attribute5	CharField		Nullable Optional
prod_attribute6	CharField		Nullable Optional
prod_attribute7	CharField		Nullable Optional
prod_attribute8	CharField		Nullable Optional
prod_attribute9	CharField		Nullable Optional
prod_attribute10	CharField		Nullable Optional
price_tier1	DecimalField		Nullable Optional
price_tier2	DecimalField		Nullable Optional
price_tier3	DecimalField		Nullable Optional
price_tier4	DecimalField		Nullable Optional
price_tier5	DecimalField		Nullable Optional
price_tier6	DecimalField		Nullable Optional
price_tier7	DecimalField		Nullable Optional
price_tier8	DecimalField		Nullable Optional
price_tier9	DecimalField		Nullable Optional
price_tier10	DecimalField		Nullable Optional
carton_length	DecimalField		Nullable Optional
carton_width	DecimalField		Nullable Optional
carton_height	DecimalField		Nullable Optional
carton_quantity	DecimalField		Nullable Optional
carton_inner_quantity	DecimalField		Nullable Optional
drop_ship_mode	CharField	No Drop Ship, Optional Drop Ship, Always Drop Ship	Nullable Optional
sellable	BooleanField
hscode	CharField		Nullable Optional
country_of_origin	CharField		Nullable Optional
country_of_origin_code	CharField		Nullable Optional
bom_type	CharField	read only. Assembly, Production, Make to Order, None	Nullable Optional
auto_assembly	BooleanField
auto_disassembly	BooleanField
qty_to_produce	DecimalField		Nullable Optional
cogs_account	CharField		Nullable Optional
revenue_account	CharField		Nullable Optional
expense_account	CharField		Nullable Optional
inventory_account	CharField		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	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	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		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		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		Nullable Optional
customer_guid	ForeignKey FK to dear_customer		Nullable Optional
sales_rep	CharField		Nullable Optional
source_channel	CharField	Source of the order	Nullable Optional
document_currency	CharField		Nullable Optional
document_exrate	FloatField		Nullable Optional
base_currency	CharField		Nullable Optional
warehouse	CharField		Nullable Optional
order_memo	TextField		Nullable Optional
sale_note	TextField		Nullable Optional
sale_contact	CharField		Nullable Optional
sale_contact_phone	CharField		Nullable Optional
sale_contact_email	CharField		Nullable Optional
order_type	CharField	Type of sale: Simple Sale or Advanced Sale, Service Sale	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
ship_by	DateField	Date, no timezone provided	Nullable Optional
customer_reference	CharField		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		Nullable Optional
refunded_ex_tax_base	DecimalField		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		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
shipping_notes	CharField		Nullable Optional
sale_carrier	CharField		Nullable Optional
billing_addr1	CharField		Nullable Optional
billing_addr2	CharField		Nullable Optional
billing_postalcode	CharField		Nullable Optional
billing_region	CharField		Nullable Optional
billing_town	CharField		Nullable Optional
billing_country	CharField		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	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	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	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	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		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		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		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		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		Nullable Optional
revenue_account	CharField		Nullable Optional
cogs_account	CharField	Not available at present	Nullable Optional
base_currency	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
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
year_month_currency	CharField		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		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		Nullable Optional
quoted_qty	FloatField		Nullable Optional
ordered_qty	FloatField		Nullable Optional
shipped_qty	FloatField	dropships are included in this total	Nullable Optional
shipped_with_tracking_qty	FloatField		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		Nullable Optional
drop_ship_qty	FloatField		Nullable Optional
credited_qty	FloatField		Nullable Optional
returned_qty	FloatField	Not implemented yet	Nullable Optional
avg_unit_price_base	DecimalField		Nullable Optional
quoted_basecur	DecimalField		Nullable Optional
ordered_basecur	DecimalField		Nullable Optional
shipped_basecur	DecimalField	valued at order price, not cogs, so that is is comparable	Nullable Optional
shipped_with_tracking_basecur	DecimalField		Nullable Optional
in_fulfilment_basecur	DecimalField		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		Nullable Optional
drop_ship_basecur	DecimalField		Nullable Optional
actual_cogs_per_unit	DecimalField		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		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	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	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
threepl_available	FloatField	3PL Available stock	Nullable Optional
comment	CharField		Nullable Optional

LLM Prompts

Basic sales report query

This schema allows advanced reporting on Cin7 Core (DEAR Systems). Use SQL and the sales_fact table to show basic invoice line details with transaction data, customer name and basic product data such as SKU and product category, using left outer joins to related tables. Using left outer joins is best practice in analytics reporting queries to make sure that fundamental financial data is not left out of the report because there is missing foreign key master data. In this Analytics data, not all products on invoice lines are defined in the dear_product table, so using an outer join is important.

Use the Zoho Analytics SQL dialect.

        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"

CIn7 Core GrowthPath Analytics Tables Documentation

Table of Contents

avg_ex_rate

Fields

dear_customer

Fields

dear_customer_addresses

Fields

dear_customer_contacts

Fields

dear_customer_tag

Fields

dear_financial_transactions

Fields

dear_finished_goods_header

Fields

dear_location

Fields

dear_metadata

Fields

dear_po_header

Fields

dear_product

Fields

dear_product_availability

Fields

dear_product_custom_prices

Fields

dear_product_family

Fields

dear_product_tag

Fields

dear_sales_header

Fields

dear_sales_invoice

Fields

dear_stock_adjustment_header

Fields

dear_stock_adjustment_lines

Fields

dear_stock_transfer_header

Fields

dear_stock_transfer_lines

Fields

dear_supplier

Fields

dear_supplier_product

Fields

mainfreight_events

Fields

product_movements

Fields

purchase_order_lines

Fields

purchase_order_payments

Fields

purchases_fact

Fields

purchases_lines_lifecycle

Fields

sales_fact

Fields

sales_lines_lifecycle

Fields

sales_order_inventory_transactions

Fields

sales_order_lines

Fields

sales_order_payments

Fields

sales_shipments

Fields

shipping_subscriptions

Fields

shippit_transactions

Fields

table_update_status

Fields

threepl_stock_analysis

Fields