filemaker format numbers in email script

I have set an email to trigger that contains number fields from my layout.  It is easy to format the fields on the layout; however, when stuffed into an email via a FM script, they are not formatted.  I can put a dollar sign text in front of them, but can I format them so that they will include a thousand separator and decimal place?  My percent figures are easy to format with the Round function, but I cannot figure out how to format currency when stuffed into an email.  Thank you.
LVL 2
rvfowler2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lesouefCommented:
display format is just a display filter, it does not alter the real value in the field. so you must make it up your self:
"$" & Round(money;2)
rvfowler2Author Commented:
I must have misunderstood your formula because when I entered it, it made no change at all.  What I entered was

"$" & Round(PriceField;2)

Where price field is the actual field that I want to display in the email.  It did not put a comma, etc.  I already had the $.  Thank you.
lesouefCommented:
if you are outside a table context, you must specify the table name also:
"$" & Round(table::PriceField;2)
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

rvfowler2Author Commented:
I didn't do it manually, I did it via selection, so it should work.  Attached are screen prints.  What am I doing wrong?
Formatting-Currency.doc
lesouefCommented:
all I can see from this is that the PFpurchasePriceprice would not be a number but text?
rvfowler2Author Commented:
No, all are formatted as numbers or calculations (NOI).  See my revised try attached.  Notice that even when I used the Round formula, it made no difference to the numbers inputted into the email.  See additional screen prints attached.
Formatting-Currency.doc
webwyzsystemsCommented:
The display filter only affects the contents upon exporting when the format box is ticked and on the screen - otherwise, in scripts you will only ever get exactly what is contained in the field. You have to convert numbers into text with formatting manually. So, make a calculation that will return the appropriate TEXT formatted into an amount of currency based on your number.

If you have to do a bunch of these, you might want to base the calculation on the contents of a global field. That way you can run a whole pile of numbers through it if you need to.



lesouefCommented:
I can't check your file, I don't have word on this one (better use images for captures), but what I said has been tested, I mean I did send an email with this simple formula: "$" & round(fieldvalue;2), and it does work.
So if round leaves it untouched, your number must be an integer or stored in a text field instead of a number field.
Peter HarrisFileMaker Developer at CognitiveCommented:
If the value you are rounding is from a Calculation check inside the calculation dialog (in the lower left) that the result is set to Number not Text. See example screen dump.
FileMaker-Pro-Shot.jpg
rvfowler2Author Commented:
First, I did check and each calculation is checked to return a number and not text.  Also, each non-calculated field is formatted as a number, not text.  leseouf seems to think that the Round condition does return a currency format, but I cannot make it do that yet.  Regarding formatting the output as text, I don't have a lot of fields to do (just 10), but I'm not sure how to do it.  I believe I vaguely remember some type of formula that can count places from the left or right and then possibly I could insert a comma, but it seems there must be an easier way.
lesouefCommented:
round does not return a currency but a number which must be converted to text.
as you send an email, you don't need to store the text string, you can get it on the fly directly in the email body calc. definition as shown above
"$" & Round(table::PriceField;2)
of course this calc result (email body) must be set to text
rvfowler2Author Commented:
lesouef, I know you've stated the above before, but I can't replicate it.  And, my partner who returned today says he does not see how a Round formula produces a currency format; it just will format to two decimal places (in your formula above).  

Again, look at the attached, which has the screen print of me trying to use your Round formula in my email script and the resulting email that shows no difference between the format of the old and new expense numbers.  Am I doing something wrong in my email script or does it look OK to you?
CurrencyFormat2.doc
webwyzsystemsCommented:
This was actually a bit of a mental twister to unravel....i need coffee now.... ;)
NOTE: the input field "Money" may not contain a comma or be in the billions. You could also use
a variable or a script parameter for the input - just change the word Money.

formattedCurrencyNum =
Let (
[
isDecimal=If (PatternCount(Money;".") = 0;0;1);
dollars = If (not isDecimal;Money; Left ( Money;Position ( Money; "."; 1; 1)-1));
formattedDollars=Case(
Length(dollars)<7;Left(dollars;Length(dollars)-3) & "," & Right(dollars;3);
Length(dollars)<10;Left(dollars;Length(dollars)-6) & "," & Middle ( dollars; Length(dollars)-6 ; 3 ) & "," & Right(dollars;3);
"error"
);
cents=Case(
not isDecimal;"00";
Length(Right(Money;Length(Money)-Position ( Money; "."; 1; 1)))=1; Right(Money;Length(Money)-Position ( Money; "."; 1; 1)) & "0";
Length(Right(Money;Length(Money)-Position ( Money; "."; 1; 1)))=0;"00";
Right(Money;Length(Money)-Position ( Money; "."; 1; 1))
)
] ;
"$" & formattedDollars & "." &  cents
 )
rvfowler2Author Commented:
OK, brilliant.  It works with one line.  Will complete the other lines and post it next.  By the way, if you paste the above formula into Word, you can quickly replace all instances of "money" with your desire field for a quick completed code.
rvfowler2Author Commented:
Oops, not so fast.  This doesn't seem to work with millions, as it replaces a "9" with a "2".  The new asking price should read $299,000.00

New Asking Price:  $2,299,000.00
Old Asking Price:  $299,000.00
webwyzsystemsCommented:
You must have put a comma in the input. I did mention that it wouldn't work if you do this...

However, you can re-write the calculation to strip out the commas first if you need to.

temp=Substitute(Money,",";"");
then go thru and replace the MONEY word with temp.

I thought you had a number field as an input - so there shouldn't be commas in it.
rvfowler2Author Commented:
Actually it is a # field and even so shouldn't have an extra 2
webwyzsystemsCommented:
I cannot get the calculation to repeat your results. When I enter the value:
299000 I get back $299,000.00

If I input 299,000 I get back $2,299,000.00 - which of course is an error because of the comma.

Don't pass anything but digits or, digits and a single decimal point in the input - otherwise the calc will fail.

Because your input has a comma in it, the calculation is failing.

Try it without the comma. Don't put a comma in the input.


webwyzsystemsCommented:
Ahhhh....if you have any other characters trailing...it will fail too. So, if there is a trailing space - the errant result is also repeatable.
with "299000 "   <-one space at the end
the calc also shows $2,299,000.00

rvfowler2Author Commented:
Shoot, I was hoping you were right, but the attached shows my cursor in the field.  I pressed delete about 20 times after the last 0 to ensure no trailing space.  Your code looks good.  Why only this issue when it goes to 7 figures?  Pasting how I applied your code:

Let ([isDecimal=If (PatternCount(WoodrichSales::PFTotalGrossIncome;".") = 0;0;1);
dollars = If (not isDecimal;WoodrichSales::PFTotalGrossIncome; Left ( WoodrichSales::PFTotalGrossIncome;Position ( WoodrichSales::PFTotalGrossIncome; "."; 1; 1)-1));
formattedDollars=Case(
Length(dollars)<7;Left(dollars;Length(dollars)-3) & "," & Right(dollars;3);
Length(dollars)<10;Left(dollars;Length(dollars)-6) & "," & Middle ( dollars; Length(dollars)-6 ; 3 ) & "," & Right(dollars;3);"error");
cents=Case(
not isDecimal;"00";
Length(Right(WoodrichSales::PFTotalGrossIncome;Length(WoodrichSales::PFTotalGrossIncome)-Position ( WoodrichSales::PFTotalGrossIncome; "."; 1; 1)))=1; Right(WoodrichSales::PFTotalGrossIncome;Length(WoodrichSales::PFTotalGrossIncome)-Position ( WoodrichSales::PFTotalGrossIncome; "."; 1; 1)) & "0";
Length(Right(WoodrichSales::PFTotalGrossIncome;Length(WoodrichSales::PFTotalGrossIncome)-Position ( WoodrichSales::PFTotalGrossIncome; "."; 1; 1)))=0;"00";
Right(WoodrichSales::PFTotalGrossIncome;Length(WoodrichSales::PFTotalGrossIncome)-Position ( WoodrichSales::PFTotalGrossIncome; "."; 1; 1)))] ;
"$" & formattedDollars & "." &  cents)

Currency-Format-Millions.doc
lesouefCommented:
round does make a value with 2 decimals, but what is a currency but a value with a "$" prefix?
I got lost on the way, and do not see why you should take into account the currency filtered values if it is a number? and even if the user users a different entry format, fm stores number always the same. so somewhere your values are text before rounding them.
now, if you need a thousands separator, you need to make a current format custom function or script.
I'll come back later on if webw formula does not work.
rvfowler2Author Commented:
Yes, I need the thousand and millions separator.  The formula just above your post works great for thousands, but has a hiccup withe millions separator, changing a "0" to a "2".  Very strange.  And, I double checked to make sure there were no extra spaces or tabs in the field, which is a number field.
rvfowler2Author Commented:
Another example on another record:

New Asking Price:  $1,172,000.00
Old Asking Price:  $172,500.00

All I did was delete the last 0, save, and then readd the last 0.  Notice that the New Asking Price in the email added a "1" and dropped off the last "5".  Very strange.  I changed the Income which changed the NOI, but since these were both in the thousands, they worked fine.  Only when a number is in the millions does the formula cause a problem.
lesouefCommented:
try this:, assuming table::money is your number
Let ( [
number=table::money;
t = Int ( GetAsNumber ( 2) ) ;
prec = Case ( Length ( t ) ; t ; 2 ) ;
dec = Case ( Length ( "." ) ; "."; "." ) ;
n = Round ( number + 0 ; prec ) ;
sgn = Case ( n < 0 ; "-" ) ;
n = Abs ( n );
x = NumToJText ( n ; 1 ; 0 ) ;
y = NumToJText ( n * 10^prec ; 0 ; 0 ) ;
z = Position ( x ; "." ; 1 ; 1 )
] ;
sgn & "$" & 
Substitute ( Case ( z = 1 ; 0 ; z ; Left ( x ; z - 1 ) ; x ) ; [ "," ; "," ] )

Case (
prec > 0 ;
dec & Right ( 10^prec & y ; prec )
) )

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rvfowler2Author Commented:
We have a winner!!  Worked perfectly.  And I like that your answer is a bit more concise.  Do you know why Web's didn't work on millions?  Also, going to give him 1/3 credit cause at least he pointed us in the right direction and got us 90% of the way there.
rvfowler2Author Commented:
Thanks for the help; sorry it was a bit time consuming.
webwyzsystemsCommented:
lol...nice code lesouef!

I flipped two things around - filtered out everything by using the GetAsNumber(Money).
But most importantly - my parse was 1 char out - changed a 6 to a 5, then it worked.

Lesouef's is much nicer anyways - give him all the points.
rvfowler2Author Commented:
Thanks.  Points already assigned so not sure how to change now.  Oh, by the way, a hidden error in my code at the very bottom was how I handled the cap rate.  In the middle of the round formula, I added *100 and then it came out right.  I'm sure you guys know that, but great to give a heads up to people who use rates and %'s.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.