Back to VERITICO Homepage
  1. All Collections
  2. Integrations
  3. Most common mistakes with data preparation when integrating VERITICO.cloud with CSV files
Image

Most common mistakes with data preparation when integrating VERITICO.cloud with CSV files

Avatar

By Filip Zapletal

May 23, 2023
When integrating with VERITICO.cloud, there are most common mistakes our partners do. Please take them into account when integrating with us.

Date of transaction

Date of transaction (date_of_transaction) is a required field which means that must be filled in for all transactions, date_of_order is taken into account only with transaction type== 2 (receiving) and is not mandatory (it allows our system to validate lead time and lead time standard deviation for ordering purposes). Date of transactions should be in Y-m-d format (for example 2020-03-10) and no time (like H:i:s) should be exported. 

Format of CSV files

CSV files must: 

  • have all fields as in definition in the same order. Last field is not followed by ;
  • be saved as UTF-8 
  • no field should be skipped (if the value is empty or NULL, then both delimiters should be in the data ;; so that next field is not moved one index left) - recommendation to use CSVLint or other tool like that. Excel is also a help. 
  • all foreign keys must be present - so for example if you have product_id + warehouse_id combination in transactions, it must be also present in product_warehouse and also product_id must be in product file and warehouse_id in warehouse file 
  • should not contain invalid UTF-8 characters - some exporting systems have issues with for example ü or other characters when transporting from windows 125X or ISO-2 encoding.
  • All decimals should be with decimal .(dot) and no thousand separator etc.

Transactions

Transactions - price and stock_price are both prices per unit (eg if you sell 1 piece and then 2 pieces in another transaction then the price should be same and not double in the second transaction.

Customer's reservations

Should you have reservations in your system for goods, you can deduct the reserved amount from "available_supply" - it can be even negative number then - and our system will require order for that amount more.

Zero stock price

We are using stock price in the sale transaction for calculating margin (sales price - stock price = margin). If you don't fill the stock price we are not able to calculate margin and then we are not able to use margin as a criteria for product segmentation.

Length of product name

Length of product_name is 64 and is shortened if necessary. Length of product_id is also 64.

Sales categories

VERITICO is able to work only with one level of categories. Should you have tree structure for your products, it is better to thing upfront if is better for your reporting to have smaller number of categories with more products in one category or to cover the "leaf" part of the tree and have more categories with less products in each.

Suppliers

When exporting the suppliers, do not export whole address list with suppliers and customers. Only suppliers which are in warehouse_product and transactions should be listed. Usually this list is no greater than 100 items. 

Huge negative margin

For example stock price is 500 and sell price is 5, while this might be ok in some cases (extreme sale or employee sale) usually this is due to not having unit prices on both fields or putting stock price for entire stock. Should there be huge negative margin on multiple transactions, then this product might be segmented not correctly.

Case sensitive IDs

For example having product set on one warehouse as abcd123 and on another as ABCD123 might be issue of data - please check that this is the same product and unify it. 

IMAGE

2022 © VERITICO