🤔 How to: Remove .00 from Math column numbers without rounding up/down with "Precision 1"? (w/ pic)

[JamieErvin]

Hello!

I’m not sure I’m asking this correctly.

I’d like to eliminate the .00 after my numbers but “Precision 1” rounds the numbers (which I don’t want).

I need whole numbers with no .00 if possible.

Is there an alternative?

Thanks

[Jeff_Hager]

Are you saying that sometimes you do have decimal amounts, but you want to truncate the decimal instead of round it? Just basing off your examples you have .00, so there would be no rounding taking place if you set precision to 1. Just want to make sure I understand what kinds of numbers you are working with. There are math formulas such as Floor or Trunc which would work for you, but I’m not sure you need to take it that far.

[ThinhDinh]

Would be better if we hear from him what he expects in these test cases:

123.00
123.01
123.06

[JamieErvin]

@Jeff_Hager @ThinhDinh

Thanks for the reply.

These are mostly going to be whole numbers usually with occasional .5:
“This is a 1 spread” (rather than 1.0 spread)
“This spread is 1.5” (and 1.50 is fine here)

“This is a 2.25 spread” (perfect, not rounded up)
“This is a 2.5 spread” (this is fine because 2.5 and 2.50 works for us, here)

In the column called premium we’d prefer:
“Premium (cr) .12” (rather than 0.12)

Also for the sleg column, most of those whole numbers end with 5 or 0.
“225” (not 225.00)
“225.50” (without rounding up but 225.5 is ok here too).

Hope that makes sense.

[Jeff_Hager]

I don’t know if that cleared things up or made me more confused. Is this more of a cosmetic thing where you just want to remove any leading or trailing zeros?

[Darren_Murphy]

Please answer the question from @ThinhDinh …

@ThinhDinh

Would be better if we hear from him what he expects in these test cases:

123.00
123.01
123.06

Given each of the above, what would you expect as the output?

[JamieErvin]

Yes.

[Jeff_Hager]

Try this in a javascript column. The p1 parameter would be your number.

function trimLeadingAndTrailingZeros(number) {
    
    // Convert number to string
    let strNum = String(number);
    
    // Trim leading zeros
    strNum = strNum.replace(/^0+/, '');
    
    // Trim trailing zeros
    strNum = strNum.replace(/0+$/, '');
    
    // Check if number is valid
    if (isNaN(strNum) || !strNum) {
        return '0'; 
    }

    return strNum;
}

return trimLeadingAndTrailingZeros(p1)

[JamieErvin]

Thank you. I’m too new to understand what to do with that information, though.

Where do I put it?

[Jeff_Hager]

@Jeff_Hager

Try this in a javascript column

[JamieErvin]

Ohhhhh!???

All of the Glide community help posts I ran search implied JavaScript wasn’t supported.

So it’s in a column now! Got it!

Will implement ASAP and report back!

Thanks Jeff!

[Jeff_Hager]

JavaScript is not supported on the front end, such as in a Rich Text component, or injecting javascript some other way. It’s always been available through the back end in the table as a column type.

[JamieErvin]

@Jeff_Hager

function trimLeadingAndTrailingZeros(number) {
    
    // Convert number to string
    let strNum = String(number);
    
    // Trim leading zeros
    strNum = strNum.replace(/^0+/, '');
    
    // Trim trailing zeros
    strNum = strNum.replace(/0+$/, '');
    
    // Check if number is valid
    if (isNaN(strNum) || !strNum) {
        return '0'; 
    }

    return strNum;
}

return trimLeadingAndTrailingZeros(p1)

First of all, thank you for this. Much appreciated.

Secondly, am I to understand that I would need to create a javascipt column for each number I need?

[ThinhDinh]

If you mean you need them to format different columns, then yes, each column requires a JS companion.

[JamieErvin]

Let me rephrase what I mean.

I understand the script will format the column.

So in this case will (p1) be the same number on every row in this case the .21

OR

Is there a way to adjust this so that I can have the script trimming each of the numbers in a column instead of just one number I put in p1?

[Jeff_Hager]

You are specifying a column for your p1 parameter. With that, the javascript will run for each value in each row. It would only be the same if you were to hard coded an actual number instead of specifying a column.

With that, the code you copied and pasted doesn’t appear to be formatted the same as what I shared, which is leading the the error you are seeing. It’s important that it looks the same.

[Darren_Murphy]

For what it’s worth, you can get the same effect as the JavaScript with an if-then-else column:

This is something I stumbled upon a couple of months ago. As long as you have at least two IF cases, it will work - in the sense that fractional numbers have trailing zeros removed, and exact numbers are returned whole. I accidentally discovered this behaviour whilst looking for a solution for a client that had exactly this requirement.

[JamieErvin]

Thank you kindly! I’ll implement pronto and report back!

[nathanaelb]

What a nice hidden “feature”. Very random (no pun intended) but useful. I have no idea how I could remember that though

[ThinhDinh]

@Darren_Murphy

This is something I stumbled upon a couple of months ago. As long as you have at least two IF cases, it will work - in the sense that fractional numbers have trailing zeros removed, and exact numbers are returned whole. I accidentally discovered this behaviour whilst looking for a solution for a client that had exactly this requirement.

This is pure magic.