Jump to content
Salesforce and other SMB Solutions are coming soon. ×

3 decimal places to text


Tagus

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?

Link to comment
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)

Link to comment
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)

Link to comment
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")

Link to comment
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.

Link to comment
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.

Link to comment
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.

Link to comment
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. cool.gif

Link to comment
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:

Link to comment
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? laugh.gif

Link to comment
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!

Link to comment
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)

Link to comment
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.

Link to comment
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")

Link to comment
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?

Link to comment
Share on other sites

David,

 

So you're as hard than a woodforest cool.gif

 

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....

tongue.gif

Link to comment
Share on other sites



×
×
  • Create New...

Important Information

Terms of Use