Formatting merge fields

[steve weinberg]

Among my layouts are letters (text boxes?) built using the "insert merge field" function to bring data from those fields into the letters. These include dollar amounts, name and address info, dates, etc.

I have always been unable to format these merge fields when they are numbers or dates. For instance when the merge field has been a dollar amount drawn from the database, I have not been able to format the number as a dollar amount. I have had to place a typed "$" before the place where the merged field inserts the dollar amount into the lettter text and cannot even get Filemaker to place a comma into the number. So in the merged document they end up looking like this. $15000.

Now I face a new dilemma. On certain layouts, where Filemaker is placing a street number into a text layout merge field as part of an address (I keep street number and street name as separate fields), it is placing a $ in front of the street number. "$35 Madison Avenue" I have no idea how this is happeneing and have no access to formatting tools that might change this obviously undesired outcome.

So I cannot format merge fields in text boxes, but somehow Filemaker is doing it on its own??

The Filemaker help menu tells me that I should be able to format date and number merge fields, but the procedure does not work as described.

Any ideas? Ideas on how to turn plain numbers in merge fields into dollar amounts? Ideas on how to turn off this $ before street numbers? How do you access the elusive formatting tools and procedures for merge fields in text boxes?

[Genx]

Instead of having a seperate layout for each letter you might consider using the substitute function for your mail merges. Have a look at this sample file provided by vaughan:

mail merge demo

Doing it this way would also allow you to target specific sections of your letter and maybe apply custom formatting into your fields. Though you could also do it this way:

Hard Formatting Number Fields

Goodluck,

~Genx

[Genx]

Seeing as you have fm 7 and no dev though, instead of specifying a custom function, use this as a calc but define your number field as a variable using the "let" function first, this lets it be referenced through out the rest of the calc.

~Genx

[Lee Smith]

Not all numbers should be formated as NUMBER format, in fact, most should be Text. Your Street Number fields should actually be a Text Field.

Using a Merger field for each of the components can be cumbersome when it comest o formatting. I do not understand the reason for separating out the Street No from the Street, but I would have a tendency to use a Calculation Field for these two fields and Concatenate them before using that in the Merge.

HTH

Lee

[steve weinberg]

~Genx, thanks for the reply

But I am not quite at the user level to understand it completely.

For instance, while the street # field data appears in one text box layout with a $ in front of it, it does not appear formatted this way in the many other similar layouts. This is what befuddles me. Same field. Same data. Same type of layout (text box using merge field in the middle of text). Different results.

And no way to control or change the formatting.

I confess I do not know how to alter the definition of the number field as you suggest (I checked but did not see words similar to those you used as options) nor do I understand how that would change the field's formatting in this one case or if it would impact on the perfectly fine use of the data from this field (formatted as it is) in other similar layouts.

[Genx]

Ok, we'll do it the easy way as Lee suggested. Your street number, shouldn't actually be a number... It should be a text field. When you are in layout mode click on a field, then go into the format menu, and choose either text or number. Text will give you formatting options particular to the text. Number will let you put in $ signs, comma seperators etc. This is what I'll refer to as "soft formatting", it doesn't actually change the contents of your field, it only changes the way it appears on this particular layout in this particular text box.

However, what should be noted is that number formatting will work only on fields that have been defined as number fields. Only fields that will be used to display prices, costs, calculate things etc. should be defined as number fields, a street number, should actually be text seeing as it's not likely to be used in any sort of calculation like this, for averages or require $, formatting anywhere.

The way i'm assuming you've set this up is by dumping a whole heap of merge fields into one thingo (i forget what their called, so ill call them text boxes hehe). This is fine, you can choose to format your one text box, and all merge fields that refer to number fields will be appropriatly formatted.

Sorry for the rushed answer, I've got to head off to uni or i'm gonna be late.

~Genx

Edited March 1, 2006 by Guest
.. reasons of my own :p

[comment]

See if these two threads help:

 

 

[Inky Phil]

That second link of Comments is as concise and clear as it gets

Good Call

Phil