‘DriveWorks Tables: They’re Not Just for Lookups Anymore’ by Paul Gimbel of Razorleaf.
Tabular data is one of the foundations of the DriveWorks design rule. Rules generally fall into one of three categories: Calculations, Logic or Lookups.
The perennial favorites VLookup() and HLookup() join forces with DWVLookup() to allow DriveWorks architects the ability to store tables of static data, like material properties, available stock parts, and so on, and pull values from data for calculations or decisions. But the humble table has grown up over the years.
Let’s take a look at some of the improvements that have allowed the humble table to emerge from its cocoon of static goodness, developing into the monarch of all things data that it is today. (Pretty dramatic for a bunch of rows and columns, no?)
Premiering at number five in our countdown, Group Tables arrived on the scene to a moderate fanfare. The group table holds information that is available to all projects in a group. This is wonderful news for common data that was previously duplicated in multiple projects or stored outside of DriveWorks in SQL or an ODBC source. Common values like material properties or customer information can be maintained in a single location. This is handy for allowing child specifications to share data without having to pass it from the parent.
But what we didn’t realize at the time was the group tables’ most useful application, the settings table. Values like fonts, colors, paths, and database connection strings can all be stored in a single location to allow you to easily consistentize (it’s a word now) your user interfaces and utilize libraries of models and other files. At Razorleaf we utilize multiple columns in our settings tables. This allows us to switch between a development environment, test environment and production environment in one project. We can build in visual clues so that we can tell the current implementation status, and it makes moving the group from our development environment to our client’s systems and back a lot easier.
New Table and List Functions
Coming in at number four, a plethora of new table and list functions have given us the ability to perform easier and more powerful queries. When I say queries, I not only mean that in its literal sense, but also in the way that the power of SQL queries can often be matched or exceeded with DriveWorks tables. From table filtering to joins to creating new tables and manipulating table structures, real power is now available in standard DriveWorks functions. Add in the DriveWorks Labs Specification Power Pack (available for an additional $0!) and you can have rules chock full of data query goodness.
Data Table Control
Up two spots in our countdown this week, to number three, is the Data Table control. This control replaces the thankfully somewhat obsolete Data Grid control. I always felt so bad for the data grid in training since it could only be introduced as “this can display the contents of a table and…well, that’s about it.” Data Table allows us to present tabular information, format it into a more useful and presentable shape, and allows the user to interact with it including selecting a row. This control could function as a result delivery device for two-dimensional data, as a combo box that allows the user to see more information about their selection, or even as an interactive reference source. Very flexible and easily formatted with multiple fonts, colors and alignments, the Data Table control continues to be a tremendously useful weapon in the form design arsenal.
Output Documents to Update a Simple or Group Table
Coming down the home stretch, we find our number two piece of functionality, a pair of very simple output document types allowing DriveWorks to update a simple or group table. The release of this feature raised such a massive groundswell of support from the DriveWorks community, that…ok, fine, maybe that’s a little overstated, but we do think that it is pretty cool. This changes the role of the table from just being a repository for static data to now having the ability to store dynamic data for an individual specification or for an entire group.
At Razorleaf we had been using this technique fairly extensively using external SQL databases for some time. And while SQL still does have the advantage of being able to provide access to outside tools, the maintenance and added layer required to make the tables specification-specific still add a few notches of complexity that make us want to avoid them. The table and SQL output documents essentially function the same way, so our implementation strategies were still 100% valid. As mentioned earlier, the use of tables for sharing information between projects and amongst parent-child specifications is made a lot easier now that DriveWorks lookup and group tables can be dynamic. Using specification tasks (which should have their own place in the countdown, but this is getting a bit long), this document type can be released at any point with a macro button, form/control event or specification flow step.
But it all comes down to our number one piece of DriveWorks functionality to cement the data table’s place in folklore and the DriveWorks Feature Hall of Fame. Number one for an amazing 42 weeks, the mystical array, also known as an inline table, turns static and dynamic tables into fully functional weapons of mass production. Most DriveWorks architects and administrators don’t realize, or at least don’t leverage, the fact that an entire table can be stored in a single constant, variable or control value. This allows for the use of all of the table and list functions to be combined and nested in powerful ways. This provides your forms the ability to include the same powerful searches you would find anywhere on the web. Tabular data can go beyond dynamic to the verge of omnipotent.
Alas, we come to end of our countdown. To recap: coming in at number five, group tables make information available to all projects, great for using standard data like material properties and for a robust settings approach to consistent projects (see if you can figure out how Razorleaf utilizes EVERY setting with a SINGLE RULE). In the number four spot, the table functions available to rules in the DriveWorks core product and the Specification Power Pack continue to expand. Jumping up to number three, the Data Table control brings tabular data out of the shadows and makes it more useful to one and all. Our runner up, the Export to Table document types move tabular data into the realm of the dynamic. And holding onto its number one position, the unsung hero/heroine of the DriveWorks rules kingdom, arrays take tabular information to new places by allowing that data to be accessible and storable through any DriveWorks value from constants to variables to form control properties.
So that’s our countdown. Thanks for tuning in. And don’t miss next month’s list of the 25 best ways to prove that you’re a DriveWorks nerd.