Things to consider when creating an import file

When you want to import data, you must determine which fields are to be allocated values and which values the fields are to contain. You must also know which names the tables and fields have in the database.

To find out which columns are to have values allocated, and the values the columns shall have, you can register data in the table and print the rows to a file. The fields occur in the same sequence in the file as on screen. You can leave out number and text fields that are 0 or blank. You can also leave out fields that are automatically allocated a value (primay keys and sequence numbers).

In front of the first row of each table in the file there must be a heading with the name of the table and the fields. The heading can be put automatically into the file, see Utskrift av importfil med tabelloverskrift.

Fields that you did not call up on the screen or fields you cannot retrieve are not included when you write to file. You can print all the fields with exports, apart from the control fields.

Hidden fields

Fields can be hidden because:

• The fields are reserved for future use.
• You are not supposed to change them.

Access Restrictions

There are some fields you can see, but not change. The restrictions are defined in Visma Business, which determines whether you are the system administrator, or the restrictions are defined by the system administrator in the Access groups table. The restrictions defined by the system administrator are in addition to the restrictions that are defined in Visma Business. Both sets of restrictions can be seen in the dialog boxes of the processing menu choices in the Access groups table. The restrictions defined by the system administrator are shown before the table name and column name, and the predefined restrictions are shown after with § as prefix.

If it is not permitted to edit a field on the screen, you can still change the field on import. You can also change the fields with SQL.

If you want to change values in fields that you could not change on the screen, you can proceed as follows:

1. Open the required table.
2. Retrieve the relevant fields. You must select the primary key fields in the table. The primary keys are the fields in which the field name is underlined. You do not need to select the primary key fields that were allocated a value automatically.
3. Make a selection from the relevant rows.
4. Select File/Page setup/File parameters.
5. Select Name in the Table heading field.
6. Click on OK.
7. Hold down shift and click on the Print icon and select file name.
8. Make the changes you want in the file.
9. Import the file with Overwrite stated fields.

Memory columns

Some fields that Visma Business uses internally are not located in the database, but only in the memory. These can be fields that are derived from other fields and that are calculated as required, for example contribution margin and ratio. These can be fields that store temporary information, for example values that the field had when last saved.

Even if a field is derived from other fields, it can still be included in the database. For example, it might be that you are interested in getting break-line totals for the field, and the field is part of a multiplication or division. This applies, for example, to warehouse values. It might also be that you or Visma Business wants to override the fields.

Merk: Fields that are contained in the memory may not be assigned an SQL name using the Export menu item. When you write a table to file using the print routine, you can decide whether the table header should include the name (SQL name), number (External ID) or mixed (SQL name and External ID for fields that are not contained in the database).

Spliting tables

After the heading come a number of rows that are to be included in the table. You can select if you want to gather all the order heads under one heading, and all the order lines under another, or if you want to split them up. For example, so that the order lines for an order come directly after the order head. This is of relevance to the allocation of order numbers, line numbers and sort sequence numbers, as explained in the section Syntax for ASCII files.

If you select to split them, you have to select a different import method to Replace entire contents of tables, because then only the last time the table occurred in the file is valid.

Merk: You must select to split when you want to add new orders that do not have an order number.