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.

EntitySync FROM -> TOFrequency (min)
ProductsMS SQL > OPevery 60 min
SupplierMS SQL > OPevery 60 min
Supplier ProductsMS SQL > OPevery 60 min
Sell OrdersMS SQL > OPevery 60 min
Buy OrdersMS SQL > OPevery 60 min
Receipt LinesMS SQL > OPevery 60 min
Product CompositionsMS SQL > OPevery 60 min
PromotionsMS SQL > OPevery 60 min
Promotion ProductsMS SQL > OPevery 60 min
Buy OrdersOP > MS SQLevery 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, and ProductCompositions.
  • 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_at fields 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

  1. Select the entity and direction in the schedule, preserving the distinct Buy Orders inbound and outbound rows and their different frequencies.
  2. For inbound mappings, compare the exact named table or view with its entity table below; review outbound Buy Orders only in its separate section.
  3. Compare field names and source descriptions literally, including case, punctuation, formats, and expressions.
  4. Check Required markers: explicit yes is marked required; blank is not a claim of optionality.
  5. Verify selected updated_at replication-key annotations and source type/length/precision/date/relationship qualifications.
  6. For Promotions and Promotion Products, apply the documented (*1) conditional rules as written; do not infer behavior for unlisted combinations.
  7. For outbound Buy Orders, preserve buyOrderLines as the line_items JSON 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 dimensionWhat to compare
Entity fieldsLiteral field names, source descriptions, type/format/length/precision and explicit requiredness
Synchronization optionsDirection and schedule; selected updated_at replication keys; connector-specific setup and conditional qualifiers
Outbound structureConnector 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

OptiplyMS SQLRequired
remoteIdThe ID in the remote Systemyes
namestring max length 255yes
skuCodestring max length 255 - The SKU (Stock Keeping Unit) code that your own company uses—
articleCodestring max length 255 - An article code that your own company uses that is not the SKU or the EAN—
pricedecimal max 9 integer digits, will round decimals to 2 places—
unlimitedStockboolean - By setting this boolean to TRUE, stock tracking can be disabled. Set this to FALSE for products you want to buy.yes
stockLevelinteger, 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
statusenum - enabled or disabled—
eanCodeBarcode - The EAN code used for selling this Product—
notBeingBoughtboolean - When TRUE, Stock level advice for this product will be 0. Product will effectively not be bought—
created_atdatetime with time zone, format "%Y-%m-%dT%H:%M:%SZ"—
updated_atdatetime with time zone, format "%Y-%m-%dT%H:%M:%SZ"(to be used as Replication Key)yes
deleted_atdatetime with time zone, format "%Y-%m-%dT%H:%M:%SZ"—

Suppliers

OptiplyMS SQLRequired
remoteIdThe ID in the remote Systemyes
namestring max length 255yes
emailsstring[] - (exemples: ["example@example.com"] or ["example@example.com";"example2@example.com"]—
deliveryTimeinteger—
created_atdatetime with time zone, format "%Y-%m-%dT%H:%M:%SZ"—
updated_atdatetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" (to be used as Replication Key)yes
deleted_atdatetime with time zone, format "%Y-%m-%dT%H:%M:%SZ"—

Supplier Products

OptiplyMS SQLRequired
remoteIdThe ID in the remote Systemyes
namestring max length 255yes
skuCodestring max length 255—
eanCodestring max length 255—
articleCodestring max length 255—
pricedecimal max 9 integer digits, will round decimals to 2 places—
minimumPurchaseQuantityinteger - 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.—
lotSizeinteger - 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.—
productIdlong - The remoteId used in Products Tableyes
supplierIdlongyes
preferredboolean - Indicates this is a preferred supplier - can you be set one per Product—
statusenum - enabled or disabled. Use to mimic deletes.—
deliveryTimeinteger - Supplier lead time per supplier product (days)—
created_atdatetime with time zone, format "%Y-%m-%dT%H:%M:%SZ"—
updated_atdatetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" (to be used as Replication Key)yes
deleted_atdatetime with time zone, format "%Y-%m-%dT%H:%M:%SZ"—

Sell Orders

OptiplyMS SQLRequired
remoteIdThe ID in the remote Systemyes
placeddatetime with time zone - date on which the order was placed and is to be sentyes
totalValuedecimal max 17 integer digits, will round decimals to 2 placesyes
updated_atdatetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" (to be used as Replication Key)yes
deleted_atdatetime with time zone, format "%Y-%m-%dT%H:%M:%SZ"—

Sell Order Lines

OptiplyMS SQLRequired
remoteIdThe ID in the remote Systemyes
quantityintegeryes
productIdlong - The remoteId used in Products Table - ID of the product that's related with this sellOrderLineyes
sellOrderIdlong - The remoteId used in Sell Order Table - ID of the sellOrder that this sellOrderLine is part ofyes
subtotalValuedecimal max 17 integer digits, will round decimals to 2 placesyes
deleted_atdatetime with time zone, format "%Y-%m-%dT%H:%M:%SZ"—
updated_atdatetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" (to be used as Replication Key)yes

Buy Orders

OptiplyMS SQLRequired
remoteIdThe ID in the remote Systemyes
completeddatetime with time zone - Used to close the order. Fill when no products are left to be received.—
placeddatetime with time zone - This field denotes the date and the time BuyOrder is placed.yes
totalValuedecimal max 17 integer digits, will round decimals to 2 placesyes
updated_atdatetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" (to be used as Replication Key)yes
deleted_atdatetime with time zone, format "%Y-%m-%dT%H:%M:%SZ"—
referenceBuyOrders.idinteger - this should only be filled for BuyOrders synced from Optiply to MS SQL.Yes
supplierIdlong - The remoteId used in Suppliers Table - ID of the supplierID that's related with this buyOrderYes

Buy Order Lines

OptiplyMS SQLRequired
remoteIdThe ID in the remote Systemyes
quantityintegeryes
productIdlong - The remoteId used in Products Table - ID of the product that's related with this sellOrderLineyes
BuyOrderIdlong - The remoteId used in Buy Order Table - ID of the BuyOrder that this BuyOrderLine belongs toyes
subtotalValuedecimal max 17 integer digits, will round decimals to 2 placesyes
created_atdatetime with time zone, format "%Y-%m-%dT%H:%M:%SZ"—
updated_atdatetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" (to be used as Replication Key)yes
deleted_atdatetime with time zone, format "%Y-%m-%dT%H:%M:%SZ"—
referenceBuyOrders.lineitems.lineidinteger - this should only be filled for BuyOrders synced from Optiply to MS SQL.Yes

Receipt Lines

OptiplyMS SQLRequired
remoteIdThe ID in the remote Systemyes
quantityintegeryes
buyOrderLineIdinteger - ID of the BuyOrderLine that owns this ReceiptLine.yes
occurreddatetime with time zone, format "%Y-%m-%dT%H:%M:%SZ"yes
deleted_atdatetime with time zone, format "%Y-%m-%dT%H:%M:%SZ"—
updated_atdatetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" (to be used as Replication Key)yes
referenceReceiptLines.idinteger - this should only be filled for ReceiptLines synced from Optiply to MS SQL.yes

Product Compositions

OptiplyMS SQLRequired
remoteIdThe ID in the remote Systemyes
composedProductIdlongyes
partProductIdlongyes
partQuantityinteger - Must be at least 1.yes
created_atdatetime with time zone, format "%Y-%m-%dT%H:%M:%SZ"—
updated_atdatetime with time zone, format "%Y-%m-%dT%H:%M:%SZ" (to be used as Replication Key)yes
deleted_atdatetime with time zone, format "%Y-%m-%dT%H:%M:%SZ"—

Promotions

OptiplyMS SQLRequired
remoteIdThe ID in the remote Systemyes
namestring max length 255yes
entireShopboolean - POST only. Setting this to true will cause every product on the account to get added to the Promotion.—
startDatedatetime 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
endDatedatetime 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—
enabledboolean—
updated_atdatetime 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

OptiplyMS SQLRequired
remoteIdThe ID in the remote Systemyes
promotionIdlongyes
specificUpliftType (*1“absolute”, “relative” or "close_out"—
specificUpliftIncrease (*1)integer—
updated_atdatetime 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 sourceMS SQL destination
buyOrder.idid
buyOrder.placedplaced
buyOrder.expectedDeliveryDatedelivery_date
supplier.remoteIdsupplier_remoteId
supplier.namesupplier_name
buyOrderLinesline_items (JSON with the line attributes)

Each entry in line_items contains these documented child mappings:

Optiply sourceField within line_items
buyOrderLine.idline_id
products.remoteIdproduct_remoteId
products.skuCodeproduct_sku
buyOrderLine.quantityquantity