Lead Time flow 2 — Inventory item lead time, and the Shopify push

What it does. It turns the arrival dates flow 1 recorded into the sentence a customer reads on the product page — how long the wait is, when new stock lands, and whether the item can be bought at all — and pushes that to Shopify.

Why it exists. Everything else in the chain is bookkeeping; this is the part the customer sees. A promise that is too short produces a late order and an apology. One that is too long loses the sale outright. This flow is where a date in NetSuite becomes the sentence someone reads before they spend money.

When it runs. It has no schedule of its own. Flow 1 starts it when flow 1 finishes — four times a day — whether or not flow 1 succeeded. A half-failed flow 1 still results in the storefront being updated.

What it looks at. Four separate lists of products, run one after another: Local, Imported, Rugs, and Item Simple. They are not slices of one list — they are four categories of product, each with its own list, all handled by exactly the same logic.

Who it affects. Customers read the result. Sales and showroom staff quote from it. Buying sees the consequences when a promise turns out to be wrong.

How it decides.

• Bonded stock is settled first: whatever the Sydney backorder does not swallow is spare, and the spare is what can honestly be promised.
• The product is then tested against a ladder of eight rungs, in order, and takes the first that fits — our own warehouse, then on order, then the supplier's stock, then made-to-order, and so on down.
• The wait is expressed as a band, never a date: one of twenty-three, from "2 - 3 days" to "44 - 48 weeks". Beyond forty-eight weeks it becomes "Please contact showroom for lead time".
• Where two arrival dates compete — ours and the supplier's — ours wins if they are within a week of each other, otherwise the earlier one does. A date more than a fortnight past is discarded.
• Three sentences come out of that: the in-stock message, the arrival window, and the out-of-stock message. Each is published separately.
• Whether an out-of-stock item can still be bought is decided separately from the wait, and is the more commercially dangerous of the two.
• Nothing is written unless something actually changed.

Outcomes. The three sentences and the quantities are updated and pushed to Shopify · the product is skipped because nothing changed · the product is updated in NetSuite but never reaches Shopify, because it has no Shopify id · no rung matches and the promise is left blank — defect LT2-D6 · the whole run stops on one item with no supply type — defect LT2-D7.

Bonded stock on hand, and paying off the backorder first LI-BL-INV-011

Why it exists. Stock in the bonded warehouse in Vietnam is ours, but it is not sellable today — it has to cross the water and clear into Sydney first. Before this rule existed that stock was invisible: it could not shorten a promise, and a Sydney warehouse that was oversold published a negative on-order quantity straight to the storefront. This rule makes bonded stock count, but only for what it can honestly do.

Two separate jobs, and it is worth keeping them apart.

The two ideas behind netting.

1. Bonded stock is never available now. It can shorten the arrival promise. It can never produce "Ships in 2 - 3 days".
2. It pays off the backorder first. Sydney's on-order figure already has backorders netted out of it, so a negative figure means we have sold stock we do not have. Bonded stock covers that debt before any of it can be promised to a new customer.

How much bonded stock is spare.

That spare figure is what the website's Quantity On Order is built from, and what allows bonded stock to unlock backordering for supply types that would otherwise refuse it (LI-BL-INV-008). None of it changed in V3.1.

What the Sydney figure already contains. The on-order number this starts from is NetSuite's own figure for the Sydney location, and it counts every inbound transfer order — including ones that have gone stale, and ones moving stock to a showroom. That is deliberate: the question the website's Quantity On Order answers is "how much is coming", not "how much is coming that we could promise". It is a different question from the one flow 1 asks when it picks an arrival date, which is why the two numbers can disagree on the same product without either being wrong.

The one arrival flow 1 cannot see. Flow 1 settles every arrival that has an order behind it — a Sydney purchase order, a bonded purchase order, a transfer order. It reads a report of open order lines, so anything without a line on that report is invisible to it.

Bonded stock sitting on hand with no transfer order raised yet is exactly that: a stock balance, not an order. It has no line, so flow 1 cannot date it. This rule contributes that one date, and nothing else:

The earlier of the two becomes the date everything downstream uses. Only spare bonded stock may set a date — stock still covering the backorder is already promised to someone.

The customer-facing message reads the first arrival. Confirmed by the project owner on 2026-09-23. The three lead-time sentences and the dispatch band are all built from the arrival date this rule settles, and that date is meant to be the first arrival written by flow 1. Shopify runs no logic of its own — it displays the sentences as given. Because a stale first arrival is dropped and the second is promoted into its place, the message only ever has to read one field.

The code does not always honour this. Bonded stock on hand is allowed to beat the first arrival when it lands sooner, so on those items the message is built from a bonded estimate rather than from the first arrival — and the storefront then shows a different date from the one NetSuite holds. That is defect LT2-D17.

This contribution is legacy, and is being removed. The uplift here predates the first-arrival field, which now already accounts for bonded transit. On 2026-09-23 exactly one item held bonded stock available in Vietnam and it already carried a first arrival, so this path can only override a date flow 1 calculated correctly, never fill a gap. It is defect LT2-D17.

A consequence to settle. The confirmed rule that bonded stock on hand with no transfer order is dated at today plus thirty-five days then has no implementation anywhere: flow 1 reads a transaction search and bonded on-hand is a stock balance with no line on it. The rule would be dormant rather than wrong — it currently affects at most one item — but it should be a decision, not an accident.

The two dates can legitimately disagree, and that is not a fault. NetSuite's first-arrival field holds the next documented arrival. The date the customer reads is the next arrival of any kind. On an item with spare bonded stock on hand, the customer's date can be the earlier of the two. They answer different questions. Anyone comparing the product record against the website — customer service especially — needs to know that.

What changed on 2026-09-21. Until V3.1 this rule recalculated the arrival date from scratch and overwrote flow 1's answer. It did two things wrong doing so. It dropped flow 1's date whenever NetSuite's net location on-order was zero or below — a different measure from the one flow 1 uses, so correctly calculated dates were being blanked. And it added the 35-day transit to the raw bonded date a second time, on a field that deliberately includes stock already claimed by a transfer order — promising stock that was already spoken for. Both were removed. Flow 1's date is now passed through untouched.

Outcomes. The item carries the earlier of flow 1's date and a bonded-on-hand date · it carries flow 1's date unchanged, which is the common case · it carries a bonded-derived date when flow 1 found nothing sellable · it carries no date at all, and the customer is sent to the showroom.

The lead-time ladder, and the words the customer reads LI-BL-INV-006

The ladder. Each product is tested against these in order and takes the first one that fits. Nothing further is considered once a rung matches.

The last row is now a real rule, not a gap — changed 2026-09-23. Until that date three of the four conditions never rejected anything (LT2-D21), so rung 8 fired for every product that got that far and "none of the above" was unreachable. A product with no lead time recorded anywhere was told "Please contact showroom for lead time" by accident. Both halves are now fixed: rungs 5, 6 and 8 genuinely check that a lead time exists, and a product matching nothing is given that same wording by rule. The customer sees no difference — that was the point, and it was measured across all 19,225 rows before either change shipped — but the ladder that runs is now the ladder written above.

A gap is reserved between rungs 1 and 2. The business has an open question about whether something belongs between "in our warehouse" and "on order" — showroom-only stock, or an item with one unit on hand and four on order, which today is quoted 2 - 3 days for the whole order. Until it is settled there is no rung there. It is numbered P2 in the project's own rule set, which is why that set counts nine priorities where this ladder shows eight.

The wait is always expressed as a band, never a date. A calculated number of weeks is rounded into one of twenty-three bands — "2 - 3 days", "1 - 2 weeks", … "44 - 48 weeks" — and anything beyond forty-eight weeks becomes "Please contact showroom for lead time".

Three sentences are produced for every product, and each is published separately:

The arrival window is always the arrival date plus seven days — a one-week spread, not a real delivery estimate.

Whose warehouse the sentence names. If the supplier drop-ships, the wording becomes "our partners warehouse" instead of "our warehouse". Two brands are hard-coded by name:

• Ellison Studios — treated as not drop-ship, and any "our partners warehouse" wording is rewritten back to "our warehouse", so the customer is told the stock is ours.
• McMullin & Co. — see LI-BL-INV-008.

Which arrival date wins. After LI-BL-INV-011 has settled ours, two remain: ours and the supplier's.

The band and the sentence can describe different days. The wait band is always counted from our date, even on a product where the comparison above picked the supplier's. So a customer can read a dispatch band measured from one date and an arrival window quoting another. Defect LT2-D13.

When nothing matches, say so — do not leave the last answer standing. A product that reaches the bottom of the ladder, or an In-Store Only product with no showroom stock, should be told to contact the showroom. Today the three sentences are simply left untouched in both cases, so the product keeps whatever promise it was last given, however old. Defect LT2-D6, which covers both.

Which clock the dates are read on. Every date decision here — "is this more than a fortnight past", "how many weeks until it lands", "is the supplier's ETA beyond seventy days", and the bonded-on-hand estimate of today plus thirty-five — is meant to be made on the Australia/Sydney date. The flow declares that timezone for its schedule, but the arithmetic inside it reads the server's clock, which runs on UTC. Between 10am and midnight UTC the server is already on tomorrow's date in Sydney, so those four decisions can land a day out. Flow 1 does this correctly; flow 2 does not. Defect LT2-D14.

Only real changes are written. Eight values are compared against what the product already carries — the three sentences, the dispatch band, the two Shopify quantities, the showroom total and the backorder flag. If any one differs, the product is written and pushed to Shopify. If none does, it is skipped entirely. On a full run around 5,300 products are examined and roughly a hundred are written.

Who signs a change off. The three sentences, the dispatch band and the arrival window are what a customer reads before deciding to buy. A change to any of that wording, or to how a date is chosen, is signed off by the customer service lead before it ships. This is a process rule, not something the code enforces — nothing in Celigo or NetSuite checks it, and the September 2026 changes to the Shopify push went in without it.

The arrival date is not one of the eight, and is never written back to NetSuite. Flow 2 calculates it, uses it for the wording, and sends it to Shopify — but the NetSuite field belongs to flow 1. Nor are the first and second arrival values the Shopify push now carries. A product whose only change is one of those never requalifies, so those metafields fill in as other things move rather than when they actually change. Defect LT2-D15.

Whether an out-of-stock item can still be ordered LI-BL-INV-008

Separate from the lead time, and the more commercially dangerous of the two: this decides whether the storefront will let a customer buy something we do not currently have. Getting it wrong in one direction takes money for stock that may never come; in the other it refuses a sale we could have fulfilled.

Publishing to Shopify — product or variant LI-BL-INV-009

Once NetSuite is updated, the same values go to the storefront. Each product takes one of two routes, and a product that fits neither is silently dropped.

Nine values are sent. Five have been there throughout — the three sentences, the on-order quantity and the warehouse on-hand quantity. Four were added on 2026-09-21 to carry the first and second arrival to the storefront, which is where the business expects sales to read them:

Which searches carry the arrival values, and why two do not. The four saved searches behind this flow do not carry the same columns, and that is deliberate:

A drop-ship item has no arrival, and that is the right answer. The absent columns are not a gap in those two searches; asking them for a Life arrival date would be asking the wrong question. What the customer waits on is the supplier's own lead time, which the ladder already handles.

NetSuite and Shopify must hold the same value. Confirmed by the project owner on 2026-09-22. The arrival metafields are internal visibility only — nothing on the storefront branches on them — so there is no reason for the two systems to differ, and NetSuite is the source of truth because it is written first.

Blank and zero both publish as nothing. Confirmed 2026-09-23. NetSuite distinguishes the two — the first-arrival quantity is null on 78,736 items and an explicit zero on 1,198 — and the distinction is real but is about provenance, not about the business answer:

Both mean nothing on order, which is what a customer or a salesperson needs to know, so carrying the difference through to the storefront would show 0 on some items and nothing on others for no reason either could name. It also removes 379 rows from the reconciliation that were never wrong.

Worked example: 421626 Air Bedside Table (Oak Brown) has never had a purchase order and holds null. 129586 Air Bedside Table (Oak) has seven, all closed, the newest from April 2025, and also holds null. 248034 Abstract Console (Black) has six closed orders but was in scope while the flow ran, so it holds an explicit zero. All three publish nothing.

The rule covers all nine metafields, not only the four arrival ones. Confirmed 2026-09-23. The on-order quantity, the warehouse quantity and the three sentences are held to the same standard. Live gap found the same day: BE-ETH-AIR-VRNOAK holds 0 in NetSuite for the warehouse quantity and has no metafield at all in Shopify, while every comparable item carries 0.

A negative is published as zero, and that is required rather than cosmetic. NetSuite's own location figure is on order less backordered, so it goes negative when an item is oversold — 82169 Adele Armchair reads −1 today. That figure is an input this flow never writes. The field it does write is clamped to zero before publishing, confirmed 2026-09-23 as necessary because other calculations do arithmetic on it and a negative would propagate.

Blank and zero are genuinely different states: NetSuite stores 78,736 items with the 1st PO quantity null, 1,198 with an explicit zero and 1,503 above zero (verified 2026-09-23). So the rule can be honoured exactly rather than approximated.

How the two systems currently fail to match. The quantities are sent unconditionally, so an item with no arrival pushes an empty string into a whole-number metafield. The dates are wrapped in a presence test, which omits them when blank — and omitting is not clearing, so Shopify keeps a value NetSuite has already cleared. Both are defect LT2-D16, and the presence test is the wrong tool for the job: a blank quantity is 0, and a blank date needs the metafield deleted, because a date metafield cannot hold an empty value.

The date stays a date. Confirmed 2026-09-22 — the two arrival dates keep the Shopify date type rather than becoming text, so clearing one requires a delete rather than a blank value.

The warehouse quantity is not what the storefront sells against. It counts our own two warehouses and nothing else — no bonded stock, no showroom floor stock, no supplier stock. The quantity the customer can actually buy adds supplier on-hand on top, and is published through the inventory sync, not here. Two fields named qty_available_wh therefore exist and hold different numbers: this one, and the NetSuite field, which means Life plus supplier. Anyone reading one and assuming the other will be wrong, and nothing in either system flags it.

Confirmed as intended on 2026-09-23, and flagged as a concern to revisit — the project owner may rename one of the two so the names stop colliding. Until then, any reconciliation between NetSuite and Shopify must exclude this pair, or it reports every supplier-stocked item as a mismatch when both values are correct.

Change detection has no comparison for this metafield, nor for any of the four arrival values. An item whose sentences and quantities are all unchanged never requalifies, so those metafields fill in as stock moves rather than across the catalogue at once. See defect LT2-D15.

Known defect — a product that fits neither route is discarded in silence. The router has a variant branch and a product branch and no default, so anything matching neither is dropped with no error and no log. This was first seen in August as LT2-D2, when the Item Simple search did not return the Shopify type at all; that column was added and those products now publish. The mechanism was never fixed — only that one trigger for it. The live trigger today is an item with no Shopify id recorded in NetSuite. Measured 2026-09-23: 220 records a run are discarded this way. Most are items that genuinely are not on Shopify, which is the right outcome reached by the wrong mechanism — but 20 of them are live storefront products, and they lose every update, on every run, permanently. See defect LT2-D19.

Two different fixes are needed and they should not be confused: a default branch that logs stops it being silent, and recording the missing Shopify ids stops it happening.

Nothing is ever read back from Shopify, and a failure is actively reported as a success. The storefront is written to and never compared, so a value changed in Shopify by hand is never noticed. Worse, a rejected write is not noticed either: Shopify answers a rejected metafield with HTTP 200 and the reason inside the response body, and neither import reads the body. A run can log hundreds of successes and have written nothing. See defect LT2-D18 — it is the reason no other defect in this chain can be measured until it is fixed.

Field registry

Every field this automation reads or writes, by the name it carries in the system — what it means, and what changes when it changes. Field ids are in the developer reading.

Inputs — what it reads

Values the automation looks at to make its decision. Change one of these and the decision changes.

Outputs — what it writes

Values the automation puts back. Everything downstream of this rule reads them, so a wrong value here travels.

from a date to a sentence LI-BL-INV-006

which route to Shopify LI-BL-INV-009