Can you modify the result of a calculation field

[Johnny777]

I have a field on an invoice layout which produces a calculated value using a LIST function. This field is then used as a merge field on an invoice letter.

I have one annoying problem.

The items in the list function are seperated by tabs (Tab symbol set to a global variable $$tab).

The first item on the line is the product description which varies in character length, which in effect means I end up with something like this

Firstproductdescr??:?:?? 550 0 550

secondproddescr?? 550 0 550

So can I either format the List function so that these columns line up, or is it possible to modify the text in the returned value. or is it possible to put the returned data into another field that would allow it to be modified.

[comment]

Why don't you format the portion of the text object where this fields is merged, by inserting tab stops in the text ruler?

Incidentally, why don't you print directly from the line items table?

[Johnny777]

Hi

Thanks for that. The tab solution works very well.

Didn't want to print directly from line items as I have two join tables and didn't want to go down the loop route if possible.

Although the problem I have now is that numbers on the list function will not format properly ie I need them to be strictly all numbers with two characters after the decimal point.

There doesn't seem to be any way to format them within the calculation itself so may have to abandon this approach and start again.

[comment]

numbers on the list function will not format properly ie I need them to be strictly all numbers with two characters after the decimal point.

There doesn't seem to be any way to format them within the calculation itself

Of course there is a way. But it would be easier to do this in the original calculation in the items table (the one that concatenates the fields before they are "collected" by the List() function).

[Johnny777]

That's excellent - so can anyone tell me how you would do it. I have been thru all the number functions there are ones to truncate and round but I can't find one that formats a number specifically to two decimal points even if there are no pence ie 399.00

[comment]

See here, for example:

http://fmforums.com/forum/showpost.php?post/257391/

BTW, this is a text operation. "399.00" is not a number.

Edited March 13, 2009 by Guest

[Johnny777]

Hi

Tried the little formula on the link in the last post ie

Int (Net Cost) & "."& Right("0" & Net Cost*100 ; 2)

The problem I now have is some of these numbers are not integers, the two digits to the right of the dec point are the number of pence. So if I use the above (leaving out the integer function) 65.63 becomes 65.63.00, although the integers format properly. Is there another little tweak I can use to stop this happening

[comment]

As long as Net Cost is rounded to the nearest pence, the formula will work. If you leave out the Int() function, it will not work.

[Johnny777]

Hello thanks very much for the reply and your patience so far.

Sorry this still does not work and I can't see how logically it can work.

I have two types of numbers I need to format 1)45 In this instance it will work perfectly but for 2)45.43 The int function reduces it to 45 (assuming the precision is two and then adds the zeros = 45.00 losing the pence. It needs to be something along the lines of "if number is an integer then apply the formula else round to two decimal points" - Is that possible?

[comment]

45.43 The int function reduces it to 45 (assuming the precision is two and then adds the zeros = 45.00 losing the pence.

No, that's NOT what the formula does. It returns "£45.43" when the input is 45.43, "£45.40" when the input is 45.4, and "£45.00" when the input is 45.