Postcode Programming API  PostTrans XL Visual Importer: Import Exchequer Customer/Supplier records


This PostTrans spreadsheet allows the user to easily Import/Extract/Amend Customer/ Supplier data in Exchequer Enterprise.   It uses a series of tags, placed in row 1, to identify the data to be Imported/Extracted/Amended.  These tags can be easily added to a spreadsheet using the button, on the PostTrans Toolbar.  The data is read from row 3 onwards, which allows you to put user descriptions in row 2.  more Information on Designing templates.

  Demonstration Video:


            Show me a demonstration video

The "Customers and Stock" Example shows this in action. more information on PostTrans examples


Features:


Extracting Customer/Supplier Data from Exchequer Enterprise

To extract Customer/Supplier data press button, on the PostTrans Toolbar, this will allow you to add criteria to the lookup:

Exchequer Enterprise get Customer and Supplier

Here we are going to get all customers with "SAM" in user field 1.  We then press [Clear and Lookup Values] or [Add Lookup Values] button to populate the spreadsheet with data Exchequer Enterprise Customer/Supplier records.

IMPORTANT : The [Add Lookup Values] will not detect potential duplicate records added to the spreadsheet.

Exchequer Enterprise Customer Import

 

Amending the Exchequer Enterprise Customer/Supplier Data

Having extracted data, the user can now simply edit the Enterprise data, before re-importing it back to Exchequer Enterprise.  

Importing new Exchequer Enterprise Customer/Supplier Data

Simply add new lines to the spreadsheet from row 3 onwards.  Then press button, on the PostTrans Toolbar,  to import Exchequer Enterprise data.  

Customer/Supplier Required fields

To Import/Extract/Amend Exchequer Enterprise Customer/Supplier Data, the following Tags/Fields must be specified:

ACPosted, ErrorDesc, AccountType, AccountCode and AccountName

Cell A1 must equal ACPosted

AccountCode is used to detect end of spreadsheet.  PostTrans will stop Importing/Amending, from row 3 onwards, when this field is empty.

Restrictions

Once imported it is NOT possible to change a customer to a supplier.  Or visa versa.


Exchequer Customer/Supplier fields available for update:


The following Tags are used to identify the corresponding fields in Exchequer Enterprise.  Use the [Design] button (or Ctrl+Shift+D), on the PostTrans Toolbar, to access these tags.   Click here for more Information on Designing templates.

Tag Name

Data Type Length

Description

Required:

 

 

ACPosted

Yes/No

Returns 'POSTED' if Trans line has been posted and will be ignored if 'POSTED' (required)

ErrorDesc

Text

Description of error posted back if record unsuccessfully posted

AccountType

Text 1

Must be C or S (required)

AccountCode

Text 6

If exists then updates record (required)

AccountName

Text 45

Account Name (required)

Credit Terms:

 

 

AccountStatus

Text 1

0-3 (0=Open,1=Notes,2=On Hold,3=Closed)

CreditLimit

Double

 

PayDays

Integer

0-999 (see Ex Ref. Manual)

SettlementDiscDays

Long Integer

Settlement Discount Days

SettlementDiscount

Double

Settelment Discount

Other Identifiers:

 

 

AccountAltCode

 20

Alternative Cust/Supp code

AreaCode

Text 4

Area Code

AccountTypeCode

Text 4

Account Type Code

TheirAccForUS

Text 10

 

VATReg

Text 30

VAT Reg. No

Address:

 

 

Add1

Text 30

Address line 1

Add2

Text 30

Address line 2

Add3

Text 30

Address line 3

Add4

Text 30

Address line 4

Add5

Text 30

Address line 5

Postcode

Text 20

Address postcode

Country

Text 2

Main Address 2 digit Country Code

Contact

Text 25

 

Phone

Text 30

 

Phone2

Text 30

 

Fax

Text 30

 

e-mail

Text 100

e-mail address

Delivery Address:

 

 

DelAdd1

Text 30

Delivery Address line 1

DelAdd2

Text 30

Delivery Address line 2

DelAdd3

Text 30

Delivery Address line 3

DelAdd4

Text 30

Delivery Address line 4

DelAdd5

Text 30

Delivery Address line 5

DelPostcode

Text 30

Delivery Postcode

DelCountry

Text 2

Delivery Address 2 digit Country Code

Defaults:

 

 

Currency

Byte

0-29,1=Sterling

VATCode

Text 1

S,Z,E,1,2,3,4,5,6,7,8,9,A,D,T,X

DefaultCostCentre

Text 3

Default Cost Centre

DefaultDepartment

Text 3

Default Department

LocationCode

Text 3

Default Sales General Ledger Code

TagNo

Long Integer

Tag Number

FormSet

Long Integer

No 0-99

SOP Defaults:

 

 

StatementTo

Text 6

Account code which points to Remittance address record if this record is used as delivery address. SPOP only.

InvoiceTo

Text 6

Account code which points to Invoice address record if this record is used as delivery address. SPOP only.

SOPAutoWrittenOff

Text 1

Y/N (SPOP only.)

OrderConsMode

Long Integer

Order Consolidation mode 0-8

AutoStatement

Yes/No

True/False

DiscountBandOrPercent

Text 1

Discount Price Band

Trading Terms:

 

 

TradeTerms1

Text 60

 

TradeTerms2

Text 60

 

Default GL:

 

 

SalesGLCode

Long Integer

0-999999

COSGLCode

Long Integer

Default Cost of Sales General Ledger Code

ControlGLCode

Long Integer

Default Control General Ledger Code

OrderPaymentGLCode

Long Integer

Order Payments GL

Intrastat:

 

 

EECMember

Yes/No

True/False

IntraStatDelTerms

Text 5

Intrastat Delivery Terms

IntraStatTransportMethod

Integer

Intrastat Transport method

Payment:

 

 

CheqOrBankPayment

Text 1

C/B, defaults to [C]heque if blank, else [B]acs

BankSortCode

Text 15

Used for BACS payment

BankAccountNo

Text 20

"

BankAccountRef

Text 28

"

AllowOrderPayment

Yes/No

True/False, allow access to Order Payments

DirDebitMode

Long Integer

0=First Request, 1=On-going Request, 2=Represent Last Request, 3=Last Request

User Fields:

 

 

User1

Text 30

User field 1

User2

Text 30

User field 2

User3-4

Text 30

User field 3

User5-10

Text 30

User field 5-10 (V6.9+)

Credit Card:

 

 

CreditCardStartDate

Date

Credit Card Start Date

CreditCardEndDate

Date

Credit Card End Date

CreditCardName

Text 50

Credit Card Name

CreditCardNumber

Text 30

Credit Card Number

CreditCardSwitch Ref

Text 4

Credit Card Switch ref

E-Business:

 

 

eBusPassword

 20

Password for the e-bus web site

AllowWeb

Yes/No

True/False,  allow access to the Web site

SendHTMLwithXML

Yes/No

True/False, send documents with HTML

SendReader

Yes/No

True/False, send reader on next send

SendInvoicesBy

Long Integer

0=Print, 1=FAX, 2=e-mail, 3=FAX/Print, 4=e-mail/Print, 5=XML

SendStatementsBy

Long Integer

0=Print, 1=FAX, 2=e-mail, 3=FAX/Print, 4=e-mail/Print, 5=XML

Notes:

 

 

GNotes

Memo

General Notes (Deletes any existing Notes in General and Dated Notes)

chr$(10) or "<br>" gives new line