Solidworks Design Tables Linked to ERP Data
Elevate Design Tables with ERP Data Integration using Excel
Design tables in SolidWorks are a powerful tool for managing part and assembly data, enabling the control of dimensions, suppression of features, and setting of properties through an organized structure. However, traditional design tables often work in isolation within individual files, which can lead to inconsistencies in data that frequently undergo modifications to meet varying customer specifications.
The solution lies in linking SolidWorks design tables to a centralized Excel file containing master data, such as approved vendor data, specifications, materials, etc. This linkage ensures that users select from validated data, fostering uniformity across various products. Simplfying future modifications and enhancing compatibility for products using both standard and custom components.
In this approach, the master data Excel workbook holds the ERP data within various sheets, each sheet being dedicated to specific products. These sheets contain essential data, unique to those various products that integrates seamlessly with the SolidWorks design table, ensuring consistency and accuracy in the design process.
Right-click this image and open in a new window or tab to enlarge it.
The following image demonstrates the SolidWorks design table, showcasing how data from the master workbook populates the dropdown lists in the design table columns. Observe how the linked data connects to the custom properties within the SolidWorks file using the ($PRP@XXXXX).
Right-click this image and open in a new window or tab to enlarge it.
First step - making the data in the master data workbook available
The integration of master data into design tables relies on Excel's formulas and Defined Names for the lookups. These formulas are crafted to dynamically reflect changes in the master data—any additions or modifications automatically update and expand the associated dropdown lists. Consequently, all design tables using this linkage immediately benefit from the updated data, unlike standalone tables which don’t offer such dynamic updates. This dynamic approach of using linked data ensures uniformity and precision across all designs.
The image below depicts the Defined Names dailog and associated formulas within the master data workbook. Highlighted in yellow is one example of how a column of data is captured for passing to the design table workbook. The Refers To column in the table is where the constructed formula captures column B data of the sheet named Common Input Data. If more rows of data are added to that column the formula adjusts for this automatically.
Right-click this image and open in a new window or tab to enlarge it.
Second step - linking the design table to the master data workbook
To connect the design table to the master data workbook is a simple process of pointing a cell to the Defined Names in the master data workbook. First, create a Defined Name in the design table workbook and point it at the master table workbook. Keep the names the same to make it easy to understand and edit at a later date. Below is an image showing the menus to work through after picking a cell in the design table workbook to link the data to.The following image shows this step.
Design table Defined Name (CAN_SIZE) pointing to master data workbook Defined Name (CAN_SIZE).
Now click a cell in the design table that you want to see a drop list of values from the master data. Then using the Data Validation menu and the List option select as the Source the Defined Name just created. This step is depicted below.
Linking the design table to the master data workbook.
Closing the Loop
To make this a true closed-loop system, the data in the master data workbook should be linked to various curated sources, such as an ERP system. The rows of data in the master data workbook are not static, but is instead dynamically linked to the other business systems. Excel has multiple ways to connect to these types of systems. Below is the menu in Excel that can create these dynamic links to remote systems. Now the data lists in the master data workbook is dynamic and that dynamic data flows right through to the design tables in every Solidworks file that has them. This creates the seamless control loop: ERP and other business systems manage and verify data, which is then retrieved and updated by the Excel master data sheet through SQL queries. This data is made accessible to the SolidWorks design tables and upon release of the CAD file, can then be relayed back to the ERP by various means, ensuring a consistent and accurate flow of information that is fully controlled, end-to-end.
Tools to link master data from an ERP/MRP to Excel
Why the master data workbook?
The master data workbook is used as a “go between” for the ERP and SolidWorks world to allow SolidWorks specific information, such as Material databases values and other data to be appended to the master data sheet that is in turn shown/used in the design tables vs. directly going to the ERP tables. Additionally, there has been some quirks with design tables that have embedded SQL lookups to business systems that can cause issues with the SolidWorks files.
Final polishing!
It is important to note that Excel requires that files that have cross-workbook links need to be open in memory for the lookups to work. iNTEGRAL suggests that the read-only master data workbook be stored in the PDM vault and uses a macro that is executed at file open to update itself against the SQL databases it gets its data form. Additionally, create a shortcut to the workbook and place it in the users startup folder. Each time users log on, the master data workbook will be open and ready in background when working in a design table.