Reformatting numbers

[David Nelson]

We have number fields which are dollars and I have formatted them with two decimal points.  Sometimes people put 3 decimal points and that is allowed - even more if they wished.  But what they don't want to happen is that when they click into the field, a 3.00 turns into a 3.  They think it is very distracting and weird.

 

I tried formatting the number using inspector but that just handles the display.  So what I want is to default with .00 I think, as actual data.  And when they enter a number, it formats it so the following is the result (first number is data entry and second number is number result in the field itself.

 

1 ... 1.00

1.245 ... 1.245

1.1 ... 1.10

-1.1 ... -1.10

 

so it fills with at least two decimals, padded with zeros if necessary.  I have tried many things and have read but cannot figure out the auto-enter that I need.  I know I need to uncheck "replace the existing value" so it updates.  I have included an fp7 file if anyone wishes to take a look for me.  I would really appreciate the help.

 

This is all because people complain when the field shows 3,000.00 but when you click into it it displays 3000.  I think I need Mod!  Then I also need to convert the existing data to catch it up.  Is this how others handle the problem?

numbers.zip

[No_access]

you can use  as the calculation in the ctestresult copy field.  also format the field to money

 

If(dollars=Int(dollars);dollars &".00";dollars)

[David Nelson]

Hello No_access and thank you for helping.  If the field is 1.2 it still displays it as 1.2 and I want to change the data to 1.20.  Almost there.  This is what kept messing me up too. It needs to look at those decimals and compute.  I tried counting the number of decimal numbers but that failed as well.

[No_access]

Try this 

Int(dollars)
& If(Round(Mod(dollars;1); 2) > 0; "";".")

& Left(Round(Mod(dollars; 1);2) & "0"; 3)

 

 

this will round  two  2 digits, if you change it to round to 3 or more.. when 1.35 is entered it will show as 1.350

[Lee Smith]

FileMaker does not natively show the 0 at he end or proceeding a number entered. i.e. 1.20 entered will display as 1.2. 

 

You can display the zeros by setting the field in Inspector to 2 digits. I don't think  there is a think good way to calculate a digit, I believe it can come back o bite you in the butt.

 

Training the users to look at what they enter would be the best approach. Maybe a simple Conditional Formatting would do it for you.

David Nelson.fp7.zip

[David Nelson]

Hi Lee, thank you for helping me as well.  I can't train my users since they are my bosses and they tell me what to do and I'm just trying to provide them with what they request.  And when I click into the field and 3.00 becomes 3 it can be disjointing so I understand their dislike of it.  I DO set the format in inspector to display the zeros but that does not eliminate the jarring they experience.

 

Hi No_Access, we are much closer and I appreciate you hanging in there with me.  We also have negative numbers and I notice that it turns -.35 into 0.65. Maybe Lee is right that it will bite me in the behind.

 

Mathematically I would simply add the dollars and cents but leave it whether it is negative or positive - not sure how - and then pad with 0 using Right("00";2) somehow but of course it isn't that simple particularly if they have .045 and they want to keep it as is.  I will continue to work on your idea.  BTW, they will not want rounding either, sorry I did not mention that to begin with.

 

Otherwise I suppose I could loop and set with zeros at night.  Gulp.  And then I will talk to them again.  They are reasonable folks.  Usually.

[doughemi]

Make the display fields calculation fields, result text.  if the number fields are to be used as inputs, place them under the text fields, and make the text fields non-enterable in browse mode (in the field behavior dialog).  If this is the case, also conditionally format the text field so that it is transparent if empty, but your desired color if not empty.

 

The calculation  would be something like

 

Case(

NumberField = Int(NumberField); NumberField & ".00";

NumberField * 10 = Int(NumberField * 10); NumberField & "0";

NumberField

)

[David Nelson]

Hi Doug,

 

Thank you for responding.  I finally got a chance to try this.  Using a calculation unfortunately doesn't stop the behavior I am trying to avoid - clicking into the field still changes 3.00 to 3 for entry.  But when I use your same calc and put it as auto entry then it works too so that is what I did.  I have attached the file showing the final and the various results but will also put it here:

 

Let(money=Case(Self = Int( Self); Self & ".00";
Self * 10 = Int(Self * 10); Self & "0";
Self
) ;
money
)

 

I cannot thank you enough.  You have made me look like a hero to my boss and that is always appreciated.  If anyone sees a problem with this technique please respond because I will be using it extensively in this solution.  It seems perfect though.

 

David

[David Nelson]

I forgot the file

numbers2.zip