Combining Dates For a "Sort By Date"

Mike Rini

I have an interesting scenario where I am combining 3 SmartSheets into 1, and this happens in three phases:

1. First, someone initiates a request by submitting a request through a form into the first Smart Sheet. When they submit the request, the request is simultaneously copied over to a "Master Sheet." When it does this, it also copies over the field entitled "Created Date" which is a System Date.
2. Second, another team member completes the request. During this process, DataMesh links together (and updates) the "Master Sheet" based on the Request ID. When DataMesh copies it over, we also copy over that form's "System Date."
3. Last, when there is a Change request for one of the Request IDs, a second DataMesh process is run to update the corresponding Request ID. When DataMesh copies it over, we also copy over that form's "System Date."

So, we are combining 3 forms into 1, but also capturing each form's "System Date." The reason we do that is to try and have the ability to Sort by the most recent Recent request submitted. When we had 3 separate forms, it was easy because we always Sorted by the Request ID in Descending order. Now that everything is in one sheet, it's harder. I've attached an example of

I created some if-then-else logic to parse the three date columns (there's some easy logic to determine when to use which date), but it doesn't seem to keep the Sort. Each time we login, we have to re-sort the sheet and it's not the best scenario.

date-comparisons.PNG

Question: Is there another way that I'm not thinking of that this can be done, or is the fact that we're constantly updating the "Sort by" field what's messing this up?

Paul Newcome

Sheets do not automatically sort new entries. Try building a report instead. Those do automatically sort new entries.

Mike Rini

Our other sheets automatically show the last entry added, at the top, and I think we were able to sort oldest to newest or newest to oldest depending on what we wanted to see. So my question still is - shouldn't there be a way since there is a sort feature in the sheet itself?? We don't manage from a report, we manage from the sheet.

Andrée Starå

Hi @Mike Rini,

Great idea! It would be a great addition to Smartsheet features to be able to sort and keep it sorted in sheets.

Please submit an Enhancement Request when you have a moment

As a possible workaround, you could add an automatic numbering column to always be able to come back to the default order you had it.

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Mike Rini

I don't think that would work because I'm updating the same row each time. In my other sheets, a new row is Added. I think it introduces the same issue.

So one interesting thing to note is that It is a formula - which might be the issue. Would it work if during the DataMesh sequence, I updated the Date field? Or, is it also possible that the field I'm trying to Sort on is a Text field, and not a Date Field?

Andrée Starå

@Mike Rini

Ok.

How did it go? If it's not solved, I would recommend checking what the values are coming in like to the sheet. Are they NUMBERS or TEXT?

Mike Rini

Hey - thanks for checking. We're still experiencing the same problem. I'm wondering if it's b/c it's a formula, or if it's b/c it's a formula and a TEXT field (i.e. - should be date). It was real easy when it was a new request added, that one always was surfaced to the top.

Andrée Starå

@Mike Rini

You're welcome!

Did you check what they are interpreted as? NUMBERS or TEXT?