Renaming columns affects reports

Andrew Stewart

Why is it that changing the name of a column in a sheet breaks all the reports that include that column?

I need to go into each and remove the column with the now defunct name and then add the column back into the report again. This is tedious and frustrating (what if I miss a report?).

Fortunately conditional formatting rules and alerts recognise the name change as just that, so do not need to be redefined, otherwise it would be to risky to change a column name (we have LOTS of them)!

The API shows each report column has a virtual column id and a column id that points back to the column in the base sheet, this should not change, so why does the report stop working?

L_123

This is a good question. The formulas in cells referring to the column that change adapt to the new column name, so it is definitely possible

Richard Rymill SBP

Andrew 

IHMO if you had done this the right way round to start with, you would not have created all this extra work. 

We always recommend that the first job in designing a workflow is to identify the data needed, Column data types and come to a corporate decision on column names, so everyone uses the same words to describe action in those columns. 

From that base you can then design your sheets knowing they will not change and the lessons you have learned have helped you to use Smartsheet at what it is good at. IMHO you are overthinking the challenge when this basic set up rule is applied you save time, plus everyone knows what you mean.  

Can also be fun trying to get everyone to agree the names of what you do at the outset, so yo have corporate data structure and uniformity across your empire. 

Simple methodology but ignore it at your cost! 

 

Hope that helps

RichardR

 

Mike Wilday

Good points Richard. A stitch in time saves nine.  But what do you about people who make decisions then determine to change those decisions halfway in. We can't do much with pathological mind changers. In those instances, we deal with it. 

Richard Rymill SBP

Mike

We know where you are coming from with this Mike and have relevant experience of people who should know better making unapproved changes.

What works best is a Smartsheet that acts as a change request sheet which would be reviewed at agreed periods and actioned following impact and benefits consideration. 

Most businesses see the benefit of a disciplined approach when the consequences of NOT are explained in advance.  

As consultants I believe we have a responsibility to recommend best practice while explaining why our methodology is really important to them. 

None of us are perfect but it doesn't stop us doing the best we can to help our colleagues.  

Richardr

L_123

I have found my project parameters change by higher ups on numerous occasions. And have implemented continuous improvement practices on old sheets that have required me to add, remove, and rename columns. Changing column names at a later date is not outside the norm even for an experienced user, even if it should be avoided as much as possible.

 

I don't think it would be a difficult solution for smartsheet to implement theoretically, though i'm somewhat confident there is some glitch there that is preventing them from doing it. I don't see any advantages to the way it is set up right now, whereas I see many advantages to the report names changing as the user changes them in the sheet.

Andrew Stewart

Richard,

I know where you are coming from. For many years I worked for a large multinational that recognised the need for governance of metadata and consistency of definition of terms, there was a dedicated SAP instance just for this purpose. It did make progress quite slow, though, for more than five years we in the Asia Pacific waited for global to role out a framework for collecting approvals. Waterfall projects would invest significant effort in the design phase, because changing things later was very costly. Changes requested by users would be logged and addressed in future releases, sometimes (I am not kidding) six months to a year later.

I now work for a much smaller company, that is committed to lean and agile principles. Continuous improvement is a mantra. Every employee is empowered (even required) to make suggestions to remove inefficiencies in processes they see. If someone misinterprets a column heading, we do not resolve the issue by providing a glossary or a training course, poka-yoke dictates that change the column name to something that cannot be misinterpreted. It causes me a little pain with the interface to Power BI, which extracts data based on column name.

Ultimately, which approach is better? Opinions will differ, and that is fine, but I find the agile approach a lot more satisfying, I spend my time delivering real value, and I get to see the impact very quickly. I love my work even more now.

In this company every time we do something, we ask ourselves the question: would the customer want to pay for that? If not, then try to avoid having to do it. Back to Smartsheet, does my customer want a new name for the column in the sheet? Yes! Does he want to pay for me to remove and re-add the column in all the reports that reference the column? Not so much. So it would be beneficial for SmartSheet to fix this.

A nice to have would be the ability to override the column heading on a specific report with different text. Views in databases allow this, why not Smartsheet?  

 

Chris McKay

I agree Luke. The above rhetoric is pie in the sky stuff. We all know that things change and often policies/processes are not worth the paper they are written on, especially when you're dealing with C-suite execs. If other Smartsheet components update when column names are changed, the reports shouldn't be a stretch to extend it to Reports.

J. Craig Williams

I don't think the Reports SHOULD change when one of the possible dozens or more of the underlying Sheets is changed. Reports are a consolidation tool. Yes, it is true that many of them may only look at one sheet or perhaps the same type of sheet across several workspaces. However, there is no real "link" between a Report and the Sheets. 

For example, if I have 5 project sheets, maintained by 5 different PM's with one Report that pulls in data from them and then one PM decides she knows best and changes a column name, then either:

1. The other sheets need to be updated to reflect that change so the Report is correct

or

2. There will be inaccuracy in the Report because it does not know the various pieces are linked.

The API functionality in the area of Reports is extremely limited (to the extent that is it nearly useless for this sort of request)

When a C-level tells me that "I want this changed", I tell them how much it will cost before I get to far down the road. 

Craig

Andrew Stewart

Hi Craig,

I was only considering the one sheet, many reports scenario.

In the many sheets to one report scenario, there will be an issue, regardless of the approach.

I would prefer an automatic rename in all reports that include the column and reference only one sheet, and a warning message if the column is included in any report that references more than one sheet.

At the very least, it would be nice to at least have a way to easily identify all the reports that reference a column, to assist in impact analysis.

I did write a long response to Richard earlier that must not have posted correctly, I must have pressed "Upload" instead of "Post".   Probably a good thing really :-)

To summarise, I used to work in a waterfall environment where frequently the response from IT was "it is too expensive to do". Now I work in a lean and agile one. Following the concept of poka yoke, if someone misinterprets a column name, rather than updating training manuals and creating glossaries, I remove the ambiguity from the name itself.

It would not work everywhere, but personally I get a lot more satisfaction out of adding lots of little bits of value for my "customers" rather than explaining to them why the system can't do it. And being incredibly lazy, I want it to be effortless.

J. Craig Williams

Unfortunately, since a functionality is not just one thing (in this case Reports as one view of one sheet), the design changes must incorporate multiple use case or things will get broken. 

Of course I would like an easier way to update (and create) Reports. On a recent project, despite my best efforts, the customer ignored my attempts to nail down the columns (names, types, usage) before plowing on to the rest of the project. It took days (plural) to update everything when they finally got heard me. 

side question: how does changing column names to remove ambiguity NOT result in changes to documentation and training materials?

There are so many things 'wrong' with Reports that I believe a redesign is coming. I just hope they don't screw it up like they did the UI refresh.

Craig

 

Chris McKay

There are definitely ways to address it in a more user friendly manner. I agree with Craig that a redesign is probably in the works. But when and what will it look like? I'd love the opportunity to have some input.

To deal with the "many sheets, one report" use case, the addition of a pop-up to advise that a column name in a sheet has been changed including options to re-map right there in the dialogue.

Discussions with C-suite where "the cost" is discussed are fine when they happen, but more often than not in larger organisations, Smartsheet is not seen as a version controlled information repository in the same wasy as an ERP, CRM of Finance system. Therefore, ad-hoc changes will often happen without your knowledge. In this case, it's much better to preempt reports being run with incorrect data (especially when data is aggregated across sheets).

Just my 2 cents worth.

Andrew Stewart

Craig, to answer you side question, an effective software design should be close to self-documenting. Even I don't RTFM except as a last resort. 

 

Chris, I'm with you 100%, we are up to 4 cents, can we make it to a dollar?

J. Craig Williams

@Chris - most orgs I have worked with have too many Admin's, which leads to inconsistencies. The counter argument is funneling everything through a small set of people to make changes may lead a bottleneck.

I also believe that if C-level people don't understand that infrastructure, regardless of type or functionality needs to be maintained and controlled. A system that is used by more than one individual or group needs to have some sort of agreement/guidelines, explicit is better than implicit, for how changes will occur. And when someone violates that policy, they should be trained, then chastised, then corrected as necessary.

@Andrew "close to self-documenting" implies "not self-documenting" therefore some level of changes will be required.

I think we are all in agreement that Reports in general, and columns in Reports specifically cost too much to maintain or update (too much = more than it should/could). Where I think we diverge is expectations placed on developers and end-users and organizations.

/begin digression 

Periodically, I have tried to look as solving this via the API, but I fear that I get inaccurate results when a Report could pull from 10 sheets but is only pulling from 1 or 2 due to criteria. 

I have used Reports to perform maintenance tasks such as checking if an old column (for example "statis" misspelled or "status" has been changed from Text/Number to Dropdown) has been corrected everywhere that it needs to be.

/end digression

What I want is a tool that will help me fix things, not some automated tool that will break things as easily as it will fix them.

Craig

Chris McKay

Hi Craig,

I agree with what you've said. In theory it works 100% of the time, but in practice.... not so much

/consultant hat on/

There are only business drivers, business projects and business outcomes. The C-level view is almost always that "IT is a tool and its existence is determined purely by the business"

More often than not, protocol is thrown out the window when an outcome is determined (weird to determine an outcome first I know) or less often, when a driver (e.g. a business challenge or opportunity) is identified. IT and technical solutions (including solutions such as Smartsheet) are viewed as flexible, pliable tools to get the job done.

As a fundamental, the sad truth is that Execs do not understand the infrastructure because that is not their primary focus (i.e. their agenda). They are not interested in what goes on underneath the hood. They simply want accurate information and relevant insights because that is what ensures that they're not questioned, they'll keep their job and will "earn" their bonuses.

/consultant hat off/

My view is that the addition of "gates" that prevent incorrect data (i.e. data that possibly excludes sheets because of a column name change) should be a no-brainer. It still does not preclude the creation of information management policies, separation of duties etc. but adds that extra catch-all to ensure that the wrong data is not provided to execs and then (god forbid) presented to an external audience (e.g the board) by them.

Even with the creation of a more rigid structure, Smartsheet does not effectively help a limited subset of admins manage the impact that changes to column names has on all reports. It's way too easy to miss something or for a sheet that was included in a report to be excluded because of a change.

Another 2 cents worth 

Kind regards,

Chris McKay

J. Craig Williams

We really should go get a beer and leave these other people alone.

I spent much of my adult working life using / building simulators for power plant training. One of the nice things was the platform was flexible. One of the horrors was that the platform was flexible.

I believe the same is true for Smartsheet. The platform is flexible so it can be what the user wants, but as I discovered upon my arrival in Europe a few years ago, there will never be world peace because we can't even agree on the simple things like which direction the clock should go (down or up) in a hockey game.

We want to use Smartsheet because it is flexible and configurable. We complain that it isn't smart enough to recognize when we config it a certain way. If we had 10 Excel workbooks that came from the same original master, and someone changed something in one of them, there would be very little griping about inconsistencies because they are individual entities, not spawns of the same cloth with that lineage important. But Smartsheet gives us the illusion that they are connected AND KNOW IT through the use of Reports. But that is backwards. The Report is the one asking the questions, not the one being told the answers, if that make sense. "Show me all the rows in all the sheets where [Column A] = "Merkel"". Great! I'll do that, says the Report. It does not ask "show me all the rows in all the sheets where the column has been defined as having the content we have defined as the characteristics of X. Today that happens to be called [Column A] but the name is not important." 

In Smartsheet, names have power and can be changed at the whim of an Admin. To prevent the user from making the C-level weenie look bad in front of her peers, we should remove the admins. And that's what we get when we buy an off-the-shelf product that doesn't even hint that you can change the structure. 

How does Smartsheet's Reports know the difference between three date columns? By their names.  

The price of freedom is diligence. If we want the users to change things, we have to be prepared that they will. But I can envision no tool that I would want to automatically watch for changes and then interpret why they were made. This is our only hope that some of you (and maybe me, but I doubt it) will still be needed after the coming robot revolution.  What automation can do in this case is provide a tool that says "this column has changed -- there are 47 instances of the old column name that still exist, here are links to them -- do something, do nothing, your choice" ... but then we will run into the case where I am only shared to 47 of 99 instances of that column and will screw something up if I take action. Or the case that 99 times out of 100 I don't want to see that notification and because everything is configurable, I will turn it off and forget it was there.

Craig