Salesforce and other SMB Solutions are coming soon. ×

# 3 decimal places to text

## Recommended Posts

I have number fields which are formatted to show 3 decimal places on my BOM's which they do. When I enter 37 in the number field it shows and prints as 37.000

I've created a calculation based on two number fields and the resulting string of text shows as 37 x 52.375 but I would like 37.000 x 52.375

Is there any way of retaining the 3 zero's other than entering them initially?

##### Share on other sites

After a very strenuous brain workout, and thanks to Ugo's assistance, use this as your calcuation. Just replace num1 and num2 with your two number fields.

Int(num1) & "." & Left(Substitute(Round(Mod(Abs(num1),1),3),".","") & "000",3) & " x " & Int(num2) & "." & Left(Substitute(Round(Mod(Abs(num2),1),3),".","") & "000",3)

##### Share on other sites

Okay, that one doesn't work for (-1,0).

This one should, however.

Choose(Sign(num1)+1,"-") & Abs(Int(num1)) & "." & Left(Substitute(Round(Mod(Abs(num1),1),3),".","") & "000",3) & " x " & Choose(Sign(num2)+1,"-") & Abs(Int(num2)) & "." & Left(Substitute(Round(Mod(Abs(num2),1),3),".","") & "000",3)

##### Share on other sites

This will work too:

Round(num1, 3) &

Case(Position(num1, ".", 1, 1),

Choose(Length(num1) - Position(num1, ".", 1, 1), "000", "00", "0"),

".000") &

" x " &

Round(num2, 3) &

Case(Position(num2, ".", 1, 1),

Choose(Length(num2) - Position(num2, ".", 1, 1), "000", "00", "0"),

".000")

##### Share on other sites

Hi David.

Unfortunately, that one doesn't include the leading zero for (-1,1). It also fails on any number ending in +/-[.9995,.9999], as I just found out mine does, as well.

Back to the drawing board.

##### Share on other sites

[qb]Unfortunately, that one doesn't include the leading zero for (-1,1).[/qb]
Huh? When I enter the values -1 and 1 (I assume that is what you mean), the calculation returns -1.000 x 1.000. What leading zero are you referring to?

[qb]It also fails on any number ending in +/-[.9995,.9999], as I just found out mine does, as well.[/qb]
OK. Bug fixed for that. New calculation is:

Round(num1, 3) &

Case(Position(Round(num1, 3), ".", 1, 1),

Choose(Length(num1) - Position(num1, ".", 1, 1), "000", "00", "0"),

".000") &

" x " &

Round(num2, 3) &

Case(Position(Round(num2, 3), ".", 1, 1),

Choose(Length(num2) - Position(num2, ".", 1, 1), "000", "00", "0"),

".000")

The old one failed on any number rounded to a whole number. Seems to work OK now.

##### Share on other sites

Hi David. (x,y) is mathematician speak for 'exclusively between x and y', meaning it doesn't include the x and y values, but all values in between, whereas [x,y] means 'inclusively between x and y.' So I meant that there was no leading zero for numbers between -1 and 1, not including the endpoints. I should have written (-1,0) and (0,1), actually, as "0" works fine. try +/- 0.1 to see what I mean. Nice job on the rounding fix, though.

##### Share on other sites

OK, I see what you mean now. (x,y) is also used for Cartesian coordinates on a plane so that's what confused me.

The new calculation:

```Case(num1 > -1 and num1 < 1, "0") & Round(num1, 3) &
Case(Position(Round(num1, 3), ".", 1, 1),
Choose(Length(num1) - Position(num1, ".", 1, 1), "000", "00", "0"),
".000") &
" x " &
Case(num2 > -1 and num2 < 1, "0") & Round(num2, 3) &
Case(Position(Round(num2, 3), ".", 1, 1),
Choose(Length(num2) - Position(num2, ".", 1, 1), "000", "00", "0"),
".000")```

Bit complicated but at least it works.

##### Share on other sites

Thanks for that ...

Next time I fly to Cartesia, I'll use that to find out how close I am ...

Wolf

##### Share on other sites

Great work people!! It took quite a while to put this in place but it works lovely. Really appreciate your assistance! Thank you.

##### Share on other sites

Aaargh !!!

David, I ended with somehow a similar calc structure, but noticed that this calc still breaks when entering a num = -0,01 for instance.

Should I say it Tagus...check FMForums for a couple of workable calcs. You'd find out that you've brought a tedious case here, and quite got my brain blust this Week-end, along with some others on this Neighboor Forum.

JT, you should have told us where this thread originated....

I browsed CafÃ© but curiously didn't came accross it until now.

:eek:

##### Share on other sites

My bad, Ugo. I thought you would have seen it already. Ernst's sample file with calculation is the most useful one I've seen so far. I may attempt to tweak it later. But I would agree that's the solution for which Tagus should look...unless, of course, Ray wishes to grace us with his solution?

##### Share on other sites

Hey guys, let's not lose sight of the original question. This calculation needs to work for a BOM (bill of materials) in which negative numbers will not occur. Except maybe for credits?

However for the sake of completeness, this is it:

```Case(num1 > -1 and num1 < 0, "-0", num1 < 1 and num1 > 0, "0") & Abs(Round(num1, 3))
& Case(Position(Round(num1, 3), ".", 1, 1),
Choose(Length(num1) - Position(num1, ".", 1, 1), "000", "00", "0"), ".000") &
" x " &
Case(num2 > -1 and num2 < 0, "-0", num2 < 1 and num2 > 0, "0") & Abs(Round(num2, 3))
& Case(Position(Round(num2, 3), ".", 1, 1),
Choose(Length(num2) - Position(num2, ".", 1, 1), "000", "00", "0"), ".000")```

Enjoy!

##### Share on other sites

Still not...

-1 ==>1

12,9599 ==>12,96

Her's Ernst calc for this.

Substitute the comma.

``` Case(Round(num,2)<0,"-")&Truncate(Round(Abs(num),2),0)&","& Right(100+Mod(Round(Abs(num)*100,0),100),2)
```

##### Share on other sites

Your right David. There will never be a negative number because the fields represent dimensions, not quantities. I suppose to those so inclined the challenge is to make this work for any situation which may arise. Good on you if you want to stretch the envelope of the FileMaker world but please on my behalf no more brain busting. Thank you very much for all you've done. All the people whom I've dealt with on this Forum have impressed me greatly.

##### Share on other sites

Damn! Try this:

```Case(num1 > -1 and num1 < 0, "-0", num1 < 1 and num1 > 0, "0", num1 < 0, "-") & Abs(Round(num1, 3))
& Case(Position(Round(num1, 3), ".", 1, 1),
Choose(Length(num1) - Position(num1, ".", 1, 1), "000", "00", "0"),
".000") &
" x " &
Case(num2 > -1 and num2 < 0, "-0", num2 < 1 and num2 > 0, "0", num2 < 0, "-") & Abs(Round(num2, 3))
& Case(Position(Round(num2, 3), ".", 1, 1),
Choose(Length(num2) - Position(num2, ".", 1, 1), "000", "00", "0"),
".000")```

##### Share on other sites

Originally posted by Ugo DI LUCA:

[qb]Here's Ernst calc for this.

Substitute the comma.

``` Case(Round(num,2)<0,"-")&Truncate(Round(Abs(num),2),0)&","& Right(100+Mod(Round(Abs(num)*100,0),100),2)
```

[/qb]

Elegant. So I simplified it and made it work for three decimal places instead of two:

` Case(num1<0,"-")&Int(Round(Abs(num1), 3))&"."& Right(1000+Mod(Round(Abs(num1)*1000,0),1000),3) `

Done now?

##### Share on other sites

David,

So you're as hard than a woodforest

12,95999 ==>12,96

0,0999 ===>0,1

and

0,000001 ==> 00,000

0,999 ==>01,000

These aren't that bad, just an extra 0 at the beginning

Don't resign....

##### Share on other sites

OK mine got out of hand and needed simplification. I defer to the modified version of Ernst's calculation.

I knew it David

Congrats...

Bravo!

• ### Images

• By Soliant Consulting,
• By Soliant Consulting,
• By Soliant Consulting,
• ### Forum Statistics

• Total Topics
33.7k
• Total Posts
141.6k
×
×
• Create New...