How can I format Numbers on Export?

[philipcaplan]

I need to Export data from a FMPro6 database to feed into a page-layout program. Tab-delimited works excellently for me, except for one thing!

I have some currency fields (created as calculated numbers), and in the Exported data I need for them to appear with a currency symbol before and exactly 2 decimal places after the point, rounded to two places.

FOR EXAMPLE: the value 2 should appear in the exported file as $2.00, and 2.5 as $2.50, and 2.5673 as $2.57.

The correct technique in FMPro seems to be to do the Export from a layout in which the field has been appropriately formatted as a Number.

Trouble is, the fields concerned cannot be formatted because they contain calculations incorporating global fields, etc.

How can I get my nice currency amounts into my Exported file?

PHILIP CAPLAN

[RalphL]

Trouble is, the fields concerned cannot be formatted because they contain calculations incorporating global fields, etc.

Where did you get this idea? They can not be indexed but that has nothing to do with formatting.

[philipcaplan]

OK. Good question, RalphL!

You've made me realize I'm mixing up one thing that came up while trying to work around my problem with the actual problem itself!

Which is, that I want to put text around some Number values during Export. And I need these Numbers to have 'currency' format applied to them (currency symbol and forcing to exactly 2 decimal places, such as $5.00 even if the Number is 5, 5.0 or 5.001).

If I just wanted the Number exported alone, I could of course do the Export from a Layout with that 'currency format' applied to that field. But then I would get the value with "tabs" before and after it (I need tab-separated data) but no automatic way to get other text in there without surplus tabs!!

A very frustrating feature of FMPro6, if it seems the only way to get a number 'formatted' within a Calculation is to write one's own "formatting" algorithm.

If that is the only way, can anybody provide a nice, neat, foolproof algorithm for adding ".00" to numbers without a decimal value, ".x0" in place of plain old ".x", and rounding more than 2 decimal places to exactly 2 places.

I can think of a whole bunch of If or Case statements to cover all these possibilities, but it doesn't look like a very elegant solution. And I'm not very confident in my algorithm-writing abilities!

Hope that explains my predicament better. Thanks to anyone who can help!

PHILIP CAPLAN

[comment]

Check here .

[philipcaplan]

Thanks, comment. I followed the threads you lead me to, and can see that this is *NOT* a problem with a trivial solution! :> Inserting a reliable algorithm stretching over many clauses does *NOT* seem an elegant or safe way to handle a simple problem.

I solved my immediate problem by putting the QuarkXPress tags I needed into 2 fields before and after the currency field, using the "Format on output" option to get the currency field correct, then search-and-replaced some marker text to get rid of the unwanted extra tabs that having separate fields created.

So, my final question is "Why haven't/can't the FMPro programmers included the functionality of the Number Format into a Calculation formula to avoid all these contortions?"

Just a friendly question! Don't jump on me please!! PHILIP CAPLAN

[comment]

That's a good question. There are many good questions like that, even better ones. The bottom line is, FMP's main mission is to be a database program that is easy to use. It's when you try to make it do something that's beyond its main mission, that you run into snags.