QuickBooks to EDGEBIC: The Data Mapping Reference

Mapping QuickBooks data into EDGEBIC is a column-mapping exercise rather than a development project: you export items and open orders to Excel or CSV, drag each source column heading onto the matching EDGEBIC field once, and save it as a reusable import mask. Because QuickBooks holds no work centers or routings, that manufacturing layer is built once inside EDGEBIC. This reference lists what maps to what, which columns are mandatory, how unit conversion and blank cells behave, and what every row outcome means when a run finishes.

EDGEBIC by User Solutions integrates with QuickBooks through those masks rather than a certified connector, so nothing is installed inside the company file. If you have not read the end-to-end version, start with the complete QuickBooks integration guide. This post is the lookup table you keep open while building masks.

The load order

Import in dependency order. Later files reference records that earlier files created. For QuickBooks the twist is that the first two entities are built in EDGEBIC rather than exported.

Two more entity types have no QuickBooks origin and are built from a spreadsheet you write: Shift (one row per shift with a start and end pair per weekday, a blank pair meaning the day is off) and PlantHoliday (closures by name and date). These define the calendar every date is computed against, so they deserve ten careful minutes.

Auto-create is on by default, so a routing spreadsheet naming a work center that does not exist yet creates it on the fly. That is a useful safety net and a poor strategy: an auto-created work center has a name and nothing else, meaning one machine, no shift assignment, and no efficiency. Build the work centers properly first.

Items to Product

Only the identifier is required. Everything else can arrive later through a second, narrower refresh file, because blank cells preserve existing values on an update run. Watch identifier formatting across exports: matching ignores case but not a size or revision suffix, so make every export render the identifier the same way.

Work centers to Workcenter

QuickBooks has no work-center concept, so this file is one you write or type in. It carries capacity, which is where scheduling accuracy is won or lost.

Number_Of_Instances is the highest-value column on the page. A cell of four identical machines imported with one instance produces a plan roughly four times too long, and it fails quietly: nothing errors, the dates are simply wrong. Fill it deliberately for every multi-machine center. The arithmetic is in how EDGEBIC calculates work center capacity.

Machine pools

Planners think in pools of interchangeable machines, and EDGEBIC models that as a work center group. A routing step bound to a group re-evaluates every member on each reschedule, picks by strategy (earliest completion, primary first, or earliest start), and applies a per-member efficiency factor so a slower machine is chosen only when it still finishes first. Operations already started keep the machine they started on, because recorded work is never moved. Build the members as ordinary work centers, then create the group inside EDGEBIC and add them: see how to create work center groups.

Routings to BOR

You build this data once, either drawn in the designer or written as a spreadsheet with these columns.

Op_Flag is the field people get wrong first. True means "this row is an operation running on a work center". False means "this row is a component consumed at this point in the routing". If you carry your QuickBooks bill of materials in as component rows, they all take an operation flag of false.

Why the import takes two passes

A routing cannot be written row by row, because step 10 must point at step 20 and step 20 does not exist yet. So the import runs in two passes. Pass one validates each row, resolves or creates the items and work centers it names, and buffers the row. Pass two groups the buffer by end product, sorts by sequence number, writes the steps, and wires the chain: 10 to 20, 20 to 30, and the terminal step to the finished item. Check that visually after the first import: a routing missing its final link draws as a chain floating free of the item it builds.

All of one item's steps must travel in one file, because a run wipes and recreates that item's steps on first reference. And re-importing is safe for running orders, because every scheduled job carries a frozen snapshot of the routing it was planned with.

Orders to SalesOrder

Two mask options control the date logic when the export carries only one date: one treats the job date as the due date, the other derives the job date backward from the due date. Choose the one matching how your QuickBooks report is built, once, on the mask.

Confirmations to Actuals

Job number, work center id, and actual date are mandatory. Hours and pieces are both optional, and either can be derived from the other using the operation's rate. A Complete column marks the operation finished, and an explicit start column overrides the default of using the earliest imported date. The import always overwrites the days a file carries, so re-running a corrected extract fixes the numbers rather than doubling them.

Conversions, blanks, and zeros

Reading the outcomes

Every row resolves to Created, Updated, Reused, or Failed. Reused is the default for a record that already exists, so a weekly item file of 3,000 rows reporting Created 8, Reused 2,992 is the desired result rather than a failure. When counts look wrong, the per-run log file carries one line per row with the exact reason for every failure.

What import cannot bring, and why it matters

The settings that most differentiate a schedule have no source in QuickBooks at all: the sequence-dependent setup matrix, operator skills and certifications, work center group strategies, bottleneck anchoring, and lot streaming transfer batches. Those are configured once in EDGEBIC and then apply to every imported order afterwards. The EDGEBIC product overview maps the engine, and the ERP integration architecture explains why one mask design serves every system.

For the questions that come up before a project starts, see the QuickBooks integration FAQ. The Fishbowl and Odoo references show how the method changes when the ERP does carry routings, and the ERP scheduling add-on page frames the category.