Decimals added in drop down value from form

Question

Hi, I have a column that can only be a drop down value.  Unfortunately this value, project numbers, has a mix of both decimal and non-decimal values.  For example, project 356 could be the main project, but has ancillary projects: 356.1, 356.2, ect.

What I'm seeing is that in my project ID column Smartsheet has started adding a decimal.  For example in my drop down list the project is 356, but when the form populates the row it uses 356.0.

Is this a bug?  I would think strictly restricting to the drop down values would only give you exactly what the drop down value is.  I saw some other questions around this that recommended fixing the formatting of the cell, but I don't think I can do this since I do sometimes need the decimal.  And I can't simply delete all the .0's because sometimes a department will include it when they create the project number.

Ultimately my issue is that this has crippled my lookup tables and Data Mesh.

Any suggestions?

electrichead · Answer

Thanks @S.Stone, that is aligned with my thoughts around the issue and you've given me some ideas to try.  I've got a few balls in the air but I will post back on how it goes.

S.Stone · Answer

@electrichead that makes me think Smartsheet is just using the existing properties of the rows above to determine how to set the properties of the new row. I don't think Smartsheet itself has a way to differentiate between 356 and 356.0 either (except the aforementioned ' to have it appear as a string). The problem with copying and pasting new rows as it stands is this error could pop up again if you get three in a row.

I don't have Data Mesh, but I'm wondering if you had a "dummy sheet" where you could send the rows to first (so they wouldn't have any earlier rows to "latch onto" for the decimal formatting). Then use an automation to copy it from the dummy sheet over to your main sheet. And one more automation to move it from the dummy sheet to an archive sheet (so your dummy sheet always stays empty).

This is probably 1) over-engineered and 2) may not work, especially if the data ends up converting the decimal point on your target sheet anyway. But this is the next test I would run, if I was trying to troubleshoot and come up with solutions.

The other thing I thought of was still back to the ' but to figure out if you could have a formula add it. I couldn't get Smartsheet to add it in a way that preserved the difference between 365 and 365.0, but it's possible your source data (Excel or whatever) could have a formula to search for anything without a decimal and then add the ' so it applies everywhere.

At the end of the day though, it seems to me that the biggest hurdle here is that Smartsheet has combined the text and number settings to the same column, and it somehow looks to the data above to determine how to handle new data. This makes it difficult to tell new data how to consistently behave without simply changing the column formatting to text (which presumably would resolve the issue on its own).

electrichead · Answer

It looks like for the first 105 form responses, which had only a few project numbers with decimals scattered around, the column format behaved properly.  Then I had three requests in a row that required a decimal number, 36127.1, 36127.2, and 36127.3.  Everything after that has a decimal number.  And… everything before it is documented properly still, with no decimal number, or only has one when it is supposed to.