Sage to EDGEBIC: The Data Mapping Reference

Mapping Sage data into EDGEBIC is a column-mapping exercise rather than a development project: you export items, work centers, routings, open jobs, and optionally labor hours to Excel or CSV, then drag each source column heading onto the matching EDGEBIC field once and save it as a reusable import mask. This reference lists what maps to what, which columns are mandatory, how unit conversion and blank cells behave, and what each row outcome means when a run finishes.

EDGEBIC by User Solutions integrates with Sage through those masks rather than a per-edition connector, which is why the same method works whether your export came from Sage 100, Sage 300, Sage X3, or a report someone maintains alongside a Sage financial platform. If you have not read the end-to-end version, start with the complete Sage 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.

Two entity types have no Sage origin and come 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 scheduled date is computed against. A shift row with a start time and no matching end time fails, so fill both cells of a pair or blank both to make it a day off.

Auto-create is on by default, so a routing file naming a work center that has not been imported yet creates it on the fly. Useful safety net, poor strategy: an auto-created work center carries a name and nothing else, which means one machine, no shift assignment, and no efficiency.

Items to Product

Only the item number is required, and a blank one fails that row with a recorded reason while the run continues. Everything else can arrive later through a narrower refresh file, because blank cells preserve existing values on an update run.

Work centers to Workcenter

This is the file where scheduling accuracy is won or lost, and in a Sage shop it is usually the file you curate by hand rather than export.

Most Sage exports describe a work center as a capacity figure rather than a count of machines, so Number_Of_Instances is the column you fill in yourself. It is worth the hour: a cell of three identical machines imported as one instance produces a plan roughly three times too long, and the failure is silent because nothing errors. With 10 to 30 work centers in a typical Sage shop, a hand-built work center file is small, stable, and re-imported unchanged for months. The arithmetic behind the column is in how EDGEBIC calculates work center capacity.

Machine pools have no export column

Where machines are genuinely interchangeable, EDGEBIC models a work center group: a named pool re-evaluated on every reschedule. A routing step bound to the group picks a member by strategy (earliest completion, primary first, or earliest start) and applies a per-member efficiency factor, so an older slower machine is chosen only when it still finishes first. Operations already started keep their machine, because recorded work is never moved.

None of that fits in an export column. Import the members as ordinary work centers, then build the group inside EDGEBIC. It is a one-time configuration per pool and one of the highest-value settings in a small shop, because it turns a manual weekly assignment into something the engine redoes automatically. The setup path is how to create work center groups.

Routings to BOR

Op_Flag is the field people get wrong on a first load. True means the row is an operation running on a work center; false means the row is a component consumed at that point in the routing. Reversing them produces a routing full of work centers that are really parts, and the graphical designer shows it immediately.

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, then wires the chain: 10 to 20, 20 to 30, and the terminal step to the finished item so the routing renders as one connected flow.

Two rules follow from the same mechanism. All of one item's steps must travel in one file, because a run wipes and recreates that item's steps on first reference, so splitting one item across two runs means the second wipe deletes the first run's steps. And re-importing is safe for jobs already running, because every scheduled job carries a frozen snapshot of the routing it was planned with.

Outside processing maps cleanly as an operation row on a work center that represents the vendor, with the turnaround as transit days, which keeps the outside time visible on the Gantt rather than buried inside one long operation.

Work orders to SalesOrder

Two mask options cover the case where 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 your export once, on the mask.

Labor to Actuals

Job number, work center id, and actual date are mandatory. Hours and pieces are 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 timesheet file fixes numbers rather than doubling them. Days the file does not mention are preserved unless you explicitly ask for them to be cleared. Actuals import last because each row has to find an operation on a job that is already scheduled.

Conversions, blanks, and zeros

The practical consequence of the blank rule is that a partial refresh file is safe: a two-column file of item number and price updates prices and touches nothing else. The practical consequence of the zero rule is the opposite: leave a column out of the export rather than filling it with zeros you do not mean.

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 900 rows reporting Created 6, Reused 894 is the correct result rather than a problem. When the counts look wrong, the per-run log file carries one line per row with the exact reason for each failure.

What import cannot bring, and why it matters

The settings that most differentiate a schedule have no column in any ERP export: the sequence-dependent setup matrix, operator skills and rosters, work center group strategies, bottleneck anchoring, and lot streaming transfer batches. Those are configured once inside EDGEBIC and apply to every imported job afterwards. The EDGEBIC product overview maps the engine, and the ERP integration architecture explains why one mask design serves every ERP.

For the questions that come up before a project starts, see the Sage integration FAQ. The SAP and NetSuite references show how little the method changes between platforms, and the ERP scheduling add-on page frames the category.