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.

Use_Std_Terms

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.

Terms_DueDays

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.

Customer_Bill_Name

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.

What do you want to do next?