Custom Function to Format Currency

[copies]

David Head helped me get started on this a couple of months ago. Originally I was trying to find a way to enter a number into a field, and have that field return it with two decimal places--kind of like a calculator with the function set to "A" for add. Now I'd like to modify it so that if a number is entered with more than 2 decimal places, it should be returned as entered.

 

For instance:

If you enter "1234" into the field, it returns "12.34". If you enter "123.4", it returns "123.40". In addition, if you enter "123.455" it returns the number as entered.

 

The CF works in most instances, except where the last digit entered in the field is a zero and no decimal is entered--i.e., 10 or 80 or 100 or 9000. In this case, the field returns the correct amount, but without two decimals. "10" becomes ".1" instead of ".10"; "100 becomes "1" instead of "1.00"; "9000" becomes "90" instead of "90.00".

 

Here's how the function is written:

 

Case(Filter (fieldname;".") = ".";

(Case

(Length(fieldname) - Position (GetAsText (fieldname); "." ; Length(fieldname);-1) = 1;fieldname & "0";

Length(fieldname) - Position (GetAsText (fieldname); "." ; Length(fieldname);-1) > 1;fieldname));

(Case

(fieldname ≤ 9; fieldname/100;

fieldname ≥ 10; Left(fieldname; Length(fieldname)-2) & "." & Right(fieldname;2

)

)

)

)

 

Also, for some reason when I change

"(fieldname ≤ 9; fieldname/100;" to "(fieldname

 

Anyhow, can someone out there help me? I'd surely appreciate it.

[David Head]

I haven't checked everything this is doing but you seem to have added a few extra parentheses. Try this:

 

Case(
 Filter (fieldname;".") = ".";
 Case (
   Length(fieldname) - Position (GetAsText (fieldname); "." ; Length(fieldname); -1) = 1;
   fieldname & "0";
   Length(fieldname) - Position (GetAsText (fieldname); "." ; Length(fieldname);-1) > 1;
   fieldname
  )
  ;
  Case (
     fieldname < 10; fieldname/100;
     Left(fieldname; Length(fieldname)-2) & "." & Right(fieldname;2)
   )
)

[David Head]

There were some issues with the above, especially when using it for an auto-entered calculated replace. I have worked on a new calculation and this seems to work fine for all cases listed and some others that I tested. this is the code for a custom function (FixDecimal) with one argument (field):

 

Let([
     dataIsDecimal = Position( field; "."; 1; 1 );
     lengthData = Length( field );
     lengthNumber = Length( GetAsNumber( field ) );
     dpCount = lengthdata - Position( field; "."; 1; 1 )
   ];
   If(
       dataIsDecimal;
       GetAsText( field ) & Choose( dpCount; "00"; "0" );
       If(
           field < 10;
           field / 100;
           Left( field; lengthData - 2 ) & "." & Right( field; 2 )
       )  // end If
   )  // end If
)  //End Let

 

This will return the following results:

 

Data    Modified
8	.08
10	.10
14	.14
80	.80
81	.81
100	1.00
8.1	8.10
8.10	8.10
811	8.11
1234	12.34
123.4	123.40
123.456	123.456
100	1.00
14.	14.00
14.0	14.00
14.0000	14.0000
14.000	14.000
14.001	14.001
14.1	14.10
9000	90.00
123.4	123.40
123.456	123.456

[copies]

Wow! Thanks, David. I was just getting ready to ask you about those issues and noted your second posting. I appreciate your responding with the fixed version of my function--I see where I had made the mistakes. That was a great help in my learning curve!

 

I haven't tried your FixDecimal function yet--it's past my bedtime and I have a real job to go to in the morning. It sure looks like it will do the trick. I'll work on it tomorrow evening....

 

As always, I really appreciate your help. I'll let you know once I've worked it out.

[copies]

David, thanks again for helping with this CF. It works perfect, I just had to make sure that the fields were formatted to "leave data formatted as entered." This a great forum, and thanks to all who participate and help out!

[copies]

While the custom function works well, I didn't consider that at times I use a script button to convert the result of the CF to a negative number.

At present, if "123" is entered into the field, the result is "$1.23".

 

The script button would

1) act as an "enter" key to set the field, convert it to a negative number ($-1.23), and move to the next field, or in some cases

2) change the number to a negative amount by placing the cursor in that field and hitting the refund button.

 

The script I've been using before was simply

Set Field [(Get(ActiveFieldContents) * -100)]

 

It still works, however when a number ending in 2 zeros is entered, those 2 zeros disappear. Thus "100" should return "-1.00" but returns "-1"; "10000" should return "-100.00" but returns "-100". The result works well in formulas, but is visually inconsistent with other numbers that don't end in zero.

 

Am I asking too much? I've spent quite a few hours in trial and error, and can't seem to get it right. I'd appreciate any help you can give me.

 

Many Thanks!!!! in advance.

[David Head]

I don't understand what you mean by a 'script button'. Is that a button running a script? If the user wanted a negative number, wouldn't they just enter -100 for it to convert to -1.00? The CF does not account for negative numbers at this stage.

 

How many times have I heard a customer say "I didn't consider that at times...". Well that will cost you.

[copies]

This is a learning process for me, and I'm trying to figure out why it doesn't work. Once I found that the script that I was using didn't do what I expected, I tried different ways of achieving the same result, one of which was entering -100. That gives me the same result--a "-1".

 

>>How many times have I heard a customer say "I didn't consider that at times...". Well that will cost you.

 

(How much?????)

 

Mebbe I ought to re-word that:

Since the script button (yes, that's a button that runs a script) worked with the first CF, I didn't consider that it might not work with the revision.

 

The button allows the user to enter a number, and convert it to a negative amount--as can be done on some calculators, where you can enter a number as either a positive or negative amount. It was designed to work as both a refund and a discount button, and it makes it very easy for the user to understand how to do both without having to think over-long about it. This is a POS program which is used when there are lots of customers waiting to pay their money. The more I'm able to decrease the amount of time it takes the clerk to figure out how to do something, the happier the customers are and the less frustrated the clerks are!

 

I'm really not sure if it's possible to do what I'm asking, and while it does indeed give the correct mathmetical result, I'd like to have it visually consistent with everything else. If it can't be done, I guess I can live with it. But if it can, I'll be a happy camper!

 

 

 

Thanks again,