DriveWorks Pro: Tracking Specifications Through SQL

In DriveWorks Pro, specifications are created either through DriveWorks Live or through the Specification Explorer in Administrator, User, or Autopilot. These specifications are variations on the original project CAD, along with other documents such as quotes and bills of materials.

When creating a new specification, the users are shown the form which had been created when setting up the project, and are now allowed to enter their specific desired parameters. Once completed, the models and documents are then customized according to the values filled out by the user and the model rules defined in the project. See an example form of a project for creating a simple block, driving the length, width, height, and color.

Once the values are set and the models and documents are released, DriveWorks saves out two files to the path defined in the Specification Settings, in a hidden folder named “DriveWorksFiles”.

This folder is hidden because it is not meant to be handled by the user. The files inside this folder contain all the model rules and variables defined when the specification was created.

Note: Since the model rules and variables are defined within this folder, if there are updates to the model rules in the project at a later time, regenerating the models for this specification will still use the model rules defined when the specification was created. In order to use the new model rules on an old specification, a new specification must be created with the same variables.

You can now see why it would be helpful to track the variables used for your specifications if you need to update your model rules and reflect those new rules on old specifications.

Tracking Variables in SQL

When it comes to exporting variables in a specification to a table, many will turn to group tables. If you plan on generating a large number of specifications, then the group table can get quite large. All group tables are loaded into RAM whenever a new specification is created for any project within your group, not just the one that updates the group tables. This can slow down your specification creation process.

Why not use a tool that was created for handling large amounts of data and allowing fast retrieval of that data? SQL Server tables can be used to store the specification variables, allowing for fast retrieval later on if necessary. You can use SQL Server Express to do this, so you do not need to go buy SQL Server Standard.

Note: The integration into SQL is only available for DriveWorks Pro. This functionality is not available in DriveWorks Solo or Xpress.

• Suggested Article >> Compare DriveWorks Packages: Features in DriveWorksXpress, Solo & Pro

Creating a new SQL Database and Table

It is recommended to create a new database for this usage, to ensure it is isolated from other databases, such as a PDM vault or DriveWorks Shared Group database.

1. You can create a new SQL database in SQL Server Management Studio by first connecting to the SQL Server instance, right-clicking on Databases, and choosing New Database…
   
   
   
2. Once your new database is created, expand the database, right-click on the Tables folder, and choose New and Table.
3. Define your columns in this window according to the variables you wish to be tracking from your project.
   1. In this example, I am tracking the Length, Width, Height, Color, and Specification ID, which would be four integer columns and one string column. It is up to your discretion whether to allow null values in these columns or not.
      
      
      
      Once you have saved your new table in SQL Server Management Studio, you can set up the project to export the variable values to the new SQL table every time the specification is created.

Using DriveWorks to Populate SQL Table

1. Create an output constant to track the success of the SQL command. (In this example, I named mine “ExportComplete”)
2. Create a new macro and insert the Drive Constant Value task from the toolbox.
   
   
   
   
3. Double-click on the summation symbol below Constant Value to change the input from static to dynamic. Open the rule builder for this value. The rule in this example will look something like this:
   
   
   
   Using the “DbExecute” command allows you to build a SQL command using the values from the form. The SQL command used here inserts the form values into the SQL table I previously created called Data.
   
   Note: SQL requires string values to be surrounded by single quotes, and so you can see that I have surrounded the “ColorReturn” value in single quotes. The other variables do not require this since they are integers.
   
   DriveWorks DbExecute Documentation
   
   
4. Run the macro either in a Macro Button on the user form or in the Specification Flow.
   1. In this example, I added the Run Macro task to the Generate transition on my Specification Flow.
      
      

Conclusion

Now, when choosing Generate after filling out the form, the values from the specification are added to my Data table in SQL. I can use this table to re-run old specifications should I make changes to the model rules.

This is a fast and easy way to track all your specification variables, so that they can easily be recycled should old specifications need to be recycled with new model or document rules.

Related Articles

Working with Office Files in DriveWorks

DriveWorks Data Management: Back Up Your Data

Control DriveWorks Model Generation Order

Compare DriveWorks Packages: Features in DriveWorksXpress, Solo & Pro

DriveWorks Test Mode: Generate (On Demand) vs Generate (Queued Using Model Insight)

VIEW ALL DRIVEWORKS ARTICLES