Appointment Number Generation

Mark Rojas

I am building a Project Management system to deal with a high flow of scheduling. Currently I have the system built using the auto number column type, however when it came to creating a copy of the row I quickly found that the system will not update that value when it is passed out to a separate grid and cleared of appointment specific data and passed back thus creating a duplicate Appointment Number (I cant clear that value). The work around I have developed is as follows: I have assigned each project a value at its time of creation (an initial appointment number) and each time I find a duplicate Project Number and Appointment Number another column will use a MAX(COLLECT())) to find the highest value within the Appointment Number column and add a set value to it thus incrementing the system by a given amount. The issue with this is that I have to reference the appointment numbers previously given to projects to collect the max value and now its a circular ref. Has anyone ever had any success with generating their own numbering system within Smartsheet?

Toufong Vang

@Mark Rojas Using a temporary sheet to assign the AppointmentID for a newly created or copied appointment, you can leverage the Modified Date timestamp to create a unique number most of the time. (Similar approach for assigning a newly created/copied project.) Use workflow automation to "Copy rows to..." into this temporary sheet

YEAR(ModifiedDate@row) + RIGHT(" 0" + MONTH(ModifiedDate@row), 2) + RIGHT(" 0" + DAY(ModifiedDate@row), 2) + SUBSTITUTE(TIME(RIGHT(ModifiedDate@row, 8), 1), ":", "")

The expression above returns a string/text, YYYYMMDDhhmm.

Count the number of existing appointments in all of the other sheets AND add to it the number of appointments in the temporary sheet that are older (e.g., 134, or "xxxx"). Appending this sum, "YYYYMMDDhhmmxxxx", will give you a unique string. Differentiate your appointments (A) from your projects (P). For example, A2024010910451234.

Count_Appointments_Sheet1 + Count_Appointments_Sheet2 + Count_Appointments_Sheet3 + Count_rows_TempSheet_WHERE_ModifiedDate_LessThan_currentRow

The AppointmentID formula = "A" + the expression that got the digits from the timestamp + the expression getting the count of appointment rows created before the current one.

Then immediately move the row back (or forward). (If forward, you may need RouteTo and RoutedFrom helper columns so that the temp sheet knows where to send the row after the ID has been assigned.) When the row is out of the temp sheet, the unique ID number becomes static.

Hope this helped!

Mark Rojas

@Toufong Vang Thank you! I came up with another solution since the system is already getting its record creation from a master intake sheet and then metadata is being indexed into the grid. I simply have a second "holding" database that holds a blank record of the project every time a new project is created. Then as a secondary appointment is needed I have a check box that is Index(matched()) to the "holding" database when checked it copies the row to the grid it is needed in and the auto-number just adds a new value. I will be testing out your solution however in a different application since my company is looking into "Smart-Numbers" for storing data within the unique string and this would be an excellent tool to assist with this.