For import functionality you can use JSONata expression. JSONata is very powerful and explained in general at http://docs.jsonata.org/overview.html and you can try quite a lot here https://try.jsonata.org.
It gives you the possibility, to map your data to our infrastructure or vice versa in different data mapping profiles.
This is a short manual, what steps are needed to create a custom import mapping and configuration.
Some preconditions must be considered, to make the custom import successful.
The source csv file is comma seperated
Prices and numbers with decimals have a . (point) as decimal seperator
CSV Header columns need to be in [ ] to be identified during the import process
CSV Header columns should not contain any spaces in the naming
CSV Header columns should not contain special characters like the german 'ö' or a dash '-'
To map a custom csv file, it is required to have a valid JSONata expression, which then can be used to map customer fields to our internal database fields.
Example:
[ItemID],[ItemName],[GTIN],[Color],[ColorName],[Size],[Season],[ItemGroup],[SalesPrice],[VAT]
1014079,NIGHT FLIGHT,4051533657809,105000,anthramelange,S,2021 FS,Nlpo7fd_0,119.95,19With that csv file, we need to map the custom header fields to our database fields, which can look as followed:
{
"mainPOSItemId": $.GTIN,
"unitlistprice": $.SalesPrice,
"taxGroupID": $.VAT = 19 ? "2" : $.VAT = 7 ? "1" : "0",
"key": {
"itemIdentifier": {
"gtin": [
$.GTIN
],
"intern": [
$."ItemID"
]
}
},
"itemGroups": [
{
"itemGroup": "relation:item_group:" & $."ItemGroup"
}
],
"texts": {
"name": {
"i18nVariants": {
"de_DE": $."ItemName" & " - " & $."Size" & $."ColorName" & " - " & $."Season"
}
}
},
"additions": [
{
"key": "Color",
"value": $.Color
},
{
"key": "ColorName",
"value": $."ColorName"
},
{
"key": "Size",
"value": $.Size
}
]
}In this JSONata expression, the $.GTIN field is internally mapped to our mainPOSItemId, which is the actual database field in our environment.
[!] The Item Group relation
must be the retail7 internal database id of the needed item group. And therefore the item group need to exist before the import. The database id can be the same like the external one and can be set during REST import.
[!] Additional item identifier
If you want to use additional item identifier, make sure to write them in completely lower case within your JSONata. Capitalized it will not recognize the correct value.
[!] Stock Management
If you want to enable Stock Management for imported items, it is mandatory to add stock management to the JSONata. Following settings enable stock management for imported items:
"stock": {
"stockMaintenanceEnabled": true,
"stockMaintenanceSales": true,
"stockMaintenanceReturns": true,
"stockMaintenanceReservationAllowed": true,
"stockLevel": "SET_COMPONENT",
"stockLowLimit": 0
}
Item JSON Example
{
"mainPOSItemId": $.retail7itemID,
"unitlistprice": $.uvp = "" ? 0 : $.uvp,
"taxGroupID": "2",
"returnAble": $.retournierbar,
"manualPriceOverwriteAllowed": $.preisueberschreibung,
"discountFlag": $.rabattierbar,
"key": {
"itemIdentifier": {
"sortiernummer": [
$.sortiernummerveloconnect
],
"artikelnr": [
$.artikelnr
],
"ean": [
$.ean
]
}
},
"texts": {
"name": {
"i18nVariants": {
"de_DE": $."bezeichnung" & " " & $."farbe" & " " & $."groesse"
}
}
},
"additions": [
{
"key": "Farbe",
"value": $.farbe
},
{
"key": "farbnr",
"value": $."farbnr"
},
{
"key": "Größe",
"value": $.groesse
}
],
"printUsecases": [],
"choiceGroups": [],
"optionItems": [],
"stock": {
"stockMaintenanceEnabled": true,
"stockMaintenanceSales": true,
"stockMaintenanceReturns": true,
"stockMaintenanceReservationAllowed": true,
"stockLevel": "SET_COMPONENT",
"stockLowLimit": 0
}
}
{
"mainPOSItemId": $.retail7itemID,
"unitlistprice": $.uvp = "" ? 0 : $.uvp,
"taxGroupID": "2",
"returnAble": $.retournierbar,
"manualPriceOverwriteAllowed": $.preisueberschreibung,
"discountFlag": $.rabattierbar,
"key": {
"itemIdentifier": {
"sortiernummer": [
$.sortiernummerveloconnect
],
"artikelnr": [
$.artikelnr
],
"ean": [
$.ean
]
}
},
"texts": {
"name": {
"i18nVariants": {
"de_DE": $."bezeichnung" & " " & $."farbe" & " " & $."groesse"
}
}
},
"additions": [
{
"key": "Farbe",
"value": $.farbe
},
{
"key": "Farbnummer",
"value": $."farbnr"
},
{
"key": "Größe",
"value": $.groesse
}
],
"printUsecases": [],
"choiceGroups": [],
"optionItems": [],
"stock": {
"stockMaintenanceEnabled": true,
"stockMaintenanceSales": true,
"stockMaintenanceReturns": true,
"stockMaintenanceReservationAllowed": true,
"stockLevel": "SET_COMPONENT",
"stockLowLimit": 0
}
}Create a new Import/Export Schema
Choose 'Items' as table
Give a meaningfull name
Choose Export → No
Choose Import → Yes
Chose CSV/Excel format → Yes
Create new JSONata definition
JSONata Type → Import
Paste the created JSONata
Create a new Data exchange profile for Items
Within Items, select the tab "Import"
Create a new Config
Provide a meaningful name
Select Custom Import → Yes
Select the mapping expression, that you created earlier
Create Custom fields
The names of the custom fields MUST be the same name as the CSV headers without the [] brackets. The names are case sensitiv, so therefore it must be exactly like the csv header columns.
With all these steps, you can go to Masterdata → Items → Item list and select import. In the import config you will be able to select you newly created configuration. When everything is done right, you can upload the custom csv file and the items will be saved to the database.