Hi all,
I’m very new to Coda but am finding it very useful and intuitive. I do occasionally run into a roadblock when working with formulas, but usually can work out what I’m trying to accomplish.
I’ve got a scenario right now that I’m continuing to puzzle over, and thought it’s a good time to reach out to the community for some input, or a different way of thinking about my problem.
BACKGROUND:
I’ve got a spreadsheet template used for keeping track of fiber optic cable usage on projects. You can think of it as having two sections:
Cable Schedule
This table is basically an index of all the cables in use—Origin and destination, cable length, is it a 12-strand or 24-strand cable?
Strand Assignments
This is a set of sub-tables, one for each cable. It holds information for each strand of the cable—What kind of signal is carried, what hardware is it connecting to on each side, is it cross-patching to another cable?
QUESTION:
I figured Coda could do this better than the manual spreadsheet method, and I’m off to a good start with it. I’ve got one master table that holds all of the cables, and then a Canvas Column for strand assignments. I’ve got a template for that table created as a subpage of my attribute tables page, and so I can set the Value for new rows as that page. What I’d REALLY LIKE to do is to have a different strand assignments table based on whether the Cable Type for that row is a 12-strand or 24-strand cable. I’ve tried using a filtered view, or creating two separate templates and using an if
formula, but am not finding success.
FINAL THOUGHTS:
It occurs to me as I type this—the Value for new rows formula calculates when you create the new row, before you’ve even defined the cable type on that row.
I know there’s got to be a better solution for this than just generating the table for all 24 strands and deleting the half you don’t need for a TAC-12 cable, and look forward to seeing others’ thoughts on this. Meanwhile, I’ll keep tinkering!
UPDATE:
Not a whole lot of progress on automating the strand assignments sub-table generation, but I have come up with a sort of workaround. In the Template page that is duplicated into each new row’s canvas column, I’ve added a filter that can select the cable type (from a hidden column) and display the rows accordingly. This is more elegant than just deleting the rows manually (or with a button, as I also played with earlier), but I still want to see some automation happen here.
It looks like I celebrated that workaround too soon. It looks like when the template is duplicated into the canvas column on the main table, it only duplicates those rows that are visible based on the state of the filter on the template page. The filter is there, the lookup control is there, but if the control on the template page is set to “blank” you’ve got no rows at all in the duplicated table…
Cool puzzle! Are 12 strand or 24 strand the only two options that would determine the setup of the strand assignment table?
I setup a switch formula (that requires some copy and pasting of the page objects) that switches the canvas template filtered to 12 strand or 24 strand objects here.
If what I understood of what you are trying to accomplish is correct, you may find that using linked tables instead of canvas will work better.
Creating a table in canvas and replicating it for every row is not a very efficient and cannot be centrally managed. E.g. Adding new columns or changing naming/colour conventions.
I think you would be better served by having a singular table of strand assignments and use it as a column in the cable schedule table.
I have replicated your screenshots in this published doc if you want to make a copy:
I have added documentation to it to show how it works.
Please take a look and let me know what you think. If I am way off base perhaps we can work something else out.
A very interesting use case, and you recognized very well how Coda could support you
I just made a quick solution where you with buttons can generate to each order the 12 or 24 strands.
I didn’t have the time to add the colors, to make it as appealing as your sample, and this can be done accordingly.
Don’t hesitate to get back to the community in case you face a roadblock.
Probably 98 or 99% of the time, the cable used would be a 12-strand type. Less than 1% of the time it would be some other strand count—I’ve seen a 72-strand cable used, or you could even have one with less than 12 strands. I could also see a use case for this sort of worksheet for analog phone lines, which would use a different color coding scheme and different pair counts/cable types.
Thank you for this! I was able to apply the techniques you demonstrated here into my own document and it is working like a charm. FormulaMap() was key here, as was Display this column as a table in the Layout Editor. I think maybe I saw that referenced in one of the videos but had totally forgotten about that.
I realize I was stuck looking at the problem the wrong way. I was thinking of each set of Strand Assignments as belonging solely to the TAC Fiber they were a part of, and so I wanted to copy the blank strand table into each row of the master table. It is far more efficient to use one table to hold all the strand assignments, populating new rows as you add cables and then displaying the filtered view in a column of the master table.
Next trick will be to figure out a way to widen the table detail view. It won’t fit all the columns, as it seems to be locked to the middle vertical third of the window.
Each page is set to Auto width by default. This sets it to standard which is middle third and only goes wide when certain content is added that changes it. You can override this by going to the settings cog top right on the page you want to change, choose page options and set to wide.
I am on my iPad at the moment so cannot properly test to see how much that will do for a detail view.
I am glad that I could help with the tables and formula.
Please note that I made use of .Last() to open the newly created row. I have since come to learn that this can be bad practice. If you are going to be using this doc simultaneously with others they could create a row at the same time as you and lead to the wrong row opening for you or them. It would be worse if it was going to make edits using that lookup. If this is going to be an issue let me know and I will provide you with the better alternative when I get chance.
It was also recommended that templates are stored in their own table with the exact same structure and copied from there. This saves you having to filter them out when doing any kind of analysis. Should be easy enough to do. Just set it up and change where the formula is looking for them. In your use case this may not even be necessary.
This is good to note. In my particular case it isn’t likely that two people would be editing this information at the same time.
Edit to add:
I’ve updated the doc to move the template rows into their own table on the attributes page, which I’ve also set to read-only to prevent accidental changes. I wanted to use the Strand Assignments table as a master list I could group by Cable ID as an alternative view, also handy for printing a reference copy.
