Data Import from Other Programs to WizCount

Contents

Introduction

File Format

Accounts Interface

Journal Entries Interface

Bank Statements Interface

Items Interface

Document Interface

Introduction

WizCount furnishes utility programs, which enable data import from other programs.  The data entry is based on an intermediate ASCII format file called Interface which connects between the other programs and WizCount.

WizCount has the capacity for reading data files edited in a variable format.  The variable format, compatible with WizCount, is based upon a Parameters file and enables selective and flexible data transfer.  Through the assistance of the Parameters file, the length of each record in the Interface file, which fields are to be transferred and the location and length of each field in the Interface record are determined.

Back to the top

File Format

Method of Transfer

The Interface file is accompanied by a Parameters file in which the format of the record in the Interface file is defined.

Parameters File

The file name is identical to the Interface name except that it ends with the suffix PRM.  This file is in ASCII format and may be edited through an editor.

In the first line of the file, enter the number that indicates the length of the record in the Interface file.

The record length includes the characters CR+LF that are located at the end of each record.

Starting from the second line onwards, the location of the appropriate field must be entered in the Interface file.  Each Interface will be explained later, as well as which field is appropriate for each line in the Parameters file.

The first number (from the left) indicates the beginning of the field and the second number (on the right), its end.  When the field appropriates for the line in the Parameters file is not transferred in the Interface record, enter two zeroes (0 0) on the appropriate line in the Parameters file.

Remarks may be entered into the Parameters file.  The sign “;” (semicolon) must be entered prior to each remark.  The program ignores everything written to the right of this symbol.

The advantages of the flexible method are:

• The fields in the Interface file are organized according to the user’s needs.
• You may execute a partial data transfer from the Interface record.
• The field length, which is transferred, is flexible.
• Fields that are not transferred receive the default values of WizCount.  The default values are identical to those in force at the time the data is entered into WizCount.

Fields

A table of fields appears later in the detailed explanation in each Interface; these fields may be transferred through the Interface.

The maximum length and content of each field appears in the table.  Fields longer than the maximum length entered in the table cannot be transferred.

A list of field types that may be transferred in Interface appears below:

Alphanumerical field: In an alphanumeric field, any character combination containing characters with ASCII values higher than 31 may be transferred.

When transferring, WizCount pushes all the field contents to the appropriate side, according to the field type defined in WizCount.

Decimal Field:  In a decimal field, customary numbers with decimal fractions are entered.  WizCount has the capability of reading decimal fields in several forms:

• A Decimal field without a decimal point:  the program determines the location of the decimal according to the decimal accuracy customary for that field in WizCount.  For example, if the value “1230256” is to be transferred to a quantity field, WizCount converts this number into “1,230.256”.
• A numeric field with a decimal point:  the location of the decimal point in the Interface file determines the decimal value.  For example, if the value “3456.2” is transferred to a quantity field, WizCount converts this number into “3,456.200”.
• A decimal field with commas between the figures: WizCount ignores the commas between the figures.
• Numbers with a positive (+) or negative (-) integer sign: the sign must be to the left of the number, although not necessarily touching it.

Two numbers, which indicate the maximum width of each decimal field, appear in the tables in the explanations for Interface.

The right number indicates the number of figures permitted for transfer to the right of the decimal; the left number indicates the number of figures permitted for transfer to the left of the decimal.

For example, if 6.3 is entered, the number “123,456.789” may be transferred.

Date field:  A date field, which is transferred to WizCount, must be in the DDMMYY format or the DD/MM/YY format.

WizCount checks the legality of this date (to see if it is permissible for transactions, if the date is non-existent, etc.)

When a reference date or a value date is transferred, it must be within the settings defined in the Definitions category in WizCount.

Transfer Procedure

In the process of transferring, WizCount checks for data integrity.  If WizCount finds irregularities in the Interface file, an errors report will be displayed without transferring the records that contain the errors.

The general and unique integrity checks in each Interface will be explained later.

At the end of the transfer, if even one record was transferred to WizCount, the program changes the suffix of the Interface file either from DAT or DOC to BAK.  The name of the Parameters file does not change.

Back to the top

Accounts Interface

The Accounts Interface in WizCount enables the import of accounts from other programs to WizCount.  The Interface enables opening new accounts and updating data in existing accounts.

Interface File Name:  HESHIN.DAT

Parameters File Name:  HESHIN.PRM

Parameters File Format:

In the first line, enter the record size in the Interface file.

In Lines 2-49, enter the location of the fields into the record according to the following table:

Special Checks

• Each record must include an account key field.
• The field “% deduction at source” may not be a negative number.

Back to the top

Journal Entries Interface

The Journal Entries Interface enables the import of journal entries from other programs to WizCount.  The transactions are transferred into temporary files in WizCount; afterwards, the user may check and correct them prior to entering them into the permanent file.

Interface file name:        MOVEIN.DOC

Parameters file name:    MOVEIN.PRM

Parameters File Format:

In the first line, enter the record size in the Interface file.

In Lines 2-24, enter the location of the fields in the record according to the following table:

Special Checks and Remarks:

• All the decimal amounts, which appear in the transaction’s record, are saved in WizCount as Two-place decimals.
• Transactions may not be transferred without an account.  Each transaction must include at least a debit account or a credit account.
• Transactions without amounts cannot be transferred.  Each transaction must have at least one sum.
• An amount cannot be transferred without an account; however, accounts without amounts (informative accounts) may be transferred.
• When a transaction has a debit sum and a credit sum, the transaction must be balanced, i.e. the debit amounts must be equal to the credit amounts.
• It is possible not to transfer dates; WizCount will then enter the date from the computer system as the dates of the transactions.
• Value and reference dates must be within the date limitations defined by the user in the Definitions category in WizCount.

• A transaction, which includes more than two accounts, must also include a transaction type code.

• The transaction type is only informative and does not actually split the amounts in the transaction.
• If a transaction type is not transferred, a second debit or credit account key may not be entered.
• It is recommended to use negative amounts for cancellation of transactions in accounting.

Journal transactions that refer to invoices

The following examples assume the VAT rate is 18% and should be modified if the VAT rate is different.

The structure of the journal transactions of invoices should be as follows:

Export invoice: insert the export document official number (if exists) in the 3^rd Reference field. If there is no such document the field should be empty.

Import: make sure that the accounting index includes a special account for VAT on input for import (on top of the VAT on input account).  This account is the debit account in the journal transaction that refers to the VAT paid for the imported items. The document should be registered in the 2^nd Reference field.

Sales to and from the Palestinians Authority: the journal transactions should be according to the above mentioned guidelines expect for a different VAR rate.

Sales to chance customers: when the debit account of sales is chance customers, the maximal amount of each sale is 5,000 NIS (not including VAT). If the amount is higher, the debit account should be of a customer account included in the index.

Sales from cash register: The number of slips (on top of their amount) should be inserted in Reference filed. If several transactions refer to the same Z, insert the number of slips in one of the transactions and 0 in the others.

Petit cash: The structure of the Petit Cash transaction is like the structure of a purchase invoice. Insert the number of invoices in the 2^nd Reference field.

Back to the top 

Bank Statements Interface

The Bank Statements Interface enables the import of bank statements from other programs or directly from the bank computer via modem into WizCount.  WizCount furnishes a flexible format for the Interface file based on a Parameters file.

Before entering data into the Interface file, WizCount performs data integrity checks; should WizcCount detect errors, an errors report is displayed without entering erroneous data into the file.

If the Interface file is intact and the account already has a bank statement in WizCount, the program checks that the imported file is the continuation of the existing bank statement in WizCount.

Below are details about Interface in flexible format:

Interface file name:        BANKIN.DAT

Parameters file name:    BANKIN.PRM

Parameters File Format:

In the first line, enter the record size into the Interface file.

In lines 2-7, enter the location of the fields in the record according to the following table:

Special Checks and Remarks:

• A date field must be in each line in the Interface record
• If, in the Interface file, there is only one amount, its location is entered in the Parameters file on Line 5, which is designated for the debit amount.  In this case, enter 0 0 in Line 6 in the Parameters file (designated for the credit amount).
• In transactions with one amount field, a zero amount cannot be entered.  In these transactions, negative amounts will be entered on the debit side and positive amounts on the credit side.
• If both a debit amount field and also a credit amount field are transferred in a record, a debit sum or a credit sum must also appear in each transaction in the Interface file.
• It is impossible to transfer, in the same transaction, both a debit and a credit amount.  The transfer of transactions with a debit amount of zero and a credit amount of zero is not permitted.
• The cumulative balance field includes the cumulative balance of the previous line plus the current transaction.  This field serves as a continuous check against the existing bank statement for the account in WizCount.  If this field is not transferred, no continuous check is performed.

Back to the top

Items Interface

The Items Interface enables the import if items from other programs to WizCount.  The Interface enables you to open new items or to update existing ones.

Interface file name:        ITEMIN.DAT

Parameters file name:    ITEMIN.PRM

Parameters File Format:

In the first line, enter the record size into the Interface file.

In Lines 2-65, enter the location of the fields in the Interface record according to the following table:

Special Checks and Remarks:

• Each record must include an item key field
• Negative numbers may not be transferred into the following fields: % Customs Duties, Weight, Volume, Purchase Quantity, Stock Level, Ordering Point, % Purchase Tax (Retail/Wholesale), Last Purchase Price.
• The ordering point must be less than or equal to the stock level.

Back to the top

Document Interface

This utility enables data import of documents – invoices, delivery notes, orders, quotations etc.

When importing the data the user determines –

1. If the account (customer or supplier) in transaction to be imported does not exists in WizCount database, should the program automatically open a new account (or reject the transaction).
2. If the item in transaction to be imported does not exists in WizCount database, should the program automatically open a new item (or reject the transaction).

If the automatic opening option is selected, the account or item name will be: "Open in data import from another program."

If the rejection option is selected, WizCount will issue a report listing all the rejected transactions.

The name of the data file: IMOVEIN.DOC

The name of the parameter file: IMOVEIN.PRM

The structure of the parameter file

The first line presents the size of the records in the data file.

Lines 2 – 52 list the position of the fields (in the record) according to the following table:

Special Checks and Remarks:

• Manufacturing report cannot be imported.
• In the document Trasfer from one wharehouse to another, the transaction refer to the inward. The fields Warehouse and Salesperson (11,12) refer to the target (to), while the fields Warehouse – origin and Salesperson– origin (14,15) refer to the origin (from). WizCount will automatically add the transaction for the outward.
• The following fields should be included in each transactions: Item ID, Quantity, Document No, Document type.
• The value of the Quantity field should not be 0.
• When importing the following documents, the Account ID should be included in each transaction: Invoice, Order, Delivery note, Quotation (all types of these documents).
• A document that functions as both invoice and receipt cannot be imported (the invoice and the receiept should be imported by two files).
• The document type determines whether the quantity enters or exits. Therfore the quantity should be positive, unless the document cancel a previous one.
• The price is the price prior discounts.

Document types