Help! Merge Fields!

[_Kimmie_]

Hi All and HAPPY NEW YEAR!

I have multiple merge fields I need to add to a document I created in FM. BUT not all of the merge fields will return text, nor should they. My problem is I am trying to create a calculation that will show the merge fields with text and add a comma and proper spacing if there are more than one.

I found this on another Filemaker site:

Example:

Create a database with the following fields:

Address (Text)

Suite (Text)

City (Text)

State (Text)

Zip (Text)

AddressSpace (Calculation,Text) = If(IsEmpty(Address) and IsEmpty(Suite), "", " ")

CSZCommaSpace (Calculation,Text) = If(IsEmpty(City), "",^", ")

CSZSpace (Calculation,Text) = If(IsEmpty(City) and IsEmpty(State), "", " ")

Then insert the Merge fields in to the text object to look like this:

<

><><>

<><><><><>

Enter any data you want. The space will appear between Address and Suite only if both values are not empty. The comma will appear after the City if City is not empty and the space will appear before the Zip as long as both City and State are not empty.

I tried using the commaspace calculation above but get an error highlighting the carat(^) saying that "a number, text constant, field name, or "(" is expected here"

I am obviously missing something but I don't know what?

Here is how I am trying to list certain products:

Division 1: <> <>, etc. (up to 10 products per Division)

Can anyone help?

Thanks!

[LaRetta]

Hi Kim,

Remove the ^ from the CSVCommaSpace calc. Also, there are other errors (I believe copy/paste translation) such as <>[color:red]<&l t;Zip>>. The red is invalid merge field format.

To produce a calculation with a string of your product fields, try:

Substitute ( List ( Product1 ; Product2 ; Product3 ; Product4 ) ; ¶ ; ", " )

However, what happens if you ever get an 11th product? You would need to add additional fields (Product11 and [color:blue]* mergeProduct11) to your table and so forth. It might be worth the investment now to have your products as records in a related table. If you wish to consider an alternative method, let us know (and it would help to see a sample file showing your structure and basic information).

UPDATE: [color:blue]* I have no idea why you even need a MergeProduct1-10 fields/calculations.

Edited January 6, 2010 by Guest
Added update

[LaRetta]

Hmmmm, it seems we've discussed using a related table few weeks back and I believe you switched to using related products so I might be misunderstanding what Product1 and Product2 etc even mean. But I suspect you still have some issues with your structure.

[_Kimmie_]

Hi LaRetta,

The merge fields was an awful idea but I was desperate! LOL Ok I have rearranged my thinking and added my products to another linked database I use for sales. When I go to a company record, I can put the year of the sale which will allow me to choose from a dropdown list from another database that just lists the items for that year. I added my products to that database figuring since I am linked it would be the best way to do it. So I set up my fields in a new table in my main database and I am running into a problem. I can't get JUST the products to show, it lists EVERYTHING. When I try to tweak the value list to show just the second field, it only shows me 11 records. I keep changing the relationship and value lists but I can't seem to get it right. I know this is difficult without showing you the database but I unfortunately can't post it. Let me try to break it down to better understand:

I have a MAIN database that is linked to a SALES database. The SALES database holds the sales ID, year, description of sale, pricing, etc. I added two more fields, division and product since these are the only two things I need to track. I added a new table in my MAIN database that's called products. I joined the products table (MAIN) to my contact table (MAIN), SALES database (sales ID to product ID in my products table), and I created a second relationship table called SALES 2 to link the division field to the division field in my MAIN db. Now I formatted my fields in the MAIN db to "lookup" in the SALES db as well as created the value lists. The value list works fine when I click on the correct product, it then populates the division number and product name as I need. I want to list ONLY the products and NOT the ENTIRE database which it's doing now. If I change the value list to say show only the second field value, it only shows me 11 records, if I take that tick off, it shows me everything. This information is permanent not driven by a year. LOST :)

[_Kimmie_]

WOW!! I closed FM and re-opened and it all works!! ODD?!? THANK YOU LARETTA!! :)

[LaRetta]

I'm glad you are happy but I remain quite concerned about how you've structured it (even after you explained what you've done). I can understand not wanting to attach your file but you could create a few small tables within one file which shows just the three tables we are discussing and how you currently have them joined (key to key) and only the fields which are critical for understanding the structure. Spending 30 minutes doing this will save you hundreds of hours down the road.

At minimum, give me a copy of your graph (which shows these tables we are discussing and shows them expanded so I can see the fields involved). I simply can't envision what you have without more information. If you wish to proceed as you are, that's fine too but I feel that I must caution you that you probably still will be required to unravel your knitting and re-do some (or most) of it once you hit another dead-end. I'd like to steer you towards fluid, simple structure where we can. :wink2:

Edited January 6, 2010 by Guest
Added sentence