Table of contents
Invoice Forms

• Tables
  • CUSTOMERS
  • DISCOUNT
  • INVOICE
  • ITEMS
  • SHIP_TO
  • SHIPPERS
  • SOLD
  • TAX
• Queries
  • Items Sold
  • Items UnSold
  • qSubForm_Adv_Discount
  • qSubForm_Adv_Shipping
  • qSubForm_Adv_Tax
  • qSubForm_Adv_Total
  • qSubForm_SubTotal
  • qSubForm_Tax
  • qSubForm_Total
• Forms
  • 1. Invoice Unique Items
  • 2. Invoice Basic
  • 3. Invoice Intermediate
  • 4. Invoice Advanced
• Index

List of fields by query

Query name	Field name	Field type	Source table	Source field
Items Sold	CUSTOMER			"FIRSTNAME" || ' ' || "LASTNAME"
	DATE	DATE	INVOICE (Table)	DATE
	INVOICE	INTEGER	INVOICE (Table)	ID
	ITEM	VARCHAR	ITEMS (Table)	ITEM
	PRICE	DECIMAL	ITEMS (Table)	PRICE
	QTY	INTEGER	SOLD (Table)	QTY
	SERIAL	VARCHAR	ITEMS (Table)	SKU
Items UnSold	ITEM	VARCHAR	ITEMS (Table)	ITEM
	ITEM_ID	INTEGER	ITEMS (Table)	ITEM_ID
	PACKAGING_FEE	DECIMAL	ITEMS (Table)	PACKAGING_FEE
	PRICE	DECIMAL	ITEMS (Table)	PRICE
	SHIPPING_WT	DECIMAL	ITEMS (Table)	SHIPPING_WT
	SKU	VARCHAR	ITEMS (Table)	SKU
	TAXABLE	BOOLEAN	ITEMS (Table)	TAXABLE
qSubForm_Adv_Discount	INVOICE_DISCOUNT			- CASEWHEN( "INVOICE"."DISCOUNT_ID" = 0, 0, "SUBTOTAL" * ( ABS( "UNITS" / 100 ) ) )
	INVOICE_ID	INTEGER	INVOICE (Table)	ID
qSubForm_Adv_Shipping	INVOICE_ID	INTEGER	SOLD (Table)	INVOICE_ID
	SHIPPING			SUM( CASEWHEN( "ITEMS"."TAXABLE" = 1, CASEWHEN( "SHIPPERS"."ID" > 0, COALESCE ( "SHIPPERS"."BASE_FEE", 0 ) + ( ( COALESCE ( "ITEMS"."PACKAGING_FEE", 0 ) + ( COALESCE ( "ITEMS"."SHIPPING_WT", 0 ) * COALESCE ( "SHIPPERS"."WEIGHT_RATE", 0 ) ) ) * "SOLD"."QTY" ), 0 ), 0 ) )
qSubForm_Adv_Tax	INVOICE_ID	INTEGER	SOLD (Table)	INVOICE_ID
	TAXES			SUM( CASEWHEN( "ITEMS"."TAXABLE" = 1, ( "ITEMS"."PRICE" - ABS( COALESCE ( "SOLD"."DISC_CURRENCY", 0 ) ) ) * ( 1 - ABS( COALESCE ( "SOLD"."DISC_PERCENT", 0 ) ) ) * "SOLD"."QTY" * "TAX"."RATE" * ( CASEWHEN( "DISCOUNT"."ID" = 0, 1, 1 - ( "DISCOUNT"."UNITS" / 100 ) ) ), 0 ) )
qSubForm_Adv_Total	INVOICE_ID	INTEGER	qSubForm_SubTotal (Query)	INVOICE_ID
	TOTAL			COALESCE ( "SUBTOTAL", 0 ) + COALESCE ( "INVOICE_DISCOUNT", 0 ) + COALESCE ( "TAXES", 0 ) + COALESCE ( "SHIPPING", 0 )
qSubForm_SubTotal	INVOICE_ID	INTEGER	SOLD (Table)	INVOICE_ID
	SUBTOTAL			SUM( ( "ITEMS"."PRICE" - ABS( COALESCE ( "SOLD"."DISC_CURRENCY", 0 ) ) ) * "SOLD"."QTY" * ( 1 - ABS( COALESCE ( "SOLD"."DISC_PERCENT", 0 ) ) ) )
qSubForm_Tax	INVOICE_ID	INTEGER	SOLD (Table)	INVOICE_ID
	TAXES			SUM( CASEWHEN( "ITEMS"."TAXABLE" = 1, ( "ITEMS"."PRICE" - ABS( COALESCE ( "SOLD"."DISC_CURRENCY", 0 ) ) ) * ( 1 - ABS( COALESCE ( "SOLD"."DISC_PERCENT", 0 ) ) ) * "SOLD"."QTY" * "TAX"."RATE", 0 ) )
qSubForm_Total	INVOICE_ID	INTEGER	qSubForm_SubTotal (Query)	INVOICE_ID
	TOTAL			"SUBTOTAL" + "TAXES"