“Calculation Tables: It’s Like Excel, Only Better” – Paul Gimbel, Razorleaf
Paul Gimbel, from DriveWorks partner, Razorleaf, is a DriveWorks enthusiast, supplier of office cookies, forum post regular, serial DriveWorks World attendee and CPD leaderboard topper.
Our partners have great knowledge of DriveWorks and Paul’s enthusiasm is clear. This means he is able to write extensive, clear, technical content about DriveWorks software and the features within it.
His most recent post on the Razorleaf blog is a brilliant example of Paul’s knowledge and experience with DriveWorks. Read the post below from the “Business Process Sherpa” to find out more about Calculation Tables in DriveWorks.
DriveWorks 12 Calculation Tables: It’s Like Excel, Only Better
A long time ago, in an automation far, far away… It is a period of engineering calculations under the flag of Microsoft Excel. Microsoft Excel has enjoyed many years of use and continues to be a weapon of choice for automators and engineers alike.
And for a while, Excel was all that we needed. Some advanced users would use VBA to write macros to further automate and to drive SOLIDWORKS models and drawings. Even DriveWorks realized the potential of Excel and up through DriveWorks 6, DriveWorks projects were created on top of Excel design masters.
But even as DriveWorks realized the limitations of Excel and the Titan engine took root, some implementations clung to Excel. The last piece of the puzzle that was needed to unseat the spreadsheet, was functionality that could calculate tables full of repetitive rules, as quickly and easily as using a spreadsheet with column fill. And thus, the calculation table was born.
What is a Calculation Table, You Say?
The Excel FILL functionality is probably the best way to think of a calculation table. Imagine that you have a worksheet with values in a few columns, and the rest of the columns filled with formulas that take the value in column C, and multiply it times the square root of the value in column D, squared plus the value in column A, minus the value in column F squared and so on and so on.
You simply fill in values in A, B, C and D then select all of the cells in that row, grab that little square dot in the lower left corner of the selection range and pull it down to instantly go from one set of calculations to 80.
Calculation tables are very similar in that you create a rule for each column that can reference other cells in the same or other columns. Then, as you add rows, the rules all propagate, taking their relative references (one cell up and five cells to the left) with them. Your columns can receive their values by using rules, for example, an index that counts by twos would just reference the cell above it and add two: =[1U] + 2.
Some of you may have picked up on the issue here. “Well what about if there is no [1U]? What does it do for the top row?”
Simple. It throws an error, just as you would expect.
You can solve this one of two ways:
- Along with column rules, you can take any cell in the table and override the column rule by putting in a cell rule or value for that cell only.
- The elegant solution is to utilize a new DriveWorks 12 function to provide a value if an error occurs: =IfError([1U]+2, 2). This means that if [1U]+2 returns an error, the function will return the value 2. Your rules can reference cells using relative references (ex. [4U,2L]), absolute references (ex.“DWCalcMyCalcTable.ColumnName1”) or you can even combine the two (ex. Indirect(“DWCalcMyCalcTable.ColumnName” & [1U]) ). And to make them even more useful, the values in the table can correspond to form controls.
The Three Types of Calculation Table Columns
There are three types of calculation table columns:
- Data Columns – where you use your rules to calculate values.
- Control Input Columns – have each cell take its value from a specifically named control.
- Control Output Columns – push their value into a specifically named control.
These controls take the name of the column and the row number (ex. …Diameter5, Diameter6, Diameter7…). Output controls will push their value to certain types of controls only, including text boxes, numeric text boxes, labels, spin buttons and sliders. List and combo box controls will not accept the outputs from calculation tables, automatically. Even outside of the calculation table, absolute references like DWCalcMyCalcTable. Diameter3 are perfectly valid to use in rules.
One use for calculation tables that we have found to be particularly helpful is the creation of dynamic user interfaces.
With macro and option buttons able to utilize images like color swatches, product variations or even dynamic text with Text Over Button, we can create a field of buttons that will reconfigure themselves. Users can make selections and apply filters such as form container width, resizing and reshuffling to make more or fewer columns and/or rows. The calculation table tracks the height, width, left and top dimensions of the control along with the images to display (unselected, selected, and hover), tooltips, error codes and more with each row corresponding to a control. To expand the number of controls, you simply add a row, copy your control, and do a little renaming and updating.
With the introduction of Calculation Tables in DriveWorks 12, the excuses for why people still use Excel have run dry and so has DriveWorks’ support for Excel-based projects.
So whether you’re managing a good number of inputs or performing a slew of identical calculations, before you start creating a bunch of indexed variables, check out Calculation Tables.
It might be the most scalable thing you do all day.
