The “Not So” Easy Button
How do you sequentially number +5,000 cables in an Autodesk® Revit® project? It’s not that easy, as it turns out. Obviously, you could do it manually, but that is a lot of manual data entry, a lot of time, and a lot of room for error. Let me qualify that same question: how do you easily sequentially number +5,000 cables in a Revit project?
Let’s begin by first defining the task. The project was a ground-up, mid-size, three-level healthcare building. Telecom design was within our scope of work. For design purposes we were calculating cable counts per termination room (Figure 1). Typical industry standard is to use symbols on plan indicating cable requirements. The power of Revit schedules are then very helpful in calculating and showing these totals in the model.
Figure 1
The extra step was to not only calculate cable totals per termination room, per level, and a grand total for the whole building, but also to provide these cable counts numbered sequentially. In a world of machine learning and artificial intelligence that can defeat humans in games like Jeopardy, Chess, and Go, this problem seems so trivial comparatively. But time and budget constraints such as they are, a seemingly mundane task like this can prove to be a major obstacle.
Here is another twist. Let’s also change some of the symbols. Not such a big deal, normally, except we had already issued construction documents (CD). A change after construction documents are issued usually requires the change(s) to be clouded and reissued, not to mention the potential confusion this would cause to the project documents. The change mostly consisted of switching the symbols for a single data and a single phone, or making the symbol of a filled triangle empty and an empty triangle filled (Figure 2). This was the case for nearly 300 devices.
Figure 2
So now our task can be defined in two parts: 1) Sequentially number +5,000 cables; and 2) Change symbols without affecting the construction document set. What could go wrong? The number of cables a device requires, which represents the number of cables being served by a termination room, isn’t technically tracked by Revit. I say technically because there isn’t functionality built into Revit to assist in such a calculation, like load calculations for electrical power or air flow calculations for ductwork. Typically, cable requirements per device are indicated by the symbol and/or a number subscript. This number subscript can be entered into a parameter, which is then easily tracked and totaled in a schedule in Revit.
However, sequentially numbering multiple parameters is much more difficult. In order to sequentially number all proposed cables, there are two basic options. First, an element must exist in the model for every cable to be counted (one cable per family instance). The second option is to add multiple “cable numbering” parameters to the devices.
Going with option one would require duplicate devices in the same locations, causing potential visibility management issues. It would also mean that each cable number and the number subscript typically used to indicate data requirements were independent. This would exponentially increase the number of devices in the model just for cable numbering purposes while not actually being used to indicate cable counts per device on plan. We did not choose this method, hopefully for obvious reasons.
The second option is to add multiple individual parameters to each data device element. The shortcoming here is that these cable numbering parameters are not easily incremented (not that incrementing a single parameter on the “one device = one cable” method would be much better). By “not easily incremented” I mean it is a completely manual and tedious process. Not having much of a choice, this was the option we chose for numbering the cables.
Returning to the issue of changing symbols, it was decided to detach the model, creating a separate model for this sole purpose—mostly because again, at the time, no better alternative could be conceived. However, the problem now would be having to maintain identical information in two models.
A somewhat natural reaction to this dilemma might be to copy and paste. We can copy and paste in all kinds of programs. We can also copy and paste in Revit. In theory, one could edit the families to change the symbols in the newly created model. This way, when information needs to be updated to match the CD set, it could be copy and pasted. However, it isn’t quite that simple. In this case we are doing more than just copying and pasting symbols or text. Past experience casts doubt on this option, considering most of our families were face-based, it was a multi-level project, and all the data devices were circuited as data systems. For these reasons copy and paste was not a viable option.
Once the models were separated, the required changes to the symbols in the data device families were easily made. Now the process of sequentially numbering all the cables could begin. The number of cables per device varied from one all the way up to eight (Figure 3). For this reason, eight “CableID” parameters were created as project parameters and applied to the data device category. Not really knowing what to expect, we simply began manually numbering these parameters in a logical sequence per room or area from left to right throughout the building.
Figure 3
There are several numbering add-ins for Revit, some free and some paid, to assist in numbering elements. All add-ins that I am aware of handle renumbering of a single parameter by various methods like selection, direction of a line, mark value, etc. Remember, for the task at hand, we had to sequentially number multiple individual parameters for a single element. I am not sure that an add-in meeting these specific criteria even exists.
For simple device numbering (a single parameter), I am happy to recommend an add-in called Element Renumber by JO Tools (Juan Osborne). It is a great add-in, not restricted to architectural or structural elements, that allows for human selection as it increments. Freely available in the Autodesk App Store, we have used it extensively to number devices. We also began with it on this project to try assisting in numbering cable counts. Configure as desired and begin selecting elements to have it increment the parameter of your choosing.
Figure 4
The problem with this approach is that for many of the data devices multiple parameters needed to be incremented. This meant manually tracking the cable count per device and then changing the parameter to be incremented (CableID1, CableID2, etc.). See Figure 4. This was barely better than completely manually entering in the cable number and still insufficient for the amount of work that needed to be completed.
It quickly became evident that we needed to find a better solution. The next step of progress was the thought that if the order of numbering got completed using the Element Renumbering add-in, then maybe the actual cable numbering parameters could be exported out of the model, manipulated in Excel, and then reimported. The Element Renumbering add-in could then at least help us do the heavy lifting, getting the numbering sequence input (Figure 5).
Figure 5
The general workflow was to create a working view where changes could be made that would allow for work, but not affect views on sheets. Then a filter was created for elements not requiring a cable number. These would be turned off (Figure 6).
Figure 6
Then, starting with an empty “room order” parameter, we created another filter to change the color of the elements so that as the parameter was incremented the element color would change back to default, acting as a visual aid to differentiate between elements that have and have not been numbered.
Figure 7
Figure 8
Figure 9
Once the order for the cable numbering was established and completed, the cable numbering could begin. Until a better solution was found, the process was to export the information to Excel and sort by the “room order parameter.” By simply using drag to autofill we would fill in the cable number parameters, manually referring to the device’s cable quantity value/parameter to determine how much to increment across and down. This was tedious to say the least, taking one person more than a day to complete just one portion of the building.
While in Excel, to check our work the device cable quantity column could be totaled and compared to the last cable number value. A feeling of despair quickly set in. With totals sometimes reaching 1,000, if these two values didn’t match, finding the discrepancy was like looking for a needle in a haystack. Even more, if the error was found, someone would have to manually renumber all subsequent cable parameters, a very demoralizing prospect.
Desperate for a better solution, we tried various means of manipulating the data in Excel to find any way possible to assist in numbering all the cables. We tried transposing the columns to rows and even Pivot tables, looking for any way to improve the process. Not an Excel formula or macro expert myself, I was not able to find a solution, but thought someone might have a solution. Unexpectedly and even better than I could have imagined, a formula was quickly provided by a user on the Excel forum: Auto-increment-across-rows-and-columns.
The formula is quite literally like magic for this process! Reading the value in the cable quantity column, the formula increments the CableID parameters as appropriate across columns and down the rows. Simply copy (autofill) all the way across and then down to the last element (Figure 10). Then import into Revit to make the changes to the elements.
Figure 10
Figure 11
A note on the formula: the number format in Excel needs to be set to General if the Revit parameters in use are using “Text” as the “Type of Parameter.” If numbers or integers are used, a different Excel formula is needed, also provided at the link above.
Once the information was back in Revit, the devices could be tagged to display the information, ultimately completing the original task. If a device is removed or a device’s cable quantity is changed, then export, make the changes, and re-import. If a device is added, use the same “room order” information from the previous device in the sequence. Add an arbitrary character to the room order parameter and export. Once in Excel, sort by the room parameter again. This whole column can then be quickly renumbered by using autofill down. The process to number +5,000 cables could now be counted in days, not weeks.
Figure 12
Over the course of a year, changes were made to the original CD set. For about six months the two models were manually kept coordinated. Changes to the CD drove the same changes to be made in the cable numbering set. Predictably but unavoidably, as more time passed and multiple people touched the project, the information between the two models became incongruent. Then the time came to update the cable numbers.
Attempting to coordinate the information between the two models proved more difficult than expected. Cable quantity totals were not matching. Manual comparison was laborious and inefficient. Looking back, I wish I had known then what I know now.
The breakthrough came with discovering that families can have user-created image parameters in addition to the instance and type image parameters natively provided with Revit. Because we use images in schedules for our legends (see the AUGIWORLD September 2018 issue), this would allow the same family to be used to display multiple symbols and/or images.
Armed with this information I could now turn to devising a method to control the symbol(s) of the families in question, more specifically their nested generic annotations. These were families where we were making use of the <Family Types…> type of parameter to set and control the generic annotation to be used by the family type. To allow for the generic annotation to be driven by a parameter, I had to create two additional <Family Types…> parameters. Then using two additional yes/no parameters as controls, as well as formulas, the symbol could more or less be changed on the fly in the project by the user. Remember that in order for a family parameter to be available in a schedule it must be a shared parameter (Figure 13).
Figure 13
Once completed and loaded into the project, symbols for the family types in question could now be toggled. To help set this yes/no parameter on multiple families at once, I created a schedule in the project. The critical parameter is the yes/no parameter needed to change it to the symbol for the cable number set (Figure 14).
Figure 14
Under the filter tab I set it to only schedule those families that had this parameter (Figure 15).
Figure 15
Then under Sorting/Grouping, I set it to sort by that same yes/no parameter. Another critical step is to deselect “Itemize every instance.” This collapses the schedule based on the sorting/grouping criteria. In this way one change can be made to all the families meeting the filter and sorting/grouping rules of this schedule (Figure 16).
Figure 16
The end result is a schedule where a checkbox can be toggled to change the symbols. Congratulations! You have just created an “easy button”!
The success of these two combined approaches is twofold. First, all the information can remain in one model. Coordinating information changes in two different models is no longer needed. Second, the symbols can be changed quite easily as needed. Nearly the reverse of how we started, the symbols are now the flexible component, allowing the information to be the priority.
Truthfully, an “easy button” does not exist in any software. Functionality that performs a task “at the touch of a button” took immense time, planning, and resources. In this case, it took a long and arduous process just to reach these two solutions. How do you sequentially number +5,000 cables in a Revit project? The same way most challenges are solved—over time, through determination, and by learning as you go.
Nathan Mulder has more than 10 years of experience in the AEC industry. He is currently the BIM and CAD Manager for Guidepost Solutions, a global leader in investigations, compliance, and security consulting, offering design services for security, telecom, and technology systems. A Revit MEP Electrical Certified Professional, Nathan is always looking for ways to fully leverage software to improve the project design and management process. Contact him at mulder.nathan@gmail.com or on LinkedIn.
