Working with Unknown Quantities in DriveWorks
The wonderful thing about design automation, is that we can work with so many situations where we handle a large number of unknowns. The frustrating thing about design automation is that we can work with so many situations where we handle a large number of unknowns. The toughest of these cases come when we have to deal with unknown quantities of things that need calculating. Let’s take a look at how we can work with loads of quantities, dimensions, inputs and outputs without doing an infinite amount of implementation work. Note that this article is meant to let you know about the techniques that are available. I won’t dive into the details of each because everyone tells me that I’m too long winded already. I would explain that I’m not, but that would require that I…um…sorry. Let’s get back to the subject at hand.
There are a lot of situations that call for an unknown or (almost) infinite number of items. The classic example, and the most common use of our first technique, is building an order or quotation. This was almost definitely the impetus for the development of the child specification all those many releases ago. The Child Specification control allows the user to click ADD as many times as they want, to create as many specifications (like a product) as they want or need, and to keep them as a part of a larger entity, like a quotation or an order or a larger assembly. Child specifications can be created from any project or projects that you create and information can be passed bidirectionally between the parent and the child. Children can have children that can have children, and so on. Tools like the Rollup Table can be employed to aggregate the information from all levels. For a great example of this, seek out the classic Shelving System DriveWorks sample project.
A lot of data can also be stored in tables, both of the project and group varieties. Project tables are project-specific, hence the name, so are good for storing information that only a single project would need, like options that are only available for one type of product and their related information. And while tables are primarily used for storing data for lookup, there are methods available to store information from a specification in each table. Project lookup tables (not aptly named for this purpose) can be used to store information for a specification since that table is stored in the design master, a DriveWorks file saved when a specification is created. Output documents, specification tasks, and a variety of table functions and Specification Power Pack functions are available to store and manage information in simple tables. Tables can be used as scratch pads to store information or can be used for longer term applications to store information collected from a specification (lookup tables) or across specifications (group tables).
The tricky bits about tables are that they are stored in such a way that they need to be copied or refreshed every time a specification is opened. As your data grows, this can cause performance lags when a new specification is created or an existing specification is copied or edited. Additionally, table functions are not always the most efficient for searching, parsing, reformatting and aggregating data. This also, can result in performance lags.
An alternative to storing information in tables is to store the information in Microsoft SQL Server databases. Can you use other databases? Sure, but DriveWorks does have some functionality designed to work natively with SQL Server, and you need SQL Server to run DriveWorks shared groups anyway. Two primary functions, DBExecute() and DBQuery(), allow you to build SQL interaction directly into your rules, but the majority of SQL writing is done through output documents, and the majority of reading comes from the use of QueryData() to bring in values from a single column, or QueryDataValues() to bring in a table of information.
Performance can also become an issue with SQL, as calling SQL and transferring information between DriveWorks and SQL are subject to network traffic and infrastructure. SQL is very powerful and efficient at managing large and complex data, so try to leverage the power of SQL by having the database do all of the filtering, aggregating and organization of the data to only bring back the data that you need, already formatted the way that you need it. With a little bit of SQL knowledge (or a web browser that can find www.w3schools.com/sql), you can leverage SQL functionality like JOINs, GROUP BY, stored procedures, scalar functions, and string functions to have SQL do all of the heavy lifting for you.
You have to be careful with SQL, though, because a single command is applied immediately, there is no undo, and you can do a lot of damage with a single query. (Yes, I know about transactions, so don’t bother writing emails or comments to explain them to me. I think they’re too much for the casual SQL user to deal with.) So, you need to be very careful to be sure that your SQL is correct and clean before you run it. This can even mean that you need to test values that the users input before using them (look up the classic XKCD comic “Exploits of a Mom” about Little Bobby Tables and SQL injections).
The last two techniques that I’d like to bring up use a single variable or value to hold a series of values. Lists in DriveWorks store a series of values as a pipe (|) delimited string. A large number of list functions and specification tasks allow you to work with an unknown, and potentially unlimited, number of values in a single list. So rather than utilizing a large number of variables (DWVariableQty1, DWVariableQty2…) you can store all of the values in a single variable (DWVariableQuantites). Lists can be passed directly to Combo and List Box controls for selections and can be written to tables as well. Similarly, you can store tabular data in a single value (variable, constant, control value, list data, etc.). Known as arrays or inline tables, this data is stored as a formatted string that you can view, which makes them somewhat manageable to work with. As with lists, a wide variety of functions and tasks are available to update, search, slice and dice, filter, reorganize and just about anything else that you may want to do with a table of data.
I’ve been known to tell people that a brute force method is not always a bad thing, just suck it up, do it once, and move on. But when we get to the point where we don’t have a reasonable way to estimate how much data we need to manage, DriveWorks does give us a host of methods to work with unknown data quantities. Child specifications allow us to store an unknown number of specifications inside or linked to a parent specification. Project lookup and group tables allow us to store data that can be specification-specific or made available across specifications and projects. SQL allows us to store tabular and more complicated relational data with a powerful data manipulation engine to manage the data for us. And working inside of our rules, we can handle lists and tables of data as a single value. So, armed with these new tools, you have the power to create more generic and flexible environments. Share with us how you leverage these tools in the comments. It’s ok. You can brag a bit about how cool you are.