On this page

Data import guide

This document describes data structure and import best practices in Sage Distribution and Manufacturing Operations.

Data organisation in Sage Distribution and Manufacturing Operations

Sage Distribution and Manufacturing Operations is based on GraphQL entities called nodes.

A node is an entity that manages records, sets of data and properties. The following operations can be associated with a node:

Read operations:

read to read a record
query to read several records
lookup to do a query on a subset of properties for selection purposes in the user interface

Operations performing updates, called mutations in the GraphQL terminology:

Create, Update and Delete are the standard mutations.
A node can also have specific mutations such as revert for an accounting entry, post for a sales invoice and block for a customer.

A node includes properties that can be of any type:

Classic types such as strings, numeric values, Boolean, enumerations, dates, pictures, rich texts and so on.
References to other nodes. For example, a customer can have a delivery mode which is another node.
Arrays of references to other nodes. A customer can have several addresses, every address being a node. References can be vital or non-vital.

A vital reference cannot exist without the origin node. This is the case for order lines attached to a sales order.
A vital reference can continue to exist if the main node has been deleted. For example, if a customer record references several sales representatives managing the account, deleting a customer doesn’t remove the sales representatives.

GraphQL schema and use for APIs

Using GraphQL is the only way to access the relational database used by Sage Distribution and Manufacturing Operations. A user dealing with APIs or a developer has no direct SQL access to the database. This ensures security. Rights are granted on nodes and operations, securing tenant isolation.

A GraphQL operation can only access the tenant it has been connected to. No limit exists on the accessible data if a node is published.

You can use the GraphiQL tool to navigate across nodes and find them. You can access the GraphiQL tool directly from the main URL of the tenant, with the /explorer/ extension. It is the easiest way to navigate across the schema and look at the different nodes that are available. You can also find information in the documentation. The main page of this tool looks like this:

GraphQL API sandbox main page

You can enter queries or mutations with the GraphQL syntax in the first column. The results display in the middle panel. You can access the schema directly in the right panel.

Mass import of data

You can create records for entities in two ways:

Using APIs for mutations on these entities.
Working with a tool to integrate a large number of lines, for example, if they are extracted from a previous system. In this case, managing CSV files is the most convenient way to import data. Sage Distribution and Manufacturing Operations delivers CSV formats associated with any node with an available creation method. The format of these files is explained below.

CSV global structure

The CSV format has columns of data separated by a semicolon:

The header line defines what the different columns of the CSV file represent:

For a single property, its name is given, prefixed by ! if it is the record's main key and by * if the information is mandatory.
A reference is identified by the referenceName(key) syntax, where referenceName is the property to be imported and key is the name of the key property that retrieves the linked record.
Arrays of references can be identified by a group of data and will be explained later in this document.

Two additional lines can be present to describe the structure, but they are not mandatory.
Every additional line represents a record.

Basic format with single-line records

Example: CSV file for items

The first line is mandatory and describes the structure of the data to import.
The following two lines are optional and are documentation oriented. The IGNORE value at the end identifies these lines and will prevent the import tool from considering them as data to import.
The other lines are data.

!id	*name	description	type	isBought	volumeUnit(id)	volume	image
string	string	localized text	enum(service,good)	boolean	reference	decimal	binaryStream	IGNORE
id	name	Default locale: fr-FR	type	is bought(false/true)	volume unit	volume	image	IGNORE
A100	ABC	ABC product	good	TRUE	litre	0.2
PRES	Prest	Prestation	service	FALSE	hours

In this example:

id is the record key, prefixed by !, and has a string data type.
name is a mandatory string field, prefixed by *, containing the item’s name.
description is a non-mandatory string containing the item’s description.
type is an enumeration, with two choices in its values list: service or good.
isBought is a true or false value, false being the default value because it is the first mentioned in the list. It defines if the item is bought or not.
volumeUnit is a reference to the unitOfMeasure node. The (id) notation refers to the name of the property in the unitOfMeasure record that is used to reference the unit.
volume is a decimal value.
image is a binary stream that can be entered as a base64-coded string. In this example, it is a picture.
description is a translatable text. The tenant language is indicated in the second line.

In this example, only fr-FR text will be imported. This document later describes how to import texts in several languages.

Reference collections in the header and lines

Frequently, the data to import is structured in header and line records. Sometimes, a single record can have several line sets and even nested line sets.

For example, if an order has order lines, every order line can have sub-lines if there are different delivery dates. This is done by splitting the total quantity of the line.

Another type of line can simultaneously be associated with the same header. For example, a list of comment lines, each comment line being a single record.

As explained in the first chapter of this document, it corresponds to a property in the main node which is an array of references on nodes containing lines.

The CSV format allows importing data of lines associated with header information if the lines are vital references to the main node.

You can also import the header and lines separately when the following situations happen together:

Lines are not mandatory in a document.
A creation operation exists on the corresponding node. The lines can be created separately in this case.

Examples

In the xtremSales package, the salesOrder node includes a lines collection that is a vital reference to the salesOrderLine node.

As this vital reference cannot be empty because there must be at least one line in the sales order, importing a sales order requires having at least one line imported with it.
As the salesOrderLine node does not have a creation operation associated with it, the only way to create a sales order is to embed the header information (salesOrder properties) and the lines (salesOrderLine properties) in the CSV file.

In the xtremMasterData package, the item node includes an allergen property that is a vital collection on itemAllergens nodes that store the item's allergens if it is food.

Allergen is not a mandatory collection but a vital one.
You can either import a record with a product and at the same time, additional records for each allergen associated with it, or import item records and itemAllergens records separately.

The item node also contains a collection of itemCustomer references. This collection represents the customer-linked information for items.

As this is not a vital reference collection, you cannot import the information related to the items and their customers' related reference in the same file.
Because a vital collection can only have one parent, Sage chose to consider itemCustomer as a vital collection for the customer. This means that you can import customer records and their associated item information in a single file.

CSV format for the header and lines

When a vital collection is on a node, the properties of the collection's members are present on the node just after the property that represents the collection.

The property that represents the collection is prefixed by a #. This indicates that the following properties are coming from a child node.
If a sub-collection exists in a child collection, the property corresponding to the sub-collection is prefixed by ##. Every additional nesting level is defined with an additional #. You can have two collections at the same level, but nested collections must follow the group they are nested in.
List the main record's properties before describing any nested collection.

You need to place the records on different lines in the correct nesting order.

Example

This file contains only a header line and data to be imported. It corresponds to a subset of item information:

The first group of information contains the item's main data.
The second group starts with #allergens.

It corresponds to the item-allergen node, which contains the allergens associated with a food product. This node includes a reference to the item and to the allergen. This reference is stored in the allergen identity.
The only necessary property to list here is allergen, as a reference, id being the key in the allergen entity. #allergens is the item node property that defines the nested collection.
No real value is expected here, but the lines defining the allergen are specified because there is a non-empty value. Usually, the line can have a counter, but any non-empty value is correct.

The third group is the classification group for chemical products. It follows the same principles as the previous groups.
The fourth group is the item-sites group. It references sites and various numeric values.

!id

*name

description

type

isBought

#allergens

allergen(id)

#classifications

classification(id)

#itemSites

site(id)

prodLeadTime

safetyStock

PIE_A

APPLE_PIE

Home-made apple pie

good

TRUE

EGG

MIL

CCW

NAT009

100

DEP-S01

300

CHO

CHOCO

Chocolate

good

TRUE

DEP-S01

PRES

INST

Installation

service

FALSE

In the table above:

The first item has header information, three lines related to allergens, and two lines associated with sites.
Then there is another product with only one sub-level corresponding to one site.
And finally, a fourth product with no sub-details.

To import the allergens associated with the Chocolate item separately, you can use the following file related to the item-allergen entity.

!item(id)	!allergen(id)
CHO	MIL
CHO	SOY

There are two main records associated with two values. One is the reference to the item; the other refers to the allergen.

Duplicate property names in reference properties

In a header, every property must be unique. But sometimes, properties can have the same name on different nested groups, for example, name, status or amount.

When this happens you need to add a suffix to the property starting with a #, so the column's title is unique.

Example

For different groups and sub-groups with similar status and name properties, the header can look like this:

status

name

#line

status#1

name#1

##subline

name#2

#address

status#2

name#3

This header has one line group with a subline nested group and one address group.

Properties named status exist in these three groups: their property codes are status, status#1 and status#2.

There are four different property codes for names: name, name#1, name#2 and name#3.

Localised texts

You can store texts such as product descriptions and retrieve them in different languages as an array of texts associated with a list of language codes.

This process is almost transparent for the user. Depending on the language used for the connection, the text displays in the correct language. If the text is unavailable in the language, there is a base text with a default value.

A language code can be a two-character code compliant with ISO 639-1. It can be en, fr, de, es, it and so on.

When you connect, you usually see a language code associated with a country code, for example, en-US, en-GB, fr-FR, fr-CA, es-ES or es-AR.

If the language code used for connection does not correspond with an existing translation for a text, the system tries to see if there is an equivalent text associated with the two first characters of the language code. For example, the following translations are stored in the database:

base: Text 1
en: Text 2
fr: Text 3
en-GB: Text 4
en-US: Text 5
fr-FR: Text 6

If you connect with:

fr-FR, Text 6 is used because it is a perfect match.
fr-CA, Text 3 is used because the first two characters match.
pt, Text 1 is used because it is the default base text.

When you create a record through import with just the property name as a header, only the base text is filled. But you can create several columns in your template by adding the language code between parenthesis just after the property name.

Example

description	description(en-US)	description(en-GB)	description(fr-FR)	description(es)
Text1	Text2	Text3	Text4	Text5

If a record is created with this import file:

Text1 is stored as the base value.
Text2 is stored as a value for en and for en-US.
Text3 is stored as a value for en-GB.
Text4 is stored as a value for fr and fr-FR.
Text5 is stored as a value for es.

When you update an existing record through import, only the exact matches are filled. In this case:

The base value is updated with Text1.
The en-US and the en-GB are created (if they don’t exist) or modified with the Text2 and Text3 values.
The en translation is not filled if it doesn’t exist. This means that a user connecting to the user interface with en-AU as locale displays the base value.
If the fr translation and the fr-FR translation exist, the fr translation is not changed, whereas the fr-FR translation is updated with Text4.
If the es-ES translation exists, it is not modified; but the es translation is updated with Text5.

Complex reference management cases

Some data structures such as addresses have a key that is not unique. It is a potential issue when creating a record that references a key on which two duplicate values exist. An error occurs. Sage will fix this in a future release. In the meantime, you can solve this issue by managing several imports rather than a unique one and by making sure the corresponding keys are unique. This is particularly important for customer or supplier imports for which you need to reference different default addresses. You can do this by importing the business entities with all the addresses first, then by importing customers or suppliers that reference the business entities and the corresponding address. See the recommended import procedure below.

Data import procedure

This section explains how to generate templates and import data in Sage Distribution and Manufacturing Operations.

Generate the template

Open: Administration > Import > Import template

The import tool is based on templates created by entering an ID, a name and the node name.

A CSV template is created with three definition lines at the start filled automatically. Select Generate template to download a template you can use later to create data files.

Import template page

Import data

Open: Administration > Import > Import data

This page allows you to upload a file to be imported and select the template.

Import data page

Data files can have fewer properties than the ones present in the template. You can delete columns in the file. As long as no mandatory field is missing, you can import such a file using the import data page.

Two buttons are available on this page:

Import, which performs the import and updates the database.
Test import, which only performs consistency checks on the data to be imported.

If errors occur, a message tells you how many records have an error.

Example

This example shows what happens when you try to import an allergen record on an item that is a chemical component.

!id

name

description

status

type

isSold

stockUnit(id)

volume

weightUnit(id)

weight

attribute

isStockManaged

#classifications

classification(id)

#allergens

allergen(id)

CHLORINE

Chlorine

active

good

TRUE

0.9

chemical

TRUE

EGG

An error message displays.

Error message

A file is generated and you can download it.

Generated file

This file has the same format as the import file, with an additional _error column which gives the error message that has been found.

In this case, it looks like this:

!id

name

description

status

type

isSold

stockUnit(id)

volume

weightUnit(id)

weight

attribute

isStockManaged

#classifications

classification(id)

#allergens

allergen(id)

_error

CHLORINE

Chlorine

active

good

TRUE

0.9

chemical

TRUE

Attribute: Allergens are only allowed on food items.

EGG

Manage templates

Creating a template is easy, as an automatic template generation is done by defining the entity to import.

In a future release, Sage Distribution and Manufacturing Operations will provide a set of standard templates containing the essential columns needed to import data to ease the initial data loading.

Import data for items

You can import items by creating a template associated with the Item entity. This imports the following entities into a simple file:

Item contains the main information related to the item: name, description, type (good or service, food, chemical), type of management (sold, manufactured, purchased), default units, traceability information, default sales, purchase information and accounting information.
ItemAllergen contains the list of allergens associated with the product (for food).
ItemClassification contains the list of chemical classifications associated with the product (for chemical products).
ItemSite contains item information per site (default quantities, reordering policies, valuation method).
Valuation is a sub-level of detail per item and site.

You can also import separately ItemAllergen, ItemClassification, ItemSites and Valuation.

In different files, you can also import ItemCustomer, ItemSupplier, ItemCustomerPrice, ItemSiteSupplier and ItemPrice records.

Items and item-related data (item-site, item-supplier, item-customer)
Business entities
Customers
Suppliers

Only creation
Only update

Creation if the key does not exist in the database
Update otherwise