Tracking a date change

Linda Woodworth

I am new to Smartsheet in a company with limited knowledge about the features.  We use a combination of sheets and a web form for 'ad-hoc' requests that are managed in a queue.  In the queue, there is a "Due Date" column.  This date can change over time and the manager for the area would love to be able to track that change and report off of it.  

 

In other words, when the request was created (via the web form) it will come with a due date (say 11/5/16).  Over time, that date may change once or many times until the request is completed (the Due Date could now be 12/15/16).   I know you can see the history of the cell but we ideally would want to do some historical reporting off of the changes made.  

 

On another sheet, we use a 'planned date' vs a 'revised date' column but that is a bit clumsy.

 

Thoughts?

Ryan vanDeventer

I am also looking for a way to do this. Very interested if anyone has any ideas

Chris Comer

I suggest using Zapier or Azuqua to automatically populate your second sheet each time there's a date change. If this is the only thing you plan to automate at this time, Zapier is the best option. Azuqua might be overkill. 

Linda Woodworth

Hi Chris,

Not sure I understand the suggestion exactly.  Are you thinking that we can create a report or second sheet when there is a change to one of the rows due date?

 

Basically, the sheet today is like a task sheet with a column for a Due Date.  The Due date can change over time and we would love to be able to do some reporting on tne changes to the due date (based on other columns) like "Assigned to", delay length (calculated if possible from original due date to new), etc.

J. Craig Williams

Linda,

 

Zapier (www.zapier.com) and Azuqua are automation tools.

I believe what Chris was suggesting was copying the [Due Date] (and other columns) to another sheet as a log.

Each change would result in a new row in the log sheet, off which your analysis would be done.

 

Your original query ("change of due date") seems to be modified by your response to Chris -- the due date changes and you want to see changes to who is tasked with it?

It is late and I'm missing something.

 

Back to Zapier, there is no reliable way to trigger on change of cell (such as date) - at least not that I have found. Zapier's trigger is on any changes to the row and the filter options may still result in false positives on them.

 

The API might be your best alternative. That's what Zapier and Azuqua are accessing, but you might need a customized solution.

 

Craig

 

Chris Comer

Hi Linda-

 

Yes, I was suggesting ( and should have been clearer) that a second sheet be used as a tracking log. We use Zapier and Azuqua extensively to automate many repetitive workflow tasks in Smartsheet, so I'm confident it can be done using one of these services. We use Zapier for more basic tasks such as notifications and cell updates, and Azuaqua for anything complex. Which service best suits your needs depends on the end result you're looking achieve.

 

Are you looking to track each specific date or just the number of times the date changes? Is there anything else you're looking to accomplish or automate?

 

Chris

J. Craig Williams

Chris,

 

Do you use both Zapier and Azuqua because of the number of tasks being automated?

Azuqua's pricing makes my eyes water. I don't have clients large enough to justify the expense - basic at 10x what they are paying for Smartsheet? Love the platform, when I can play with it.

 

If Smartsheet would just expose their data for 'highlight changes', what she is looking for can be done easily I believe. I don't it is passed to the API and certainly isn't to a formula.

 

I'm adding this to my API testing use-cases.

 

Craig

 

 

Shaine Greenwood

The API might be able to accomplish this through the cell history object, but it would take some manual logic to get the changes you need:

 

https://smartsheet-platform.github.io/api-docs/?javascript#cell-object

J. Craig Williams

I just completely missed that Shaine. 

Thank you!

 

Craig

Linda Woodworth

Thanks for the advice and comments. 

 

CHRIS - I am working with the actual end-user right now to see what she really needs/wants.  Then I can answer your questions.

 

SHAINE - thanks for the API suggestion - not sure that I have the right resources available to support us on that track but it's good to know what is available!

Richard Rymill SBP

Linda 

i'm sure there is a simple solution to this workflow and that it can be created entirely in Smartsheet once you do as you say.... talk to the end user about what they need, because the solution is all about them and what they want.

 

I am a great Fan of Keep it Simple (if you can) then go complex when that is exhausted? 

Once you have done the workflow requirements gathering with your end user, if you are not that familiar with all the functionality that is available to you in Smartsheet, then share it with the community, or my team offer a free initial 1 hour consultation, which includes Craig,  and we have between us solved many such requirements and have good examples to show.

As we regularly say...  some time invested in the workflow design will pay dividends over time and the users will love it, which is what matters!.

Hope that helps?

RichardR