A customer from the metalworking industry was facing a time-consuming and error-prone process of manually transcribing bills of materials (BOM) into their database. These BOMs were generated from CAD applications used for creating technical drawings, where complete production assemblies are composed of numerous individual parts. Each component could itself be an assembly made up of additional sub-parts. Manually transferring these hierarchical structures into the database was not only inefficient but also prone to errors. The customer's goal was to significantly simplify this process by automating the data import into their FileMaker database.
Based on sample Excel exports provided from the CAD system, we developed an automated import tool fully built in FileMaker. First, we created a temporary table into which the raw data from Excel is imported in its original format. Then, a data transformation process takes place — using scripts and conditional rules, the values are sorted and parsed according to criteria specified by the customer. For example, certain values indicate that the item is a nut or a bolt.
A key challenge was to accurately capture and store the hierarchy of components and their sub-assemblies — the so-called tree structure. During the import, the system creates relationships between parts and their parent assemblies, enabling users to trace the complete composition of any product. Our solution ensures not only the correct assignment of individual components but also the structuring of entire assemblies, with the ability to edit them further and utilize them in subsequent production stages.
The entire process runs locally within FileMaker, without the need for external tools. The user simply exports the BOM from their CAD software, opens FileMaker, and imports the Excel file with a single step — everything else is handled automatically by the system.
