How to export Epicor Kinetic history with BAQs for a data review

What a data review needs from Epicor first#

A data review needs metadata from Epicor before it needs any records: how many quotes, orders, jobs, nonconformances and returns exist in each year, the earliest dates, and how often records link to one another. These counts answer the fit-check questions without exposing customer names, prices or notes.

Keeping the first pass to metadata also keeps it small. A handful of BAQs that return one row per year per record family can be reviewed in a single meeting, and nobody has to decide yet which customers or fields are sensitive.

Which Epicor tables, and which databases, hold the history#

The Epicor tables that hold most manufacturing history are the quote, order, job, quality, shipping, invoicing and return tables. The names below are those commonly found in Kinetic databases; confirm them in the data dictionary for your version, and look for the user-defined fields your company added over the years.

History from before Kinetic often lives outside it, in a Vantage, Vista or Epicor 9 database that was never fully converted. Many upgrades carried over open orders, master data and a limited window of closed transactions, leaving older jobs, quotes and nonconformances behind.

Treat each legacy database as its own source with its own metadata pass. Some teams build queries that union live and historical data, but that only works when the keys still match. Customer and part numbers renamed during an upgrade need a crosswalk before any combined result can be trusted.

Step by step: building the metadata BAQs#

Metadata BAQs count records rather than return them, and each should run on its own in BAQ Designer without heavy joins. Give them a shared prefix so they are easy to find, review and remove later.

• 1Step 1: create a read-only BAQ for each record family, grouped by the year of its main date, such as the order date or the job creation date.
• 2Step 2: add calculated fields for the record count and the earliest and latest dates in each year.
• 3Step 3: add link-rate BAQs, for example nonconformances with and without a job number, and jobs with and without an order link through JobProd.
• 4Step 4: count attachments per record family, without opening them, to size the customer drawing question.
• 5Step 5: list the user-defined fields in use and how often each is filled.
• 6Step 6: export the results to a spreadsheet and note the Epicor version and the date the queries ran.

Exporting full history once scope is set#

Full history exports start only after the review shows which record families deserve a closer look and the rights review has set exclusions. At that point, build one BAQ per family with its keys and the agreed fields, and export it in slices by year or quarter.

Epicor offers several routes for getting BAQ results out. On premises, IT can often use a scheduled BAQ export process to write files to a server folder. In cloud deployments, direct database access is usually limited, so BAQs called through Epicor's REST services or exported from dashboards are the common routes. Check your subscription terms and the documentation for your version, since options differ.

The Data Management Tool, known as DMT, is built mainly for importing and updating records, so most teams treat it as a migration tool rather than the export route for a review. Large BAQs can hit time or row limits, which is one more reason to slice by date.

What to filter out inside the query#

Exclusions belong in the BAQ itself, so restricted records never land in an export file. Filtering afterward means restricted content has already been copied to a laptop or a shared folder.

• 1Jobs, parts and quotes for export-controlled programs, identified by a flag, product group or customer list from your compliance lead.
• 2Attachments and links to customer-supplied drawings and specifications.
• 3Employee names; keep EmployeeNum only if it will be replaced with role codes during preparation.
• 4Bank, card and tax identifiers from customer and vendor records.
• 5Pricing and cost fields, unless the rights review keeps them in scope.
• 6Free-text comment fields until a masking pass has replaced customer names.

Document each export so it can be checked#

Every Epicor export needs a short record of how it was made, because both your own reviewers and a buyer's diligence will ask. Note the BAQ name, the filters applied, the date range, the row count and the date it ran, and keep the BAQ definition alongside the file.

Add a plain-language data dictionary for the fields kept, including what each user-defined field means and roughly when the company started filling it. Fields whose meaning changed over time, such as a reason code that was repurposed after a reorganization, belong in a known-issues note so nobody mistakes a change in practice for a change on the shop floor.

Illustrative: a conveyor builder runs its first pass#

Illustrative: a fictional builder of stainless conveyors for food plants runs Kinetic in the cloud and keeps an Epicor 9 database on an on-premises server that IT wants to retire. The CEO asks whether the history deserves a closer look before the server goes.

The IT director builds metadata BAQs for quotes, orders, jobs, nonconformances and returns, plus link-rate queries. The counts show steady volumes across the Kinetic years and a deeper run of jobs and nonconformances in the old database, with nonconformances carrying job numbers in most years. Attachments cluster on quotes, reflecting customer layout drawings.

The team takes a full backup of the old database and confirms it restores, sends the metadata summary into a fit check, and leaves quote attachments out of any future scope.

How SourceX uses an Epicor metadata pass#

SourceX uses the Epicor metadata pass as the Supply input to the SourceX five-step transaction: Supply, Rights, Preparation, Approval and Delivery. Counts and link rates show which chains, such as quote to job to nonconformance, are deep and connected enough to weigh against the SourceX Enterprise Data Value Framework.

Full exports happen only for record families the manufacturer approves after the Rights step. Exports stay on the manufacturer's systems until delivery, and large sets can ship on encrypted drives rather than passing through anyone else's storage.