Preparation of the Excel file
General structure
The Excel file must have:
- a header line first
- then one line per contract line/price line
The same agreement can appear on several lines in the file. The importer then merges these into an agreement and puts the respective price line in the correct list.
Column headings and accepted names
The headings are not case sensitive and can be in Swedish or English.
The import recognizes both:
- headers from external file structure
- internal/native propname*
- Swedish and English versions
1. Identification and Update
The import primarily uses the following logic to find the right agreement:
- Serial number / contract number if it is in the file
- otherwise OrderNo + customer number if there is one and an agreement already exists in the group
- otherwise a new agreement is created
| Accepted headlines | |
|---|---|
| agreement number / identity | serial serialnumber agreementnumber agreementno avtalsnummer avtalnr project projekt |
| external order-/project number | orderno ordernumber ordernr orderno. |
If the file is completely missing an agreement number, the import will still work. Then the rows are first grouped together into the correct agreement within the file, and the import then creates new agreements.
2. Agreement field
| Agreement field | Accepted headlines |
|---|---|
| Agreement name | name agreementname description Note: In some external files, Description is used twice. Then interpreted: first Description as contract name other Description as the price row description |
| Customer number | customerid customerno |
| Customer name | customer customername |
| Facility | facility facilityname |
| Start date | fromdate start startdate |
| End date | end enddate agreementend agreementenddate |
| Created date | creationdate created |
| Cost center | costcenter |
| Agreement type | type agreementtype avtalstyp fakttyp fakttypt |
| Invoicing frequency / interval per month | invoicingperiodmonths invoicefrequency Supported values for invoicing frequency: 1, m, month, monthly → 1 month 3, kv, quarter, quarterly → 3 month 6, halfyear, semiannual → 6 month 12, year, yearly, annual → 12 month |
| Autogenerate invoice | autogenerateinvoice autofaktura autogenererafaktura |
| Financially settled | financiallysettle financialsettle slutreglera slutregleras |
3. Price row
The import can read both invoiced lines and included lines in the same file.
| Price row | Accepted headlines |
|---|---|
| Price row type | rowtype articlelist includedorinvoiced target rowtarget radtyp Supported values: included → the line is imported to Included Supported values: invoiced fakturerad fakturerat → the line is imported into Invoiced If the column is missing or the value is not recognised, the row ends up in Invoiced. |
| Row number | linenumber line row |
| Article number | artno article articleno articleid |
| Price row description | desc species desc item description Additionally, other occurrences of Description in some external files may be interpreted as price line description. |
| Amount | amount number If number is missing or is 0, 1 is used. |
| Unit | unit enhet |
| Price / gross price / sum | grossprice price |
| Discount | discount rabatt |
| External counter | externalcounter counter externteller |
| External counter value | externalcountervalue countervalue |
4. Custom field
Columns that are not recognized as standard fields are interpreted as custom fields.
In order for a self-defined field to be saved as a true self-defined contract field, the corresponding field must already be set up in the group’s Agreement Settings. If the field is not set up in the settings, the value is instead added to the agreement’s description during import.
Data format and values
Dates must be valid dates, boolean values must be clear, and numeric fields must contain numbers.
Date
Preferably use:
- YYYY-MM-DD
Other valid date formats may also work, but the recommendation is to use a clear date format such as YYYY-MM-DD.
Boolean value
The following is interpreted as true:
- true
- 1
- yes
- y
- ja
- j
Empty or other values are interpreted as false.
Numeric values
For numeric columns such as:
- amount
- price
- discount
- counter value
you should only use numeric values.
Missing number of articles
If quantity is not specified on a price line, 1 is used.
When an agreement is updated
The importer first tries to find an existing agreement by:
- SerialNumber
- otherwise OrderNo + customer number
If an agreement is found, it is updated with new values.
When a new agreement is created
If no matching agreement is found, a new agreement is created.
When the contract number is missing
If the contract number is missing in the file:
- the rows are still grouped together into the correct agreement within the same file
- a new serial number is created internally for the import
- the same file can therefore be imported correctly even without a serial number
Mixed price line types
If the file contains both:
- Included
- Invoiced
the rows will end up in the correct list per row.
If line type is missing, the line goes to Invoiced.
Duplicate handling for price lines
The import tries to avoid duplicates by comparing:
- rownumber
- articlenumber
- description
- amount
- price
If the same row is found, it is updated instead of added again.
Why didn’t the agreement come through?
Common causes are:
- no useful contract or line content on the line
- invalid date format
- numeric fields contain text
- customer cannot be matched against the customer register
- typos in headings so the field is not recognised
- the agreement contains self-created fields that are not set up in the agreement settings
*native-import means directly importing a file exported from the system
Testing
