...
| Table of Contents | ||||
|---|---|---|---|---|
|
Accounting Period Details (Required but we can generate)
Field Name | Type | Additional Info |
|---|---|---|
YearName | TEXT | Required |
PeriodNo | TEXT | Required |
PeriodName | TEXT | Required |
StartDate | DATE | Required |
EndDate | DATE | Required |
Customers
Field Name | Type | Additional Info |
|---|---|---|
Customer_ID | TEXT | Required |
Name | TEXT | Required |
Brand | TEXT | E.g. “Retail”, “Trade” |
Currency_ID | TEXT | |
Status | TEXT | E.g. “Active”, “Dormant” |
Type | TEXT | Text of “customer” or “lead” |
Sales_Notes | TEXT | Any notes relevent to the customer |
Credit_Limit | REAL | |
Current_Balance | REAL | |
Overdue_1 | REAL | Ageing, e.g. 0-30 |
Overdue_2 | REAL | Ageing, e.g. 31-60 |
Overdue_3 | REAL | Ageing, e.g. 61-90 |
Overdue_4 | REAL | Ageing, e.g. 91-120 |
Overdue_5 | REAL | Ageing, e.g. 121+ |
Average_Payment_Days | REAL | |
Sales_Target_This_Year | REAL | |
Service_Level | TEXT | |
Trading_Status | TEXT | |
Turnover_Band | TEXT | |
Analysis_1 | TEXT | E.g. customer category |
Analysis_2 | TEXT | E.g. region |
Analysis_3 | TEXT | |
Analysis_4 | TEXT | |
Analysis_5 | TEXT | |
Sales_Rep_ID | TEXT | |
Sales_Rep_Name | TEXT | |
Date_Created | DATE | YYYY-MM-DD |
Customer Locations
Field Name | Type | Additional Info |
|---|---|---|
Customer_ID | TEXT | Required |
Address_ID | TEXT | Required |
Main_Location | TEXT | “true” or “false” |
Address_Reference | TEXT | |
Address_Name | TEXT | |
Address_Line_1 | TEXT | |
Address_Line_2 | TEXT | |
Address_Line_3 | TEXT | |
Town | TEXT | |
County | TEXT | |
Postcode | TEXT | |
Country | TEXT | |
Latitude | TEXT | |
Longitude | TEXT | |
Sales_Rep_ID | TEXT | |
Sales_Rep_Name | TEXT | |
Contact_Name | TEXT | If not supplying separately |
Contact_Email | TEXT | If not supplying separately |
Contact_Telephone | TEXT | If not supplying separately |
Contact_Mobile | TEXT | If not supplying separately |
Contact_Website | TEXT | If not supplying separately |
Contact_Other | TEXT | If not supplying separately |
Customer Contacts (If not supplying contacts against location)
Field Name | Type | Additional Info |
|---|---|---|
Customer_ID | TEXT | Required |
Contact_ID | TEXT | Required |
Address_ID | TEXT | Required |
Type | TYPE | Required “tel” or “email” or “mobile” |
Contact_Type | TEXT | E.g. “Sales”, “Accounts” |
Name | TEXT | |
Value | TEXT | E.g. telephone number |
Customer Trading Status (Optional)
Field Name | Type | Additional Info |
|---|---|---|
Customer_ID | TEXT | Required |
Trading_Status | TEXT | |
Previous_Trading_Status | TEXT | |
Date_Last_Change | DATE | YYYY-MM-DD |
Customer Turnover Band (Optional)
Field Name | Type | Additional Info |
|---|---|---|
Customer_ID | TEXT | Required |
Turnover_Band | TEXT | |
Previous_Turnover_Band | TEXT | |
Date_Last_Change | DATE | YYYY-MM-DD |
Customer Transactions (Optional)
Field Name | Type | Additional Info |
|---|---|---|
Customer_ID | TEXT | Required |
Transaction_ID | TEXT | Required |
Reference | TEXT | Required Invoice/Credit number |
Transaction_Date | DATE | YYYY-MM-DD |
Due_Date | DATE | YYYY-MM-DD |
Type | TEXT | Required “invoice” or “credit” |
Value | REAL | Outstanding Value |
Currency_ID | TEXT |
Invoice Headers
Field Name | Type | Additional Info |
|---|---|---|
Invoice_ID | TEXT | Required |
Type | TEXT | Required “invoice” or “credit” |
Document_No | TEXT | Required |
Order_ID | TEXT | Link to order header |
Description | TEXT | |
Customer_ID | TEXT | Required |
Customer_Name | TEXT | Required |
Delivery_Address_Name | TEXT | |
Invoice_Date | DATE | Required YYYY-MM-DD |
Customer_Order_Ref | TEXT | |
Channel_ID | TEXT | |
Channel_Description | TEXT |
Invoice Lines
Field Name | Type | Additional Info |
|---|---|---|
Invoice_ID | TEXT | Required |
Line_ID | TEXT | Required |
Product_ID | TEXT | Required |
Product_Description | TEXT | Required |
Quantity | REAL | Required |
Base_Unit_Price | REAL | Required |
Base_Line_Price_Net | REAL | Required |
Base_Line_Price_VAT | REAL | Required |
Base_Line_Price_Gross | REAL | Required |
Unit_Cost | REAL | |
Line_Cost | REAL |
Order Header (Optional)
Field Name | Type | Additional Info |
|---|---|---|
Order_ID | TEXT | Required |
Customer_ID | TEXT | Required |
Customer_Name | TEXT | Required |
Channel_ID | TEXT | |
Channel_Description | TEXT | |
Delivery_Address_Name | TEXT | |
Delivery_Address_Line_1 | TEXT | |
Delivery_Address_Line_2 | TEXT | |
Delivery_Address_Line_3 | TEXT | |
Delivery_Town | TEXT | |
Delivery_County | TEXT | |
Delivery_Postcode | TEXT | |
Delivery_Country | TEXT | |
Order_Date | DATE | Required YYYY-MM-DD |
Delivery_Date | DATE | Required YYYY-MM-DD |
Customer_Order_Ref | TEXT | |
Internal_Notes | TEXT | |
External_Notes | TEXT | |
Type | TEXT | Required “order”, “quote” |
Status | TEXT | Status of the order, e.g. “picked” |
Hold_Status | TEXT | Reason for hold |
Created_By | TEXT | |
Source | TEXT | e.g. Caliq, Madics, Sage |
Order Lines (Optional)
Field Name | Type | Additional Info |
|---|---|---|
Order_ID | TEXT | Required |
Line_ID | TEXT | Required |
Product_ID | TEXT | Required |
Product_Description | TEXT | Required |
Unit_Price | REAL | Required |
Original_Price | REAL | |
Quantity | REAL | Required |
Quantity_Outstanding | REAL | |
Line_Value | REAL | Required |
Value_Outstanding | REAL | |
Warehouse_Value | REAL | |
Allocated_Value | REAL | |
Required_Date | DATE | YYYY-MM-DD |
Estimated_Date | DATE | YYYY-MM-DD |
Sort | TEXT | Line order sequence |
Unit_Cost | REAL | |
Line_Cost | REAL |
Sales Rep Forecasts (Optional)
Field Name | Type | Additional Info |
|---|---|---|
Sales_Rep_ID | TEXT | Required |
Sales_Rep_Name | TEXT | |
Accounting_Year | TEXT | Required |
Accounting_Period | TEXT | Required |
Sales_Target | REAL | |
Sales_Forecast | REAL |
Products (Required)
Field Name | Type | Additional Info |
|---|---|---|
Product_ID | TEXT | Required |
Product_Description | TEXT | Required |
Product_Status | TEXT | |
On_Toucan | TEXT | “True”, “False” |
List_Price | REAL | Required |
Cost_Price | REAL | |
Quantity_Warehouse | REAL | |
Quantity_Available | REAL | |
Quantity_Allocated | REAL | |
Quantity_Picked | REAL | |
Next_Purchase_Order_Date | DATE | |
Next_Purchase_Order_Quantity | REAL | |
Quantity_Inner | REAL | |
Quantity_Outer | REAL | |
Quantity_Pallet | REAL | |
Quantity_Default | REAL | |
Analysis_1 | TEXT | E.g. product group 1 |
Analysis_2 | TEXT | E.g. product group 2 |
Analysis_3 | TEXT | E.g. product category |
Analysis_4 | TEXT | |
Analysis_5 | TEXT | |
Analysis_6 | TEXT | |
Analysis_7 | TEXT | |
Analysis_8 | TEXT | |
Analysis_9 | TEXT | |
Analysis_10 | TEXT |
...