Can I remove remove 1

[MatthewRheinberger]

Hi,

I am a consultant working with a client that imports financial transaction data from Expocad into Accpac 5.4.

Expocad data is exported to a csv file (refer below) which is then loaded into the Accpac AR module.

At present remembering not to load the file twice is being managed by my client's staff. Changes made in Expocad are then exported for loading into Accpac which is time consuming, frustrating and risky.

An ideal solution would be that all Expocad financial records are exported and imported to Accpac daily (or hourly) and then the Accpac import process ignore transactions previously updated. Even better would be having this done automatically.

Is this possible? and if it is could someone assist in helping me understand what the interchange file content needs to look like?

My thanks in advance.

A sample file being used is:
RECTYPE CNTBTCH CNTITEM CUSTPO IDCUST INVCDESC ORDRNBR
RECTYPE CNTBTCH CNTITEM CNTLINE IDITEM TEXTDESC UNITMEAS QTYINVC AMTPRIC
RECTYPE CNTBTCH CNTITEM CNTPAYM DATEDUE
1 1 1 1754 51867 Trade Fair F07SS
2 1 1 1 ASCFEB Stand HT02a 2.50 x 4.00 Stand Charges EACH 10 220
2 1 1 1 DPFFEB Promotional Fee EACH 1 250
2 1 1 1 GAFFEB Administration Fee EACH 1 100
2 1 1 1 KPLFEB Public Liability Insurance EACH 1 45.45
2 1 1 1 MPPFEB Power Point Fee EACH 1 50
1 1 1 1755 45634 Trade Fair F07SS
2 1 1 1 ASCFEB Stand HT01a 2.50 x 2.50 Stand Charges EACH 6.25 220
2 1 1 1 DPFFEB Promotional Fee EACH 1 250
2 1 1 1 GAFFEB Administration Fee EACH 1 100
2 1 1 1 KPLFEB Public Liability Insurance EACH 1 45.45
2 1 1 1 MPPFEB Power Point Fee EACH 1 50

 

[jayconverse]

Add an invoice number (IDINVC) to the header record.

Jay Converse
IT Director
Systemlink, Inc.

 

[MatthewRheinberger]

Thanks Jay,

Are you saying that I can add an IDINVC to RECTYPE 2 (the transactional records)?

From what I understand the data coming from Expocad could be a full extract each time. I can investigate with Expocad can give a 'static' invoice id.

Would Accpac ignore the record if an invoice number was provided and data already loaded to Accpac?
What would happen if the IDINVC was the same and the financial value different (someone altered the transaction data in Expocad by changing booth size).

As example:
2 1 1 1 ASCFEB Stand HT02a 2.50 x 4.00 Stand Charges EACH 10 220
was imported to Accpac ($220 imported to Accpac) on initial load

Booth size reduced and then next Expocad extract contained same IDINVC but overall amount was now $150
2 1 1 1 ASCFEB Stand HT02a 2.50 x 4.00 Stand Charges EACH 10 150

Would Accpac update the original record from 220 to 150? or
Would Accpac create an additional entry (-70) to account for change in booth booking value?

Appreciate your thoughts before I go investigate the hard way.

My thanks in advance

 

[ettienne]

You need to add an IDINVC to RECTYPE 1 - the invoice header.

The import will error out with something like the invoice number already exists if you reimport the same invoice number. If there are changes/adjustments made to invoices in the source app then you need to include those transactions as credit/debit notes in the import file.

 

[IMALEGEND]

Perhaps you should speak to the Accpac Solution Provider for the site who you may find may have already investigated the shortfalls of Expocad that resulted in the current process.