Skip to end of banner Go to start of banner

Toucan Reporting CSV Structures

Skip to end of metadata

Created by Steve Evans on Oct 05, 2023

Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Below are the required structures for Toucan Reporting data.

Each are must be in a separate file and can be called anything.
The delimiter can be any character.
Apart from the required fields, you only need to supply the fields you’ve got data for, e.g. if Sales_Notes against the customer isn’t available, don’t supply the header field name
All values have to be in base currency
If you cannot supply a file, e.g. order headers, there is no need to supply an empty file

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

No labels