Cleaning an FPGA BOM: Missing Suffixes, Duplicate Lines and Alternatives
Cleaning an FPGA bill of materials should make the data easier to check while preserving the engineering requirement. Keep the original text, document every correction and separate unresolved identities from confirmed purchasing lines.
This workflow is for an existing spreadsheet assembled from exports, emails or earlier quotations. Its output is a reviewed data set with a change log. The following rules and examples are editorial recommendations, not a manufacturer's BOM format.
Preserve a reference copy
Save the received file before editing it. Record the filename, source, receipt date and the project or assembly revision it is supposed to represent. Assign a stable identifier to every original row.
Keep at least three values for each part-number field: the raw value, a proposed cleaned value and the approved value. A proposed correction should remain distinguishable from an engineering-confirmed change.
If several files contribute rows, retain the file and row reference on each record. This lets a reviewer resolve disagreements without reconstructing the input from memory.
Record the meaning of each quantity column too. A per-board quantity, a purchase request and a supplier's offered quantity are different inputs to your calculation.
Normalize formatting without guessing identity
Use a separate comparison field to flag surrounding spaces, inconsistent separators or other formatting differences. Preserve the raw field exactly. Review the proposed transformation before using it to group rows.
Do not remove every punctuation mark, fill missing suffixes or change a letter to a digit merely because the result resembles a known device. Such edits need a source and a reviewer.
Lattice's Part Number Reference Guide organizes numbering information by family. Use the relevant family reference to investigate an unfamiliar field rather than applying one cleanup rule to all manufacturers.
Keep manufacturer name, manufacturer part number, internal item code and supplier item reference in separate columns. A supplier's shorthand can be retained as a useful reference without replacing the ordering identity.
A fictional before-and-after example
Every code and request identifier below is a placeholder. MPN_A, MPN_B and MPN_C do not identify real products. [space] makes surrounding whitespace visible; ? represents an unresolved character.
| Row | Raw part field | Quantity | Context supplied |
|---|---|---|---|
| B01 | MPN_A |
25 | Request R1 |
| B02 | [space]MPN_A[space] |
25 | Request R1 in another export |
| B03 | MPN_A |
40 | Separate request R2 |
| B04 | MPN_A? |
10 | Final character unreadable |
| B05 | MPN_B |
12 | Different approved temperature option |
| B06 | MPN_C |
Not stated | Supplier-proposed alternative |
A proposed review result would look like this:
| Rows | Proposed handling | Evidence or decision still needed |
|---|---|---|
| B01 and B02 | Flag as a possible duplicated request | Owner confirms whether both refer to the same demand |
| B03 | Keep linked to R2 | Confirm its separate requirement and schedule |
| B04 | Hold identity as unresolved | Original approved ordering code |
| B05 | Keep as a distinct part | Preserve the approved variant and its demand |
| B06 | Move to an alternative-candidate record | Engineering review and applicable quantity |
Removing whitespace from B02's comparison field helps find the possible duplicate. It does not authorize deleting the row or adding its quantity to B01.
The example deliberately leaves decisions open. A cleanup tool should surface questions that require ownership, and a reviewer should close them with a recorded reason.
Reconcile quantities at the demand level
Group matching part numbers only after checking why their rows exist. Two rows can request the same part for different builds, delivery dates or destinations. Preserve those relationships even when preparing an aggregated purchasing view.
For the fictional example, if the owner confirms B02 is a repeated export of R1, the confirmed demand from B01 and B03 would total 65 units. Keep the 25-unit and 40-unit requirements underneath that total. If B02 is a separate approved demand, it must be treated differently.
Write down which rows contribute to each total. Exclude unresolved and alternative-only rows from confirmed demand unless an authorized decision explicitly includes them.
If the file gives quantity per assembly, calculate purchase demand in a separate field using an approved build quantity. Record any additional allowance separately with its basis. Avoid hiding it inside the per-board requirement.
Keep alternatives outside the approved identity field
Use a linked alternatives table containing the original row ID, proposed part number, reason for proposal, reviewer and decision. Until approved, the proposal should not overwrite the requested item.
A supplier's note saying “equivalent” is a starting point for review. Ask engineering to determine the required checks and record its conclusion against the exact two codes.
Check which identifier a source document uses before matching it to a BOM row. Microchip uses CPN for the catalog part number, or ordering number. Microchip PCN FAQ
Preserve the notice reference with the specific row it supports. Avoid turning a family-level comment into an approved change for every similarly named item.
Give each correction an audit trail
Use a compact change log with the original row ID, old value, proposed value, reason, evidence link, reviewer and status.
Distinguish formatting correction, confirmed identity correction, duplicate resolution and engineering substitution. These actions have different meanings. Calling all of them “data cleanup” makes later review harder.
When information is missing, assign an owner and a specific question. “Confirm the final temperature suffix from the released BOM” is more useful than “check part.”
Release a reviewed purchasing view
Before exporting the cleaned file, check that every approved line retains its original-row references. Confirm quantity totals, separate delivery requirements, unresolved fields and alternative approvals.
Keep the reference copy and change log with the released version. Give the output a revision identifier so later quotations can be matched to the data actually sent.
When sending an FPGA enquiry to FPGAX, use the reviewed manufacturer codes and requested quantities. Identify provisional rows separately so their uncertainty remains visible in the quotation process.
