Requiredness: “Yes” is shown only where the source marks a field required. A blank source cell means requiredness is not stated here; it does not mean optional.
Source-described query setup
The source says customers use fixed query names for the listed data categories and tailored SQL queries. It describes a replication-key column for incremental synchronization; the inbound schemas below explicitly label selected updated_at fields as replication keys.
The source lists these fixed query/table names: Products, SalesOrders, SalesOrdersLines, Suppliers, SupplierProducts, BuyOrders, BuyOrdersLines, ItemDeliveries, and ProductCompositions.
The source says datasets may be created using tables or views when they have the required names and structure. It also lists connection inputs by label (host name, port, database, schema, user, password); no credential values are included here.
Synchronisation schedule and direction
The source labels this an overview of what and when is synchronised. It documents distinct inbound and outbound Buy Orders schedules; they are shown as separate rows. Your enabled flows or agreed schedule may differ.
| Entity | Sync FROM -> TO | Frequency (min) |
|---|---|---|
| Products | MS SQL > OP | every 60 min |
| Supplier | MS SQL > OP | every 60 min |
| Supplier Products | MS SQL > OP | every 60 min |
| Sell Orders | MS SQL > OP | every 60 min |
| Buy Orders | MS SQL > OP | every 60 min |
| Receipt Lines | MS SQL > OP | every 60 min |
| Product Compositions | MS SQL > OP | every 60 min |
| Promotions | MS SQL > OP | every 60 min |
| Promotion Products | MS SQL > OP | every 60 min |
| Buy Orders | OP > MS SQL | every 10 min |
Purpose
Use this mapping reference to compare documented MS SQL-to-Optiply schemas (including Promotions and Promotion Products) with the separate Optiply-to-MS SQL Buy Orders output. It is a schema/mapping review aid, not evidence of an account's configuration or live synchronization.
Prerequisites
- Identify the exact entity and direction; the schedule has nine inbound MS SQL > OP rows at every 60 min and a distinct outbound Buy Orders OP > MS SQL row at every 10 min.
- For the documented inbound query setup, use the fixed names
Products,SalesOrders,SalesOrdersLines,Suppliers,SupplierProducts,BuyOrders,BuyOrdersLines,ItemDeliveries, andProductCompositions. - The source permits datasets implemented as tables or views when the required names and structure match, and describes tailored SQL queries and a replication-key column for incremental identification of new or updated records. Inbound rows explicitly mark selected
updated_atfields as replication keys. - Connection input labels in the source are host name, port, database, schema, user, and password. No values, credential-handling procedure, or UI workflow are documented here.
- Have a proposed schema/query definition available for non-executing comparison; do not expose or include credentials in a review artifact.
Steps
- Select the entity and direction in the schedule, preserving the distinct Buy Orders inbound and outbound rows and their different frequencies.
- For inbound mappings, compare the exact named table or view with its entity table below; review outbound Buy Orders only in its separate section.
- Compare field names and source descriptions literally, including case, punctuation, formats, and expressions.
- Check
Requiredmarkers: explicityesis marked required; blank is not a claim of optionality. - Verify selected
updated_atreplication-key annotations and source type/length/precision/date/relationship qualifications. - For Promotions and Promotion Products, apply the documented
(*1)conditional rules as written; do not infer behavior for unlisted combinations. - For outbound Buy Orders, preserve
buyOrderLinesas theline_itemsJSON group and check all four child mappings under it. Record mismatches without executing SQL or changing a dataset.
Fields and columns (field mappings and options)
| Review dimension | What to compare |
|---|---|
| Entity fields | Literal field names, source descriptions, type/format/length/precision and explicit requiredness |
| Synchronization options | Direction and schedule; selected updated_at replication keys; connector-specific setup and conditional qualifiers |
| Outbound structure | Connector destination names and the buyOrderLines → line_items parent with its four nested children |
The inbound schemas cover the nine base entities, Promotions, and Promotion Products; outbound Buy Orders mappings appear in a separate section. Tables preserve source type/default/requiredness language, replication-key annotations, the promotion (*1) conditions, and outbound direction-specific reference wording.
Expected result
A review should return a per-entity/direction comparison of documented table-or-view name, field literals, explicit requiredness, replication key, relevant promotion conditions, and applicable schedule. A match means only that the proposed definition agrees with this source reference; it does not prove access, query execution, synchronization, or tenant setup.
Limitations
The source describes names, qualifying structure for tables or views, connection input labels, schedules, schemas, selected replication keys, and outbound mappings. It does not establish actual account values, object existence, active configuration, enabled flows, successful runs, or current data. Blank requiredness is unstated. The per-day promotion date and uplift rules apply only as documented; this mapping is not an expanded SQL implementation specification.
Errors and recovery
If a definition does not match, check the entity, direction, field name, requiredness marker, and applicable qualifier again. Record the exact difference without executing SQL or changing a query, schema, or data. Leave any field or relationship not defined here unresolved rather than inferring a correction.
Mapped fields
Products
| Optiply | MS SQL | Required |
|---|---|---|
| remoteId | The ID in the remote System | yes |
| name | string max length 255 | yes |
| skuCode | string max length 255 - The SKU (Stock Keeping Unit) code that your own company uses | — |
| articleCode | string max length 255 - An article code that your own company uses that is not the SKU or the EAN | — |
| price | decimal max 9 integer digits, will round decimals to 2 places | — |
| unlimitedStock | boolean - By setting this boolean to TRUE, stock tracking can be disabled. Set this to FALSE for products you want to buy. | yes |
| stockLevel | integer, can be negative - The current physical level of stock minus already sold quantity. This calculation needs to be done in the Source system. Often called freeStock in other systems. | yes |
| status | enum - enabled or disabled | — |
| eanCode | Barcode - The EAN code used for selling this Product | — |
| notBeingBought | boolean - When TRUE, Stock level advice for this product will be 0. Product will effectively not be bought | — |
| created_at | datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" | — |
| updated_at | datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ"(to be used as Replication Key) | yes |
| deleted_at | datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" | — |
Suppliers
| Optiply | MS SQL | Required |
|---|---|---|
| remoteId | The ID in the remote System | yes |
| name | string max length 255 | yes |
| emails | string[] - (exemples: ["example@example.com"] or ["example@example.com";"example2@example.com"] | — |
| deliveryTime | integer | — |
| created_at | datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" | — |
| updated_at | datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" (to be used as Replication Key) | yes |
| deleted_at | datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" | — |
Supplier Products
| Optiply | MS SQL | Required |
|---|---|---|
| remoteId | The ID in the remote System | yes |
| name | string max length 255 | yes |
| skuCode | string max length 255 | — |
| eanCode | string max length 255 | — |
| articleCode | string max length 255 | — |
| price | decimal max 9 integer digits, will round decimals to 2 places | — |
| minimumPurchaseQuantity | integer - The minimum quantity that you will purchase of this product if you purchase it. This is 1 by default and is only allowed to be greater than or equal to 1. | — |
| lotSize | integer - The lot size used when buying this product. E.g. when products are sold in sixpacks, the lotSize is 6. This is 1 by default and is only allowed to be greater than or equal to 1. | — |
| productId | long - The remoteId used in Products Table | yes |
| supplierId | long | yes |
| preferred | boolean - Indicates this is a preferred supplier - can you be set one per Product | — |
| status | enum - enabled or disabled. Use to mimic deletes. | — |
| deliveryTime | integer - Supplier lead time per supplier product (days) | — |
| created_at | datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" | — |
| updated_at | datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" (to be used as Replication Key) | yes |
| deleted_at | datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" | — |
Sell Orders
| Optiply | MS SQL | Required |
|---|---|---|
| remoteId | The ID in the remote System | yes |
| placed | datetime with time zone - date on which the order was placed and is to be sent | yes |
| totalValue | decimal max 17 integer digits, will round decimals to 2 places | yes |
| updated_at | datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" (to be used as Replication Key) | yes |
| deleted_at | datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" | — |
Sell Order Lines
| Optiply | MS SQL | Required |
|---|---|---|
| remoteId | The ID in the remote System | yes |
| quantity | integer | yes |
| productId | long - The remoteId used in Products Table - ID of the product that's related with this sellOrderLine | yes |
| sellOrderId | long - The remoteId used in Sell Order Table - ID of the sellOrder that this sellOrderLine is part of | yes |
| subtotalValue | decimal max 17 integer digits, will round decimals to 2 places | yes |
| deleted_at | datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" | — |
| updated_at | datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" (to be used as Replication Key) | yes |
Buy Orders
| Optiply | MS SQL | Required |
|---|---|---|
| remoteId | The ID in the remote System | yes |
| completed | datetime with time zone - Used to close the order. Fill when no products are left to be received. | — |
| placed | datetime with time zone - This field denotes the date and the time BuyOrder is placed. | yes |
| totalValue | decimal max 17 integer digits, will round decimals to 2 places | yes |
| updated_at | datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" (to be used as Replication Key) | yes |
| deleted_at | datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" | — |
| reference | BuyOrders.idinteger - this should only be filled for BuyOrders synced from Optiply to MS SQL. | Yes |
| supplierId | long - The remoteId used in Suppliers Table - ID of the supplierID that's related with this buyOrder | Yes |
Buy Order Lines
| Optiply | MS SQL | Required |
|---|---|---|
| remoteId | The ID in the remote System | yes |
| quantity | integer | yes |
| productId | long - The remoteId used in Products Table - ID of the product that's related with this sellOrderLine | yes |
| BuyOrderId | long - The remoteId used in Buy Order Table - ID of the BuyOrder that this BuyOrderLine belongs to | yes |
| subtotalValue | decimal max 17 integer digits, will round decimals to 2 places | yes |
| created_at | datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" | — |
| updated_at | datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" (to be used as Replication Key) | yes |
| deleted_at | datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" | — |
| reference | BuyOrders.lineitems.lineidinteger - this should only be filled for BuyOrders synced from Optiply to MS SQL. | Yes |
Receipt Lines
| Optiply | MS SQL | Required |
|---|---|---|
| remoteId | The ID in the remote System | yes |
| quantity | integer | yes |
| buyOrderLineId | integer - ID of the BuyOrderLine that owns this ReceiptLine. | yes |
| occurred | datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" | yes |
| deleted_at | datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" | — |
| updated_at | datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" (to be used as Replication Key) | yes |
| reference | ReceiptLines.idinteger - this should only be filled for ReceiptLines synced from Optiply to MS SQL. | yes |
Product Compositions
| Optiply | MS SQL | 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 time zone, format "%Y-%m-%dT%H:%M:%SZ" | — |
| updated_at | datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" (to be used as Replication Key) | yes |
| deleted_at | datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" | — |
Promotions
| Optiply | MS SQL | Required |
|---|---|---|
| remoteId | The ID in the remote System | yes |
| name | string max length 255 | yes |
| entireShop | boolean - POST only. Setting this to true will cause every product on the account to get added to the Promotion. | — |
| startDate | datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ"Whatever time is set gets discarded as promotions are only supported on a “per-day” basis. | yes |
| endDate | datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ"Whatever time is set gets discarded as promotions are only supported on a “per-day” basis. | yes |
| upliftType (*1) | “absolute”, “relative” or "close_out" | — |
| upliftIncrease (*1) | integer | — |
| enabled | boolean | — |
| updated_at | datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" (to be used as Replication Key) | yes |
Conditions for Promotions fields marked (*1): If upliftType is "close_out", upliftIncrease will be 0; if upliftType is "relative", upliftIncrease must not be null.
Promotion Products
| Optiply | MS SQL | Required |
|---|---|---|
| remoteId | The ID in the remote System | yes |
| promotionId | long | yes |
| specificUpliftType (*1 | “absolute”, “relative” or "close_out" | — |
| specificUpliftIncrease (*1) | integer | — |
| updated_at | datetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" (to be used as Replication Key) | yes |
Conditions for Promotion Products fields marked (*1): If specificUpliftType is "close_out", specificUpliftIncrease will be 0; if specificUpliftType is not "close_out", specificUpliftType and specificUpliftIncrease must either both or neither be filled in.
Outbound mappings documented by the source
This section is separate from the inbound MS SQL schemas: the source labels the flow Optiply to MS SQL and says a table named BuyOrders is automatically created in customer MS SQL. The left column is the Optiply source and the right column is the MS SQL destination.
Buy Orders from Optiply to MS SQL
| Optiply source | MS SQL destination |
|---|---|
buyOrder.id | id |
buyOrder.placed | placed |
buyOrder.expectedDeliveryDate | delivery_date |
supplier.remoteId | supplier_remoteId |
supplier.name | supplier_name |
buyOrderLines | line_items (JSON with the line attributes) |
Each entry in line_items contains these documented child mappings:
| Optiply source | Field within line_items |
|---|---|
buyOrderLine.id | line_id |
products.remoteId | product_remoteId |
products.skuCode | product_sku |
buyOrderLine.quantity | quantity |