As a Digital Coach, I leverage Basecamp in my business and coach others to do so as well. It helps managers better manage and everyone executes better because they know what is expected by when in a simple, easy to understand fashion.
One of the underused elements of Basecamp is it’s ability to render tables for columnar data. I used this when detailing out field, page layout, and formula changes prior to implementing them in Salesforce so they can all be seen at a glance and updated in a Writeboard before ever hitting the Salesforce.com admin portal.
Basecamp renders visual tables very well with the help of Textile.
However, there are many other reasons tables are generally useful. One example is sorting by a specific column. You cannot do this in Basecamp but you can in OpenOffice Calc or Excel. However, if it was easy to go back and forth between them then using the best of each when needed would be feasible. This is what I will be illustrating.
I prefer the former so this tip will be using OpenOffice.
I want to create a table in Basecamp but rather than create it as text and insert the pipe (“|”) symbols, etc. as I go, I start with a new Calc doc and drop in my column headings and fill in the rows.
OpenOffice Calc spreadsheet
When I am ready, I simply copy the text I have entered into the spreadsheet and paste it into JEdit, a free java based text editor. The great part about JEdit is you can create custom Macros that do common manipulations to text. In this case, I am replacing the tabs automatically created from my copy/paste operation with pipes (“|”) and new lines with pipe and new line combinations. Here is the macro so you can quickly create your own. My Mac stores them in /Users/joncline/.jedit/macros/, your computer may have a different location.
textArea.goToPrevLine(false);
SearchAndReplace.setSearchString("\\t");
SearchAndReplace.setReplaceString(" | ");
SearchAndReplace.setBeanShellReplace(false);
SearchAndReplace.setIgnoreCase(true);
SearchAndReplace.setRegexp(true);
SearchAndReplace.setSearchFileSet(new CurrentBufferSet());
SearchAndReplace.replaceAll(view);
SearchAndReplace.setSearchString("\\n");
SearchAndReplace.setReplaceString(" |\\n| ");
SearchAndReplace.setBeanShellReplace(false);
SearchAndReplace.setIgnoreCase(true);
SearchAndReplace.setRegexp(true);
SearchAndReplace.setSearchFileSet(new CurrentBufferSet());
SearchAndReplace.replaceAll(view);
So, I paste this now tab separated value (TSV) text block into Jedit:
Field Type Values (CSV) Note Landing
MonthlyUnits number(8) - What is their average monthly unit volume per month? Account
MonthlyUnitSales number(8) - What is their average monthly sales? Account
SeasonalBusiness boolean yes, no From Lead Questionnaire Form Account
Run the macro on it to turn it into this:
(you may have to add the leading or trailing pipe symbol depending whether you had line breaks before or after your tab)
| Field | Type | Values (CSV) | Note | Landing |
| MonthlyUnits | number(8) | - | What is their average monthly unit volume per month? | Account |
| MonthlyUnitSales | number(8) | - | What is their average monthly sales? | Account |
| SeasonalBusiness | boolean | yes, no | From Lead Questionnaire Form | Account |
I can now paste this code directly into any Basecamp Message, Writeboard, ToDo/Milestone Chat, etc. to get a table.
BONUS: What if I want to edit my Basecamp table again or add more fields? Must I do it in basecamp now?
This is a great question and since Basecamp is collaborative, I would expect this. The method is actually quite simple if you are using a Writeboard, which you should be since you are collaborating on the source data, not just commenting on it (read: Message). Instead of opening the spreadsheet and typing in your columns and rows, you simply go to your Writeboard, click the Export button at the top and view the HTML version.
Now, simply copy the columns and rows you want from the HTML file and paste them into your spreadsheet. You may get a confirmation menu to confirm the columns and rows were selected accurately but you quickly are back into the powerful spreadsheet interface for sorting, additional columns, reordering, etc.
Once finished, simply follow the directions above and move the data back into Basecamp from TSV text again.
Keep working together, and that doesn’t mean using “Track Changes” in your favorite Office app!
Your Digital Coach,
P.S.
If you don’t want to do any of this and your data is not confidential, you might consider just embedding a public Google Spreadsheet into your Message, Writeboard, etc. and you can just modify it as needed.
Post a Comment