Sheet with a roster and sick leave form. I want to update the roster automatically with sick leave.

Josh Swendsen

Hello guys,

I've prepared a new roster for work and have approx. 50 staff on it. I have also created a form for leave. Previously people would be manually entering in sick, personal and annual leave. I want to automate this process and update roster box with leave if the person lodges the form. Is there any way I would be able to do this?

James Keuning

I think it will be a problem that you have a column for each date. But I am having a hard time explaining why.

You are going to have a difficult time specifying which field needs to update. Normally, a user on a form would pick fields that tell Smartsheet which fields to influence. For example: The form has only two fields - Person Name and Event Date. A user inputs two pieces of data and Smartsheet cruises down the Person Date column until if finds the right person, and then it does the same in the Event Date.

The Name part will be the same with your set up, but the Date is... well I can't figure it out. Because you can tell Smartsheet to reference a specific column, but you can't use a parameter to specify a column. You can't say, "give me the data from the column whose name is in this field." Is this making sense? Maybe I need to say that the Column name is on the left side of the parentheses in Smartsheet. It seems like your set up requires it to be on the right. A column name is is the name of a variable, it's not a possible value of a variable. It's the answer, not the question. The problem with your data now is that you create a new column every day. That means the data is not normalized. You can get away with that in Smartsheet, but yours is not a case where certain rules should be broken.

You would mitigate this by pivoting the data by adding a DATE column. So if you have 30 columns right now, your sheet will contain [current record count] X 30.

James Keuning

I think it will be a problem that you have a column for each date. But I am having a hard time explaining why.

You are going to have a difficult time specifying which field needs to update. Normally, a user on a form would pick fields that tell Smartsheet which fields to influence. For example: The form has only two fields - Person Name and Event Date. A user inputs two pieces of data and Smartsheet cruises down the Person Date column until if finds the right person, and then it does the same in the Event Date.

The Name part will be the same with your set up, but the Date is... well I can't figure it out. Because you can tell Smartsheet to reference a specific column, but you can't use a parameter to specify a column. You can't say, "give me the data from the column whose name is in this field." Is this making sense? Maybe I need to say that the Column name is on the left side of the parentheses in Smartsheet. It seems like your set up requires it to be on the right. A column name is is the name of a variable, it's not a possible value of a variable. It's the answer, not the question. The problem with your data now is that you create a new column every day. That means the data is not normalized. You can get away with that in Smartsheet, but yours is not a case where certain rules should be broken.

You would mitigate this by pivoting the data by adding a DATE column. So if you have 30 columns right now, your sheet will contain [current record count] X 30.