Formatting Text as a Number in Merge Field...

[Protour Travel]

Hello all. I hope you are having a great weekend.

I am running into a problem that basically results in eye candy (nice but not totally necessary) on a report.

As of now, my Starting, Ending and Total Mileages do not have a comma separator. I would like to place a comma in the proper place in these fields.

The only things I can find online that pertains to this do not work for me no matter what I try (and I've spent hours trying). Here is what I have referenced...

Filemaker Help; Formatting Numbers in a Text Calculation Field and

Filemaker Help; How to Handle Formatting Number, Date and Time Merge Fields and Formatted Numbers Within Text Fields.

Here is the code I am using now...

If ( IsEmpty ( Mileage_Starting ); ""; "Starting Mileage: " & Mileage_Starting) & ¶ &

If ( IsEmpty ( Mileage_Ending ); ""; "Ending Mileage: " & Mileage_Ending) & ¶ &

If ( IsEmpty ( Mileage_Total ); ""; "Total Miles: " & Mileage_Total) & ¶ &

"Amount Due: $" & EarnedToday

Please bear in mind that the mileage figures are not always 6 digits in length. The could run anywhere from 1 mile to 1,000,001+ miles.

Also, the .00 on the back end of the Amount Due is not necessary. Most companies use rounded numbers.

Thanks in advance for any help with this matter.

Regards,

Charlie

[Lee Smith]

How come you are using text?

[Protour Travel]

Because if there are no mileage figures, say the driver didn't drive that day, then I don't want the field labels appearing on the report as below. If I save the merge field as it is written and as a number, it returns only numbers formatted as below.

[comment]

Try =

If ( not IsEmpty ( Mileage_Starting ) ; "Starting Mileage: " & NumToJText ( Mileage_Starting ; 1 ; 0 ) & ¶ ) &

... etc.

[Protour Travel]

That worked beautifully. Thank you!

[Protour Travel]

Hey Comment, Here's one for ya'...

Using the NumToJText function worked perfectly but I'd like to go a little further with the eye candy aspect if you (or anyone else) can help.

The problem; the field isn't shrinking on the printout and from what I can gather here, it has something to do with the JText function and using the TrimAll function as a fix.

I know the problem is within this field because when I removed it, everything else slid and shrunk perfectly.

Needless to say, the information contained in the link above is completely Greek Japanese to me. A sample of the printed field is attached.

As we would say down south, Thanky.

[comment]

Can you post the current version of your calculation?

[Protour Travel]

If (not IsEmpty ( Mileage_Starting); "Starting Mileage: " & NumToJText ( Mileage_Starting;1;0 );"") & ¶ &

If (not IsEmpty ( Mileage_Ending); "Ending Mileage: " & NumToJText ( Mileage_Ending;1;0 );"") & ¶ &

If (not IsEmpty ( Mileage_Total ); "Total Miles: " & NumToJText ( Mileage_Total; 1; 0);"") & ¶ &

If (not IsEmpty ( EarnedToday );"Total Due: $" & NumToJText ( EarnedToday; 1; 0))

[comment]

If ( not IsEmpty ( Mileage_Starting ) ; "Starting Mileage: " & NumToJText ( Mileage_Starting ; 1 ; 0 ) & ¶ ) &

If (not IsEmpty ( Mileage_Starting); "Starting Mileage: " & NumToJText ( Mileage_Starting;1;0 );"") & ¶ &

Can you spot the difference?

[Protour Travel]

Yes, and thank you. I wasn't aware that you could close the parentheses outside the carriage return even though that is what you wrote in your earlier post. I guess you really can't get the full gist of a book by reading only half of each sentence, huh?

[comment]

I wasn't aware that you could close the parentheses outside the carriage return

Why not? It's text, just like "Starting Mileage: ". To make it depend upon the test, include it inside the result of the If() function.

You can also use the List() function to achieve the same result:

List (

If ( not IsEmpty ( Mileage_Starting ) ; "Starting Mileage: " & NumToJText ( Mileage_Starting ; 1 ; 0 ) ) ;

If ( ... ) ;

If ( ... ) ;

...

)