Concatenating Fields, Dealing With Blank Fields

[Packaging Guy]

Good morning. I am building my first database in FileMaker, and I've got most of what I want to do figured out. However, I do have one thing that I would like to have work better.

I have a portal in one of my layouts that shows Contact information, which is of course pulled from the Contacts table. Right now I'm using a merge field to show the following information formatted like this:

Firstname Lastname

Job Title, Company

However, I don't know the job titles for everyone in the Contacts list, so that field is sometimes blank. This means that the resulting merge field in the portal looks like this:

Firstname Lastname

, Company

Since I'm a perfectionist, this is completely unacceptable.

So, I guess my first question is, do I need to switch from a merge field to a calculation field to do this (my gut feeling is yes, which is why I'm putting this question in Calculation Engine)? My second question is, what would the syntax of the calculation need to be to say, "If a field in this calclulation has a value, put a comma and a space after it; if it is blank, don't put that comma and space there"?

Thanks in advance for your help.

[doughemi]

Job Title & if(isEmpty(Job Title); ""; ", ") & Company

[Packaging Guy]

Awesome, thanks! That worked perfectly.

Just to be clear, does the first semicolon act as a "then" statement, and the second semicolon as an "else" statement in this calculation? I'm still trying to wrap my head around some of the syntax that FileMaker uses in its calculations.

[comment]

You really should consult the help on each function in order to see what the parameters do.

http://www.filemaker.com/11help/html/func_ref3.33.11.html#1047177

[Packaging Guy]

Thanks, comment, that helped immensely. I've got calculation fields all over the place now, and it really helped solve a tricky problem with my contacts list (through a series of "If" statements, I now have my contacts properly related to retailers/products for each project so that relevant contacts appear automatically when a product and retailer is selected).

I have another question that I've done some digging into the FileMaker help for and just can't find: Is it possible to insert a tab character into a calculation field? If there's a symbol for that, I can't find it anywhere. Does this mean it's not possible to have text in a calculation field use the tab settings for a field to allow text to line up in columns? Or do I need to just set up three fields to do this?

Example: Table 1::Field 1 & (tab character) & Table 1::Field 2 & (tab character) & Table 2::Field1

EDIT TO ADD

Nevermind...I just answered my own question. I just copied and pasted a tab character from a merge field and it worked. It just seemed like something that should require a special character (like a page number).

[comment]

Before version 10, you had to copy a tab character somewhere and paste it in. Now it's much easier:

Table 1::Field 1 & Char ( 9 ) & Table 1::Field 2

[Packaging Guy]

Okay, so now it works both ways. That's good to know, thanks. Is there a reference in the FileMaker documentation for that (and other special characters)?

[comment]

I don't know of a section devoted to that. Of course, you have the ¶ character (and button) for carriage returns. Any other "special" characters, esp. non-printing characters, are best handled by the Char() function.