Smartsheet Tips: All About System Columns

Blog

Smartsheet Tips: All About System Columns

With the latest Smartsheet update, we added a new column type that gives you the ability to automatically keep track of certain data points like a “modified by” date or “created by” date – also known as System Columns.  

System Columns are really handy for instantaneously keeping track of certain data points. They immediately add certain types of data into your rows, tracking information like the date a new row was added, or the name of the person that last updated a row.  

This week, we want to give you some tips and tricks to customize one of these new System Columns – the Auto-Number Column, to fit your processes and streamline your collaboration.  

Automagic!  Using Auto-Number System Columns

Automatically adding a unique identifier to a row is helpful in so many different scenarios: requests for help, customer orders, maintenance updates... the list goes on.

You can use the Auto-Number column to assign a simple value (e.g. 1, 2, 3) to new row submissions. If you’d like to make this column a bit more powerful and specific to your processes, change the Display Format criteria to create a combination of numbers and letters that provide context for your unique process.

Here are some examples of how you could use the Auto-Number Column to help streamline your processes:

Track Submissions by Quarter Looking to break sheet submissions down by quarter? An Auto-Number Column is perfect for this. At the beginning of the quarter, insert a Prefix value of “Q1-” in the column Display Format.  With this formatting updated, when new items are entered to your sheet, the Row ID will automatically include “Q1-” in front of a unique number.  When Q2 rolls around, all you need to do is update the Prefix from “Q1-” to “Q2-”.  

The unique numbers will continue, with the new quarter added as a prefix.  If you would like a new starting number at the beginning of each quarter, all you need to do is enter a Starting Number of “1” to the Starting Number field in the formatting form.

Track Submissions by Date Another handy prefix for Auto-Number Columns: add a date prefix to the Auto-Number Column to combine an automated unique ID with the date.  In the prefix field, add “{MM}/{DD}/{YY} -”.  This specifies to append the date in MM/DD/YY format to the prefix before a unique ID number.

There are four other types of system-generated Columns to choose from, in addition to Auto-Number:

  • Created (Date) This System Column is super handy for keeping track of the exact date and time that a row was created – GREAT to use with web forms!
  • Created By Use this System Column to keep track of the email address of any collaborator who created a row.
  • Modified (Date) Use this System Column to keep track of the last time a change was made to each row. 
  • Modified By This System Column will automatically keep track of the email address for the last person who updated data in a row.

NOTE: For both ‘Modified (Date)’ and ‘Modified By’ columns, Comment updates, new attachments, and formatting changes (like a different font color) are not logged in this column.

A few other handy things to know about the new System Columns in Smartsheet:

  • Each type of System Column can only be used once in each sheet.
  • The cells in System Columns start out empty, and will generate value as you and others input data and save your sheet.
  • The values in System Columns are display-only.  You can adjust the formatting options for System Columns to adjust the appearance of these values.
  • If you change an existing column in your sheet to a Modified Date/By or Created Date/By system column, you will overwrite any existing cell data you have in those columns.

What are your ideas for using System Columns to save time?  Let us know.

Til next week, dig in and explore!

- Support Team

Comments

System Columns

A time stamp system column would be great. It would allow for so many more uses of the smart sheet.

Re: System Columns

Kelly Anthony's picture
Hi Stuart, The System Column option 'Created (Date)' acts as a time stamp. It automatically notes the date and time something new is added to your sheet - pretty handy! Thanks, Kelly

Created by - Web forms

The Created by column always displays as "web-form@smartsheet.com" when a row is generated by a form submission. It seems that this field should be able to automatically pass in the user name when a collaborator of the sheet fills out the form. This is a major disadvantage compared to Google drive forms and sheets which collects the user name (gmail) automatically.

Re: Created by - Web forms

Kelly Anthony's picture
Hi Jim, Thanks for the suggestion – I've shared your feedback with our product team to consider for future Smartsheet enhancements. In the meantime, you can try adding a field to your web form for users to submit their email address along with all of the other information you're collecting. Thanks again, Kelly

date modified for specific cell

I've been looking everywhere for how to find the last modified date for a specific cell. I would like to find out when a checkbox value is changed to 1 so I can automatically know when it was completed. Any suggestions?

Re: date modified for specific cell

Kelly Anthony's picture
Hi Mike, Checking the cell history is the only way to get this information, which is a manual process. Thanks for the suggestion of an automatic way to get this information, I've shared it with our Product team! Best, Kelly

Re: date modified for specific cell

Wanted to check in and see if there was any change on this. We need to set up Reminders prompted by the date a checkbox was checked. (We would use Notifications but need the ability to put in messages to the team members, which can be done with Reminders but not with Notifications.) If there is a formula workaround for this, that would also be great. I tried using the following, which fills in today's date, but it is not fixed, and updates everyday. =IF(([Completion Checkbox]1 = 1), TODAY(), "") Thanks!

RE: date modified for a specific cell

Emily Esposito's picture
Hi Angelene -- In Smartsheet today, reminders and alerts are only triggered by date columns. There isn't a way to set-up reminders prompted by the date the checkbox was checked. However, I have passed along this comment to our Product team. Best, Emily

System Date formats

The Created and Modified date formatted cells appear in US date format (mm/dd/yy) no matter what the personal settings are. Is this a bug?

Re: System Date formats

Kelly Anthony's picture
Hi Simon, Yes, this was a bug – confirmed and fixed on October 8th. Please let us know if you're still seeing the issue, happy to help! Thanks, Kelly

International Date Formats

I am part of an international team that is using SmartSheet for project management. We have come up against the usual issue in such an environment of whether to interpret, for example, 6/7/14 as June 07, 2014 or July 06, 2014. For SmartSheet to be an effective tool in the international setting, having dates formatted with the month in alpha-characters, for example “04-Sep-14”, is essential. This is needed in the Column Properties rather than the Personal Settings. I understand that individual users can change their personal settings according to their preferred language and that this affects the date display. However, in collaborative on-line meetings where the SmartSheet is presented by a user in one country but seen by participants in various other countries, having dates formatted with the month in alpha-characters is critical to avoid international misunderstandings.

Re: International Date Formats

Kelly Anthony's picture
Hi Daniel, Thanks for your feedback – I've shared your request for different date formats with our product team. In the meantime, you can insert a text/number column and then use the month formula (info on that formula here: http://help.smartsheet.com/customer/portal/articles/775363-using-formulas) to find the month. Please feel free to email us at support@smartsheet.com if you need some extra help getting this formula to work in your project sheets. Thanks! Kelly

Can an autonumber field be

Can an autonumber field be treated as a number in a formula?

Re: Can an autonumber field be

Kelly Anthony's picture
Hi Tomas, You can use the LEFT or RIGHT and VALUE functions to make the numbers in the autonumber column a number. Would look something like: =VALUE(LEFT([Row ID]1, 4)) If you have any other questions, feel free to email us at support@smartsheet.com for more help. Thanks! Kelly

Created by - Web forms

Any update on this issue above originally raised by Jim as below: The Created by column always displays as "web-form@smartsheet.com" when a row is generated by a form submission. It seems that this field should be able to automatically pass in the user name when a collaborator of the sheet fills out the form. This is a major disadvantage compared to Google drive forms and sheets which collects the user name (gmail) automatically. This is a bit of an annoyance and prevents the deployment for certain aspects where we need to capture the email address accurately. Is this planned to be developed any time soon?

Re: Created by - Web forms

Kelly Anthony's picture
Hi Neil, Thanks for adding your vote to this update to the "Created by" system column, I've shared your request with our product team, though it's not on the product roadmap for the near future. In the meantime, if you've got a system that works well with Google Forms, you can sync Google Forms submissions with Smartsheet (http://www.smartsheet.com/apps/google-forms-smartsheet-sync). Let us know if you have any other questions! Thanks, Kelly

The System Columns - Autonumber - what time is it?

The created date has been great to use on webforms. My question is when you have folks submitting webforms from different time zones - what time is stamped ? Is it the local time of the smartsheet owner or is it the local time of the webform submitter? Thanks!

Re: The System Columns - Autonumber - what time is it?

Kelly Anthony's picture
Hi Joe, Good question - the timestamp that you see in your sheet reflects the time that you have your Smartsheet account set to. To update the time setting in your account, you can adjust your personal settings. Thanks! Kelly

Auto-Number - Character Count

Is it possible to leverage the auto-number column as a character count for another column in the sheet?

Re: Auto-Number - Character Count

Kelly Anthony's picture
Hi Caitlin, Today in Smartsheet, auto-number columns only capture date and time changes. I've shared your suggestion with our product team – would be a great addition to our system columns. Thanks! Kelly

Auto Number is Maxing out at 68

Hello, we are implementing an auto number for creating unique row ID #'s and ideally we would like the system to auto-generate a new sequential number for each new row. However, its only assigning existing rows up to row 68 a number then stops. Is there a way that the number can carry on for new rows and / or even existing rows beyond the 68th row on the sheet?

RE: Auto Number is Maxing out at 68

Emily Esposito's picture
Jesse, great question. Please email us at support@smartsheet.com and someone from our support team would be happy to help you. Thank you, Emily

How to display auto-number in the form confirmation?

I want to display the auto-number assigned to the request in the Form confirmation message so the requester knows what number their entry was assigned (so they can reference it later in emails/etc). I can't find any web form option that allows that. Any thoughts?

Same request...supply auto-number field in WF confirmation email

Hello Emily and Mary Ellen, Any progress on this request? I just had the same client ask. Thank you, Michael

RE: Same request...supply auto-number field in WF confirmation

Emily Esposito's picture
Hi Michael and Mary Ellen -- thank you for your comments. The ability to display the auto-number field in an email is not available in Smartsheet today. However, I have passed this feedback on to our Product team for future consideration. Thank you, Emily

Is there a way to add data

Is there a way to add data from a webform to the auto numbering system. For example if you have two sites could you create a numbering system that would automatically input 'site 1' ie abc0001site1 or for 'site 2' abc0001site2

RE: Is there a way to add data

Emily Esposito's picture
Hi Jonathon -- Unfortunately, this is not possible in Smartsheet today. I have passed this along to our Product team as they continue to work on web forms. As a workaround, you could have site 1 or site 2 entered as a default hidden field via the web form, and then have a third column that adds the two together with a formula. Best, Emily

Auto-Numbering: Multilevel Formats?

Does SmartSheet have the capability of auto-numbering in multilevel formats? I would like for the numbering level to change with each indent. For example: 1.A.1.a. or 1.1.1.1.

RE: Auto-Numbering: Multilevel Formats?

Emily Esposito's picture
Hi Debbi -- Smartsheet does not currently have this feature, however I have submitted this feedback to our Product team for future consideration. Best, Emily

Auto-Numbering: Parent to Children Rows (Multilevel Formats)

I guess auto-numbering will really prove its mettle if it can also auto-number sub-tasks (children tasks) in line with the format of its parent task. For example, Parent A is 1.00; Children A1 to A3 are 1.01, 1.02 and 1.03 respectively. Parent B without children tasks is 2.00. Whilst Parent C is 3.00; Children C1 to C4 are 3.01, 3.02, 3.03 and 3.04 respectively! So if for any reason after initial set-up Parent B is to have one sub-task, then Smartsheet auto-numbers it 2.01 - say after refresh. Can Smartsheet currently do this? If not, when might it be added?

RE: Auto-Numbering: Parent to Children Rows (Multilevel Formats)

Diana Ramos's picture
Hello Michael - thanks for reaching out to us. The Smartsheet app does not currently have this functionality and it's not scheduled to be implemented in the near future. We do appreciate your feedback, however, and I have passed along your request to our Product Team for future consideration. Best, Diana

Auto numbering parent to child rows

I just watched a video "Work Smart: Simplify New Product Development webinar" in which it said auto-numbering automatically occurs for hierarchy. This is at about the 7:50 mark in the video. I like the concept and would love to know the best way to achieve this.

RE: Auto numbering parent to child rows

Smartsheet Community Manager's picture
Hi Rod, Here is our guide on auto numbering: http://bit.ly/1RPo8Bg and please feel free to contact us with any questions! Thanks, The Smartsheet Team

Modified Date/Time

We have a sheet to track issues (simple help desk) which has a quick turnaround for resolutions (often within the same day). We want to capture the date and time the ticket changes states (In Queue, Triage, In Progress, Validate, Closed) how can we capture that info for reporting?

RE: Modified Date/Time

Emily Esposito's picture
Hi Bill -- We'd be happy to help! Please email Support at support@smartsheet.com so we can lend a hand. Best, Emily

Same Question

Hi Bill, did you get an answer? I need to capture same information you asked. It will be great if you can share it with me. Thanks! Gustavo

edited by

how do i track who edited the sheet. it shows as publisheduser @ but i need the emails to appear , same for discussion

RE: edited by

Emily Esposito's picture
Hi Benjamin -- You will need to share the sheet, rather than publish it, to track with email addresses. Let us know if you have any other questions! -Emily

how to track if sheet was modified in report

Is there a way in a report to show the date that all sheets were modified in any way? thanks, JP

RE: how to track if sheet was modified in report

Smartsheet Community Manager's picture
Hello JP! Yes, you can show the Modified Date column in your report. It will show the last modified date for each sheet. Here's another helpful blog on columns: https://www.smartsheet.com/blog/support-tip-3-column-tricks. Hope that helps! Please let me know if you have any other questions.

Auto numbering based on previous dropdown choice in web form

Let's say...In my webform a drop down has a choice of A, B or C. If the user chooses A then the auto number will be A0001 and on. If the User chooses C for example, then the auto number will be C0001 and on. So the next A choice would be A0002. Because the same webform is used for multiple things I would like the auto numbering to reflect the choices made. Is this possible?

RE: Auto numbering based on previous dropdown choice in web form

Smartsheet Community Manager's picture
Hi Cliff, good question. Unfortunately, this isn't a feature at this time. But we really like the idea! I've submitted a request to our Product Team and will let you know if anything changes. Please let us know if we can help you further.

auto number assigned - reply

We allow our clients to submit a web form which feeds to a ticket tracker. It assigns a new ticket number. That Smartsheet is internal only and not for the client. IS there a way to auto respond that new ticket number to the web form writer? I have the auto respond set now to reply we have received and it is in our Smartsheet, but no number is given. Can that canned response be altered?

RE: auto number assigned - reply

Smartsheet Community Manager's picture
Brad, yes, the canned response can be changed. When editing the form, select Form Options > Confirmation Options. This can be customized, but it cannot contain dynamic data such as an auto number. Auto numbers also cannot be included in the confirmation email. You can send the row to the user after it is submitted, which would include the auto number, or you can use Zapier to build a zap which can email the content of the row to an email address (entered in the row, when the row is submitted). Thanks for the question!

System Folder Function

Hi Team, I'm looking to utilise Smartsheet as a complaints management tool via webforms. It works really well but the only stumbling block I've come across is that I haven't figured out a way to show the age of a complaint. I've added the timestamp but I'd love to be able to have a column that shows the number of days that has passed since the complaint was logged as a webform. Any ideas? Thanks for an awesome product, Alwyn

RE: System Folder Function

Smartsheet Community Manager's picture
Hello Alwyn, Add a Text/Number column to the sheet with a formula that calculates the number of days between the Created (Date) and TODAY(). Using the formula =TODAY() - DATEONLY(Created3) , Created3 is the cell reference of the Created (Date) cell. Thanks! - The Smartsheet Team

Last Entry

Is it possible yet to loop through a date column and a text column to find the last entry for the text column based on date?

RE: Last Entry

Smartsheet Community Manager's picture
Hello Ed, Here are a couple ideas for you to work with: You can use the formula =MAX(DateRange:DateRange) This will display the max date from the range of cells. You can use this to identify the last entry. You could also build a report that pulls in the entire sheet and then sort based on date, to show the most recent tasks at the top of the report. Hope this helps, thanks! - The Smartsheet Team

checking who updated line by line

I like this auto modified column option - but what if i want to see line by line in my sheet who last made the changes. currently when i make a change to a line item - it shows in the "modified by column" or modified date column that i made changes to the sheet last.. but i would like to know if i can see who updated line 10..20..30... and not just show the person that updated the sheet last. thanks, M!

RE: checking who updated line by line

Smartsheet Community Manager's picture
Thanks for your question, Marta. You can get a more detailed list of who modified and when, by right clicking the desired cell and selecting "View History." This will provide a detailed list of any changes. Regards! - The Smartsheet Team

Pages

Add new comment