🔗 MS SQL: Data Mapping & Synchronisation

This guide details the exact data flow and schema requirements between your Microsoft SQL database and Optiply. It outlines the synchronisation schedule, required data formats, and provides example SQL queries with the necessary replication key logic.

⏱️ Synchronisation General Board

The following table outlines the data entities and their update frequencies.

Entity	Direction	Frequency
Products	MS SQL → Optiply	Every 60 min
Suppliers	MS SQL → Optiply	Every 60 min
Supplier Products	MS SQL → Optiply	Every 60 min
Sell Orders	MS SQL →Optiply	Every 60 min
Buy Orders	MS SQL → Optiply	Every 60 min
Receipt Lines	MS SQL → Optiply	Every 60 min
Product Compositions	MS SQL → Optiply	Every 60 min
Promotions	MS SQL → Optiply	Every 60 min
Promotion Products	MS SQL → Optiply	Every 60 min
Buy Orders (Export)	Optiply → MS SQL	Every 10 min

🗺️ Data Mapping Requirements & Examples

1. Products

Optiply	Data Type & Details	Required
remoteId	The ID in the remote System.	Yes
name	String (Max length 255).	Yes
skuCode	String (Max length 255) - The internal SKU code your company uses.	No
articleCode	String (Max length 255) - An internal article code (not SKU or EAN).	No
price	Decimal (Max 9 integer digits, rounds to 2 decimal places).	No
unlimitedStock	Boolean - Set to `TRUE` to disable stock tracking. Set to `FALSE` for products you want to buy.	Yes
stockLevel	Integer (can be negative) - The physical stock minus already sold quantity (calculated in the source system).	Yes
status	Enum - `enabled` or `disabled`.	No
eanCode	Barcode - The EAN code used for selling the product.	No
notBeingBought	Boolean - When `TRUE`, stock advice is 0 (product will not be bought).	No
created_at	Datetime with timezone.	No
updated_at	Datetime with timezone. (Replication Key)	Yes
deleted_at	Datetime with timezone.	No

⚠️ Important Formatting Note:

All datetime fields must include the time zone and be strictly formatted as: "%Y-%m-%dT%H:%M:%SZ". The updated_at field is always required as it acts as your Replication Key for incremental syncing.

Example:

SELECT yt.id as remote_id,

yt.big_name as name, yt.sku as skuCode,

yt.Code as articleCode, yt.price as price,

yt.managedStock as unlimitedStock,

yt.stock as stockLevel,

yt.status as status,

yt.EAN as eanCode,

yt.created_at as created_at,

yt.updated_at as updated_at,

yt.deleted as deleted_at

FROM your_table yt

WHERE {replication_key_condition}

//replication_key_condition: {replication_key} >= {formatted_replication_key_value}

2. Suppliers

Optiply	Data Type & Details	Required
remoteId	The ID in the remote System.	Yes
name	String (Max length 255).	Yes
emails	String Array - e.g., `["example@example.com"]` or `["email1@x.com";"email2@x.com"]`.	No
deliveryTime	Integer.	No
created_at	Datetime with timezone.	No
updated_at	Datetime with timezone. (Replication Key)	Yes
deleted_at	Datetime with timezone.	No

Example:

SELECT yt.id as remote_id,

yt.big_name as name,

yt.email as emails,

yt.deliveryTime as deliveryTime,

yt.created_at as created_at,

yt.deleted_at as deleted_at,

yt.updated_at as updated_at

FROM your_table yt

WHERE {replication_key_condition}

//replication_key_condition: {replication_key} >= {formatted_replication_key_value}

3. Supplier Products

Optiply	Data Type & Details	Required
remoteId	The ID in the remote System.	Yes
name	String (Max length 255).	Yes
skuCode	String (Max length 255).	No
eanCode	String (Max length 255).	No
articleCode	String (Max length 255).	No
price	Decimal (Max 9 integer digits, rounds to 2 decimal places).	No
minimumPurchaseQuantity	Integer - Minimum quantity to purchase. Default is 1 (must be $\ge$ 1).	No
lotSize	Integer - Lot size used when buying (e.g., 6 for a sixpack). Default is 1 (must be $\ge$ 1).	No
productId	Long - The `remoteId` used in the Products Table.	Yes
supplierId	Long - The `remoteId` used in the Suppliers Table.	Yes
preferred	Boolean - Indicates if this is the preferred supplier (one per product).	No
status	Enum - `enabled` or `disabled`. Use to mimic deletes.	No
deliveryTime	Integer - Supplier lead time per product in days.	No
created_at	Datetime with timezone.	No
updated_at	Datetime with timezone. (Replication Key)	Yes
deleted_at	Datetime with timezone.	No

Example:

SELECT yt.id as remote_id,

yt.big_name as name,

yt.sku as skuCode,

yt.EAN as eanCode,

yt.Code as articleCode,

yt.price as price,

yt.moq as minimumPurchaseQuantity,

yt.lot as lotSize,

yt.pId as productId,

yt.sId as supplierId,

yt.preferred as preferred,

yt.status as status,

yt.deliveryTime as deliveryTime,

yt.created_at as created_at,

yt.updated_at as updated_at,

yt.deleted as deleted_at

FROM your_table yt

WHERE {replication_key_condition}

//replication_key_condition: {replication_key} >= {formatted_replication_key_value}

4. Sell Orders

Optiply	Data Type & Details	Required
remoteId	The ID in the remote System.	Yes
placed	Datetime with timezone - Date the order was placed.	Yes
totalValue	Decimal (Max 17 integer digits, rounds to 2 decimal places).	Yes
updated_at	Datetime with timezone. (Replication Key)	Yes
deleted_at	Datetime with timezone.	No

Example:

SELECT yt.id as remote_id,

yt.created as placed,

yt.total as totalValue,

yt.deleted_at as deleted_at,

yt.updated_at as updated_at

FROM your_table yt

WHERE {replication_key_condition}

//replication_key_condition: {replication_key} >= {formatted_replication_key_value}

5. Sell Order Lines

Optiply	Data Type & Details	Required
remoteId	The ID in the remote System.	Yes
quantity	Integer.	Yes
productId	Long - The `remoteId` of the related Product.	Yes
sellOrderId	Long - The `remoteId` of the parent Sell Order.	Yes
subtotalValue	Decimal (Max 17 integer digits, rounds to 2 decimal places).	Yes
updated_at	Datetime with timezone. (Replication Key)	Yes
deleted_at	Datetime with timezone.	No

Example:

SELECT yt.id as remote_id,

yt.qty as quantity,

yt.productId as productId,

yt.sellOrderId as sellOrderId,

yt.subtotalValue as subtotalValue,

yt.deleted_at as deleted_at,

yt.updated_at as updated_at

FROM your_table yt

WHERE {replication_key_condition}

//replication_key_condition: {replication_key} >= {formatted_replication_key_value}

6. Buy Orders

Optiply	Data Type & Details	Required
remoteId	The ID in the remote System.	Yes
completed	Datetime with timezone - Fill when no products are left to be received.	No
placed	Datetime with timezone - Date and time the Buy Order is placed.	Yes
totalValue	Decimal (Max 17 integer digits, rounds to 2 decimal places).	Yes
updated_at	Datetime with timezone. (Replication Key)	Yes
deleted_at	Datetime with timezone.	No
reference	Integer - Map to `BuyOrders.id`. Note: Only fill for Buy Orders synced from Optiply to MS SQL.	Yes
supplierId	Long - The `remoteId` of the related Supplier.	Yes

Example:

SELECT yt.id as remote_id,

yt.completed as completed,

yt.created as placed,

yt.total as totalValue,

yt.deleted_at as deleted_at,

yt.updated_at as updated_at

FROM your_table yt

WHERE {replication_key_condition}

//replication_key_condition: {replication_key} >= {formatted_replication_key_value}

7. Buy Order Lines

Optiply	Data Type & Details	Required
remoteId	The ID in the remote System.	Yes
quantity	Integer.	Yes
productId	Long - The `remoteId` of the related Product.	Yes
BuyOrderId	Long - The `remoteId` of the parent Buy Order.	Yes
subtotalValue	Decimal (Max 17 integer digits, rounds to 2 decimal places).	Yes
created_at	Datetime with timezone.	No
updated_at	Datetime with timezone. (Replication Key)	Yes
deleted_at	Datetime with timezone.	No
reference	Integer - Map to `BuyOrders.line_items.line_id`. Note: Only fill for Buy Orders synced from Optiply to MS SQL.	Yes

Example:

SELECT yt.id as remote_id,

yt.qty as quantity,

yt.productId as productId,

yt.BuyOrderId as BuyOrderId,

yt.subtotalValue as subtotalValue,

yt.created_at as created_at,

yt.deleted_at as deleted_at,

yt.updated_at as updated_at

FROM your_table yt

WHERE {replication_key_condition}

//replication_key_condition: {replication_key} >= {formatted_replication_key_value}

8. Receipt Lines

Optiply	Data Type & Details	Required
remoteId	The ID in the remote System.	Yes
quantity	Integer.	Yes
buyOrderLineId	Integer - ID of the parent BuyOrderLine.	Yes
occurred	Datetime with timezone.	Yes
updated_at	Datetime with timezone. (Replication Key)	Yes
deleted_at	Datetime with timezone.	No
reference	Integer - Map to `ReceiptLines.id`. Note: Only fill for Receipt Lines synced from Optiply to MS SQL.	Yes

Example:

SELECT yt.id as remote_id,

yt.qty as quantity,

yt.buyOrderLineId as buyOrderLineId,

yt.occurred as occurred,

yt.deleted_at as deleted_at,

yt.updated_at as updated_at

FROM your_table yt

WHERE {replication_key_condition}

//replication_key_condition: {replication_key} >= {formatted_replication_key_value}

9. Product Compositions

Optiply	Data Type & Details	Required
remoteId	The ID in the remote System.	Yes
composedProductId	Long.	Yes
partProductId	Long.	Yes
partQuantity	Integer - Must be at least 1.	Yes
created_at	Datetime with timezone.	No
updated_at	Datetime with timezone. (Replication Key)	Yes
deleted_at	Datetime with timezone.	No

Example:

SELECT yt.id as remote_id,

yt.composedProductId as composedProductId,

yt.partProductId as partProductId,

yt.qty as partQuantity,

yt.created_at as created_at,

yt.deleted_at as deleted_at,

yt.updated_at as updated_at

FROM your_table yt

WHERE {replication_key_condition}

//replication_key_condition: {replication_key} >= {formatted_replication_key_value}

10. Promotions

⚠️ Date Note: Whatever time is set in startDate and endDate gets discarded, as promotions are only supported on a “per-day” basis.

Optiply	Data Type & Details	Required
remoteId	The ID in the remote System.	Yes
name	String (Max length 255).	Yes
entireShop	Boolean (POST only) - Setting `true` adds every product to the promotion.	No
startDate	Datetime with timezone.	Yes
endDate	Datetime with timezone.	Yes
upliftType	String - `"absolute"`, `"relative"`, or `"close_out"`. (*1)	No
upliftIncrease	Integer. (*1)	No
enabled	Boolean.	No
updated_at	Datetime with timezone. (Replication Key)	Yes

(1) Conditions for Uplift:

If upliftType is "close_out", then upliftIncrease will be 0.
If upliftType is "relative", then upliftIncrease must not be null.

Example:

SELECT yt.id as remote_id,

yt.enabled as enabled,

yt.entireShop as entireShop,

yt.startDate as startDate,

yt.endDate as endDate,

yt.upliftType as upliftType,

yt.upliftIncrease as upliftIncrease,

yt.updated_at as updated_at

FROM your_table yt

WHERE {replication_key_condition}

//replication_key_condition: {replication_key} >= {formatted_replication_key_value}

11. Promotion Products

Optiply	Data Type & Details	Required
remoteId	The ID in the remote System.	Yes
productId	Boolean.	Yes
promotionId	Long.	Yes
specificUpliftType	String - `"absolute"`, `"relative"`, or `"close_out"`. (*1)	No
specificUpliftIncrease	Integer. (*1)	No
updated_at	Datetime with timezone. (Replication Key)	Yes

(1) Conditions for Specific Uplift:

If specificUpliftType is "close_out", then specificUpliftIncrease will be 0.
If specificUpliftType is NOT "close_out", then both fields must either both be filled in, or neither be filled in.

Example:

SELECT yt.id as remote_id,

yt.productId as productId,

yt.promotionId as promotionId,

yt.specificUpliftType as specificUpliftType,

yt.specificUpliftIncrease as specificUpliftIncrease,

yt.updated_at as updated_at

FROM your_table yt

WHERE {replication_key_condition}

//replication_key_condition: {replication_key} >= {formatted_replication_key_value}

📤 Optiply to MS SQL (Exporting Buy Orders)

For Buy Orders sent from Optiply to your MS SQL database, Optiply will automatically create a table in your database named BuyOrders (updating every 10 minutes).

This table uses the following schema:

Optiply Source		MS SQL Column Name
`buyOrder.id`		`id`	-
`buyOrder.placed`		`placed`	-
`buyOrder.expectedDeliveryDate`		`delivery_date`	-
`supplier.remoteId`		`supplier_remoteId`	-
`supplier.name`		`supplier_name`	-
`buyOrderLines`	buyOrderLine.id	`line_items`	line_id
	products.remoteId	(JSON with the line attributes)	product_remoteId
	products.skuCode		product_sku
	buyOrderLine.quantity		quantity

Example:

❓ Frequently Asked Questions (FAQs)

How should I format dates and times?

All datetime fields must be passed as a string with the timezone included, strictly following this format: "%Y-%m-%dT%H:%M:%SZ".

How is the `stockLevel` calculated?

Optiply requires the free stock. Your SQL query must calculate the current physical level of stock minus the already sold/reserved quantity directly in the source system before passing it to Optiply.

What does "entireShop" do in Promotions?

The entireShop field is a POST-only Boolean. If you pass true in your query, Optiply will automatically apply the promotion to every single product in your account, saving you from mapping individual Promotion Products.

MS SQL - Data mapping & Syncronisation

🔗 MS SQL: Data Mapping & Synchronisation

⏱️ Synchronisation General Board

🗺️ Data Mapping Requirements & Examples

1. Products

2. Suppliers

3. Supplier Products

4. Sell Orders

5. Sell Order Lines

6. Buy Orders

7. Buy Order Lines

8. Receipt Lines

9. Product Compositions

10. Promotions

11. Promotion Products

📤 Optiply to MS SQL (Exporting Buy Orders)

❓ Frequently Asked Questions (FAQs)

How should I format dates and times?

How is the stockLevel calculated?

What does "entireShop" do in Promotions?

How is the `stockLevel` calculated?