Customer Fields
The following field information is intended assist those who design reports for Sage 50 using Crystal Reports.
Filename: CUSTOMER.DAT
Customer.DAT is the customer file. It contains a row (record) for each customer. Most of the information below is entered or displayed in Sage 50's Maintain Customers/Prospects window. Use the CustomerRecordNumber to index into ADDRESS.DAT, which contains customer address information.
- To add Sage 50 customer fields to your Crystal reports, select CUSTOMER.DAT.
Indexes and Their Uses
CUSTOMER.DAT has 16 keys, as follows:
0 = By CustomerID. This field is unique but can change. It is most useful for building reports sorted by CustomerID.
1 = By Customer Name
2 = Active/Inactive and then by CustomerID
3 = Active/Inactive and then by Customer Name
4 = Customer Record Number. This is how other tables link with customers.
5 = Customer ID and then Customer Name and then Customer eMail Address. This is most useful for reading via KeyOnly
6 = GUID
7 = Customer type, case sensitive
8 = Active/Inactive and then by Customer Type, case sensitive
9 = Customer Name, case sensitive
10 = Active/Inactive and then by Customer Name, case sensitive
11 = Custom Field 1
12 = Custom Field 2
13 = Custom Field 3
14 = Custom Field 4
15 = Custom Field 5
Fields
The following table shows the fields available in the CUSTOMER.DAT file and includes the type and size in number of bytes of each field.
Field Name |
Type |
Size |
Comment |
---|---|---|---|
CustomerID |
ZString |
20 |
Customer ID entered in Maintain Customers/Prospects window. This record ID is unique, but it can change (if Sage 50's Change ID feature is used). For customer name, see Bill_Name (below). |
Customer_Type |
ZString |
8 |
Customer type entered on the General tab of the Maintain Customers/Prospects window. |
Bill-to Contact |
ZString |
20 |
First Contact name entered on the Contacts tab of the Maintain Customers/Prospects window. |
Phone_Number |
ZString |
20 |
Telephone 1 entered on the General tab of the Maintain Customers/Prospects window. |
FAX_Number |
ZString |
20 |
Fax number entered on the General tab of the Maintain Customers/Prospects window. |
SalesTaxResaleNum |
ZString |
20 |
Resale number entered on the Sales Defaults tab of the Maintain Customers/Prospects window. Use this field when the customer is exempt from sales tax because he or she is a reseller. |
AccountNumber |
ZString |
20 |
The field is reserved for an account number that this customer uses to identify your company. Note: This field is not currently used or available in the Sage 50 application. |
CustomerUnused0 |
Logical |
Do not select or use this field. It is used by Sage 50 for internal reasons. |
|
Logical |
The Use Standard Terms option on the Sales Defaults tab of the Maintain Customers/Prospects window. TRUE => use the standard sales payment terms set up in the Customer Defaults window (not currently available for GENERAL.DAT_AR). FALSE => use payment terms set up for the specific customer record (see below). |
||
GLAcntNumber |
ZString |
15 |
Default G/L sales (income) account for all transactions entered in Quotes, Sales/Invoicing, and Receive Money windows that do not reference inventory items Tip: Use this field as a key into the CHART.DAT file. |
GUID |
UBinary |
GUID (Globally Unique Identifier) used for linking the Customer table. |
|
Balance |
Float |
The current balance due by this customer. Note: This balance includes all unpaid invoices, including those entered in the future. |
|
CustomerSince |
Date |
The Customer Since date entered or displayed on the History tab of the Maintain Customer/Prospects window. |
|
Sales1 [1-42] |
Decimal |
12 |
Total sales recorded for this customer in the specified period. Period 1 = an internal field (do not use); Period 2 = Period 1 of the last closed fiscal year; Period 14 = for a 13th period, used by some companies; Period 15 = Internal; Period 16 = period 1 of this fiscal year; Period 29 = Internal; Period 30 = Period 1 of next fiscal year. |
Payments1 [1-42] |
Decimal |
12 |
Total payments recorded for this customer in the specified period. Period 1 = an internal field (do not use); Period 2 = Period 1 of the last closed fiscal year; Period 14 = for a 13th period, used by some companies; Period 15 = Internal; Period 16 = period 1 of this fiscal year; Period 30 = Period 1 of next fiscal year. |
LastInvoicedate |
Date |
Last sales invoice date recorded for this customer. |
|
Last_Invoice_Amt |
Float |
Last sales invoice amount recorded for this customer. |
|
Last_Pmnt_Date |
Date |
Last receipt date for this customer. |
|
Last_Pmnt_Amnt |
Float |
Last receipt amount collected from this customer. |
|
LastStatementDate |
Date |
Last statement date (printed) for this customer. |
|
Integer |
Number of days before invoice balance is due. Note: About Customer Payment Terms: These are set up on the Sales Defaults tab of the Maintain Customers/Prospects window. Terms fields listed below are used if the Use Standard Terms option is not selected (Use_Std_Terms => False). |
||
Terms_DiscDays |
Integer |
Number of days early payment discount is available. |
|
Terms_DiscountPcnt |
Float |
Discount percentage offered if paid early (within Terms_DiscDays) |
|
Terms_CreditLimit |
Float |
Customer credit limit. |
|
Terms_ChgInterest |
Integer |
1 |
Charge finance charges option. Finance charges are set up in the Custom Defaults window. 1 => Allow finance charges to accumulate for past due balances in this customer's account. 0 => Don’t charge finance charges to this customer's account. |
Terms_COD |
Integer |
1 |
True => Cash on delivery payment term option is selected. |
Terms_Prepay |
Integer |
1 |
True => Prepayment required term option is selected. |
TermsDaysAreActual |
Integer |
1 |
1 => Due on Day of Month payment term option is selected (Terms_DueDays is actual day of (next) month that invoice is due) 0 => Due in # Days payment term option is selected (Terms_DueDays is number of days before invoice is due). |
TermsDueAtMonthEnd |
Integer |
1 |
1 => Due at end of month payment term option is selected. |
Terms_Unused |
ZString |
Do not select or use this field. It is used by Sage 50 for internal reasons. |
|
CustomerIsInactive |
Integer |
1 |
The Inactive check box in the Maintain Customers/Prospects window. Valid choices include: True = if the customer record is inactive. False = if the customer record is active. |
Phone_Number2 |
ZString |
20 |
Telephone 2 entered on the General tab of the Maintain Customers/Prospects window. |
ZString |
30 |
Customer name entered in the Maintain Customers/Prospects window. This name is often grouped with the customer's Bill-to address (see below). |
|
CustomField1 |
ZString |
40 |
Data entered in first customer custom field. Note: About Customer Custom Fields: Custom(izable) field data is entered on the General tab of the Maintain Customers/Prospects window. Labels for customer custom fields are set up on the Custom Fields tab of the Customer Defaults window. To get the custom field names, you must use a custom formula. Tell me more about this. |
CustomField2 |
ZString |
40 |
Data entered in second customer custom field. |
CustomField3 |
ZString |
40 |
Data entered in third customer custom field. |
CustomField4 |
ZString |
40 |
Data entered in fourth customer custom field. |
CustomField5 |
ZString |
40 |
Data entered in fifth customer custom field. |
EmpRecordNumber |
Integer |
4 |
Default sales rep ID associated with the customer and selected on the Sales Defaults tab of the Maintain Customers/Prospects window. |
OpenPO |
ZString |
20 |
The standing purchase order number used for this customer and entered on the Sales Defaults tab of the Maintain Customers/Prospects window. This defaults on the Quotes, Sales Orders, and Sales/Invoicing windows. |
WhichShipVia |
Integer |
Identifies the number of default Ship Via option selected for this customer on the Sales Defaults tab of the Maintain Customers/Prospects window. Valid options are 1 to 10. Ship (via) methods are set up in the Inventory Defaults window. |
|
IsProspect |
Integer |
1 |
Prospect option in Maintain Customers/Prospects window. 1 (TRUE) => This is not really a customer, just a prospect (customer ID not included on any customer reports). 0 (FALSE) => This is a full customer, which can be invoiced (customer ID will be listed in all customer reports). |
PriceLevel |
Integer |
Identifies the number of default sales price level option selected for this customer on the Sales Defaults tab of the Maintain Customers/Prospects window. Valid options are 1 to 5. Price level amounts are set up for each inventory item in the Maintain Inventory Items window. |
|
eMail_Address |
ZString |
64 |
Email address for this customer. |
CustomerRecordNumber |
Integer |
This (internal) customer field is used for linking purposes to other Sage 50 data tables. For more information, see Indexes and Their Uses above. This field is unique in the table and never changes. |
|
WEB_Address |
ZString |
254 |
Web site address (URL) for this customer. |
BillPresentmentEnabl |
Logical |
1 |
Bill presentment setting for the customer in the Sage 50 Web Bill Presentment Center. TRUE => This customer is activated for bill presentment. FALSE => This customer is not activated for bill presentment. Note: This field is not currently used or available in the Sage 50 application. |
UsePaymentDefaults |
UBinary |
||
Cardholder_Name |
ZString |
39 |
Customer's cardholder's name as it appears on the credit card in the Cardholder's Name field. |
Cardholder_Address1 |
ZString |
30 |
Customer's cardholder's address, line 1. This defaults from the Bill to Address fields on the General tab of the Maintain Customers/Prospects window. |
Cardholder_Address2 |
ZString |
30 |
Customer's cardholder's address, line 2. This defaults from the Bill to Address fields on the General tab of the Maintain Customers/Prospects window. |
Cardholder_City |
ZString |
20 |
The city of the customer's cardholder's address. |
Cardholder_State |
ZString |
2 |
The state of the customer's cardholder's address. |
Cardholder_ZIP |
ZString |
12 |
The ZIP code of the customer's cardholder's address. |
Cardholder_Country |
ZString |
15 |
The country of the customer's cardholder's address. |
CcStoredAcctRef |
UBinary |
Identifies the vault where the credit card information is stored |
|
CcMaskedNumber |
ZString |
30 |
The masked customer credit card number. |
CcExpireDate |
Date |
The credit card expiration date. |
|
UsePMAndCAPaymentDef |
Logical |
||
DefaultPaymentsMethod |
ZString |
20 |
|
DefCashAcct_RecNum |
UBinary |
This is a record number that can be used to look up the default cash account in CHART.DAT. |
|
FlagsFormDelivery |
UBinary |
This customer is set up for forms delivery by email. |
|
LastUpdateCounter |
UBinary |
This increases incrementally when the customer record is changed. |
|
CreditStatus |
UBinary |
This is the customer's credit status set up in the Credit Status field of the Maintain Customers Terms and Credit tab. 0 => No Credit Limit 1 => Notify Over Limit 2 => Always Notify 3 => Hold Over Limit 4 => Hold. |
|
ReplaceItemIDFormOpt |
UBinary |
||
NumOfPaidInvoices |
UBinary |
||
DaysToPayInvoices |
Integer |
||
LastSavedAt |
TimeStamp |
Timestamp containing the date and time of the last time this customer was saved |
|
CreditStatusMsg |
ZString |
250 |
Credit Status - Notification text field |
ACHStoredAcctRef |
Ubinary |
16 |
Identifies the ACH vault record. |
MaskedBankAcctNum |
ZString |
30 |
The masked bank account number. |