Importing sales invoices from RetailVista to Accountview.

This document describes the import of sales invoices from RetailVista to Accountview based on a specially created Csv (comma separated) file export for Accountview.

Introduction

From RetailVista version 2.218, sales invoices can be exported to Accountview. For this purpose, a CSV (Comma Separated Values) file is created. An additional license is required within Accountview to import CSV files. This license is called 'Mutation import'. In a later stage, it is possible that RetailVista will also export master files (debtors), in which case the 'Master data import' license will also be required. However, at this moment, only the mutations are exported. These licenses can be requested from the supplier of your Accountview software.

RetailVista settings

In order to use the export functionality from RetailVista, a number of settings must first be made in RetailVista.

1. Check if there is an article 'Deposit' in article maintenance.

Create this product if it does not exist yet. The VAT on it is V (Exempt, otherwise 0%). Indicate in the 'Sales' tab that it is not a sales product.

2. Determine for each cash group in RetailVista to which general ledger account the sales should be allocated and to which general ledger accounts the VAT (High, Low, Zero, and Exempt) should be booked. Create all these general ledger accounts in RetailVista with the same general ledger number as Exact. The maintenance of general ledger accounts can be found under Administration, General Ledgers.

3. Create a general ledger account of the type 'Profit and Loss' with the description 'Unknown sales'. Consult with the customer about the general ledger code to be used.

4. Create a general ledger account of the type 'Balance' with the description 'Unknown VAT'. Consult with the customer here as well about the general ledger code to be used.

5. Check if the VAT codes in RetailVista match with Accountview.

6. Check if the payment condition codes in RetailVista match with Accountview.

7. Check via Administration, General Ledger links if all general ledger links for a sales type and VAT are available.

8. Go to Extra, Settings, 'Financial' section. Select the previously created general ledger account for 'New sales' and do the same for 'VAT on new sales'.

9. Select 'AccountViewCsv' as the journal type in the 'Export' tab. In the 'Invoice document' field, enter the next available invoice document number within Accountview for sales invoices. Enter the code of the journal in which the RetailVista invoices should be imported in the 'Invoice journal' field.

10. Check if the customer file in RetailVista matches the numbering of customers in Exact. A customer can be created and modified via Start, Relationships. The 'Notification code' field contains the number used for the link to AccountView. If a debtor number from RetailVista does not appear in AccountView, the file cannot be imported.

11. Check if the financial code of each payment condition matches the payment conditions in AccountView.

12. It is recommended to create a separate sales invoice journal in which all invoices from RetailVista are imported. This makes it easier to remove an import from AccountView if it turns out that the import did not go as expected. This becomes more difficult if there are also directly entered data in AccountView.

AccountView import definition

In order to import files, an import definition must first be created so that AccountView can import CSV files. It is convenient to already have an export file available to create the definition based on that file. Download a demo export file via http://support.nedfox.net/go/binaries/av.zip. Extract this ZIP file to a temporary location. The following screens assume x:\av.csv

Now go to File, Administrations in AccountView. Then go to Document, Import, Create import definition, Mutations. The following screen appears where a description must be given to the definition. It is important to choose 'Add definition to menu' here. This ensures that the definition is saved and can be easily reapplied later.

 In the next screen, choose 'Import into Journal Pages'.

In the next screen, choose 'Text files with column delimiters' as the data type and indicate where the (demo) export file from RetailVista can be found.

Then click the 'Structure' button to display the contents of the file on the screen. It is very important to adjust the width of several columns in the screen below. AccountView first calculates the width based on the widest data in a specific column. In practice, certain values can turn out to be larger, causing data to be lost or incomplete (without adjustment).

In the first 'structure' screen that is displayed, general settings can be adjusted for line separators and field separators. No adjustments need to be made in this screen, as the settings are already set correctly by default for RetailVista exports.

In the next screen, the maximum width can be indicated for each column. By clicking on a column, the data for that column will appear at the top of the screen. The following corrections must be made:

Column 1 (Field_1): Line number, adjust width to 4

Column 3 (Field_3): Period, adjust width to 2

Column 5 (Field_5): Description, adjust width to 60

Column 6 (Field_6): Debtor number, adjust width to 20

Column 8 (Field_8): Total invoice amount including VAT, adjust to 10

Column 13 (Field_13): Payment condition code, adjust to 2

After making these corrections, close this screen by clicking 'Finish' and in the wizard screen that appears again, click 'Next' to go to the screen below. Click on 'Modify' there to create an import selection.

The filter screen below appears, where it can be indicated that only certain lines from the CSV export file should be imported. Because only the 'Journal pages' are imported in this section, it is important to create a filter that ensures this. In the CSV export file, the first column is filled with a line number that increases per invoice starting from 0. The invoice 'header' line is always line 0, so a filter must be created on FIELD_1 == 0. The screen below shows what that filter looks like.
Note: It is very important to click on the 'Add' button after setting the filter. This will ensure that the filter appears in the top text box. If 'Ok' is clicked directly, the filter will NOT be added!

Close the screen by clicking on 'Ok' and go to the next page in the wizard screen where the link between the fields in the CSV export file and the AccountView fields needs to be made. Click on 'Edit' to start the field mapping.

The image below shows the fields that need to be linked. By 'dragging' a field from left to right, a permanent connection is made.

The field 'Payment condition' is not listed by default in the list of AccountView fields to be linked. By clicking on 'Show all fields', this field can be found, its code is 'DISC_CODE'.

The field 'Period' (FIELD_3) must be explicitly set to 'Numeric'. This can be done by double-clicking on the connection line between FIELD_3 and PERIOD. In the screen that appears, choose the 'Numeric' type.

Close the field mapping screen and click on 'Next' in the wizard screen. The invoice header lines are now linked. Next up, the invoice lines need to be linked. Again, provide the same demo file to import and click on 'Structure' again.

The following corrections must be made:

Column 1 (Field_1): Line number, adjust width to 4
Column 3 (Field_3): Period, adjust width to 2
Column 6 (Field_6): Amount including VAT, adjust to 10
Column 7 (Field_7): VAT code, adjust width to 3

Afterwards, click on 'Finish' and click on 'Next' in the wizard screen.

Click on 'Edit' in the screen above to indicate which rules from the CSV export file should be imported. The filter screen below will appear, where it is important this time not to import the header invoice rules but rather the specification invoice rules. These are all rules with a rule number greater than 0. (Rule number 0 indicates the header invoice rule).

Close the selection screen by clicking 'Ok' and click 'Next' in the wizard screen. A page will now appear where the columns from the CSV file need to be linked to the journal entry fields in AccountView. Click 'Edit' to start the column mapping screen.

In the screen below, indicate which columns from the CSV export file should be mapped to the different AccountView fields.
Note: By default, the data from line 1 of the CSV export file is displayed in the left half of the screen. This is an invoice header rule, not an invoice detail rule. It is therefore advisable to click on the > button at the bottom to change the display to 'Rule 2 of xxx'.

The 'FIELD_6' field needs to be changed to a numeric field, and it is very important that the decimal separator in the subsequent screen is set to a '.' (period) and the thousands separator to a ',' (comma) symbol.

Close the transformation properties screen above by clicking 'Finish'.

In the screen above, it is possible to click on each 'magnifying glass' icon to view the data at that point. Especially the last magnifying glass icon is useful, as it shows how the invoice header and detail rules will ultimately be merged.

Go to the next page in the wizard by clicking 'Next' again.

In this page, it can be indicated how the data should be imported. There is no need to work with an intermediate file, because all lines end up in a separate sales journal. From that journal, lines can always be removed, one of the great advantages of AccountView.

It is advisable to indicate that the files should be renamed after importing.

In the last screen of the wizard, the result of a possible import can be viewed on the screen and optionally printed or saved. 

Exporting journal

Once the necessary preparations have been made, an invoice journal can be exported to AccountView. It is important that a journal has been generated first. This documentation assumes that a journal has been generated and printed, and that it has been verified as correct.

Go to Start, Invoicing, Export invoice journal. Optionally adjust the booking period as indicated there and click 'Export invoice journal'. A file will now be downloaded that can be imported into Exact.

Importing file into AccountView

The previously created 'Invoices' import definition is now available through the 'Mutations import' menu of AccountView. Go to File, Administrations. Then go to Document, Import, Mutations and select the previously created import definition 'RetailVista invoices'.

Afterwards, the following import screen will appear.

Now enter the file name of the invoice export file created by RetailVista. If the import definition still needs to be modified, it can be done by clicking the 'Modify' button from this screen. Press 'Import' and in the next screen, indicate the same file to be imported again. Enter the file again and click 'Import'.

In the result screen above that appears after executing the import, it can be seen that one record has been offered and imported correctly. This concerns the number of imported invoices, not the number of invoice lines!

If errors have occurred during the import, a report can be printed in this same screen, which clearly indicates with which data AccountView had problems importing.

Ultimately, the imported invoices can be found in the specified journal of AccountView. The example below shows the pages of journal 650, counter invoice book.

Frequently Asked Questions

Question: Can I export a debtor file from RetailVista and import it into AccountView?
Answer: No, unfortunately this is not yet possible.

Question: Can an import definition also be taken over from another AccountView environment or does NedFox have a standard definition available?
Answer: Unfortunately, AccountView does not offer a secure way to import import definitions.

Question: Can I import RetailVista invoices with any AccountView environment?
Answer: Yes, as far as we know, as long as you purchase the correct license from AccountView.

Question: There is no invoice due date in the export file. Why is that field missing?
Answer: AccountView does not provide the option to import the due date. Based on the payment condition (which is included in the export), AccountView calculates the due date itself (based on the invoice creation date from the export file).

Question: Where can I find a demo version of AccountView to test?
Answer: The various dealers of AccountView provide free demo versions on CD. The limitation of the demo version is that it can only import up to period 3 and there is a limit on the maximum number of financial transactions in one administration.

Question: Is there an example export file available from RetailVista?
Answer: Yes, it can be downloaded via http://support.nedfox.net/go/binaries/av.zip

Question: How can I delete an import definition?
Answer: As far as we know, this is a bit cumbersome. The only possibility we have been able to find is to start the import of a file and then click on 'Edit' from there.

Question: How can I delete an import definition?
Answer: As far as we know, this is a bit cumbersome. The only possibility we have been able to find is to start the import of a file and then click on 'Edit' of the definition in the first import screen. In the 'Import definition' screen that appears, deselect the option 'Add definition to menu'. Then, click on 'Next' repeatedly until the wizard is completely finished. By doing this, the definition will disappear from the import screen and will no longer be present from that moment on.