A reader asks:
I created a full name field by calculating First & " " & Last. All are text. It works on the layouts. It works when printing to PDF and preview. I actually see the full name.
I then export the fields to a basic file. All the fields all are what they should be, except for the full name field. It is “0” in every single instance. Why does it not produce text instead of 0?
This is one of those things that can be maddening to figure out it you don’t know exactly what you’re looking for, but it’s actually a simple fix. In all likelihood, the problem is that, while the First and Last fields are text, the calculation field has been defined to return a number. You need to change it to text.
Technically, you can have text in a number field. Try it: define a plain ol’ number field (without any validation options) and enter the name “Huck Finn”. It looks like it’s still working. It will display the name the same as a text field. It’ll print on paper or PDF the same way a text field will. But the name “Huck Finn” isn’t a number. If you do a search for “Huck” in that field, you won’t find that record. And when you go to export it, you get the number 0. The same is true of a calculation that creates a text string but results in a number.
When you create a calculation field, by default FileMaker assumes you want a numeric result (if you’re creating a number of calculations one after the other, each new calculation field will default to the result type of the last defined field). So it’s really easy to accidentally define a full name field to result in a number (I’ve done it plentyof times). Then, once you have finished defining the calculation expression, you don’t see that option anywhere again unless you go back to edit it. There are a few places where you might notice it: field formatting options in Layout mode will let you display it as currency, for example, and the design function FieldType will probably tell you it’s a StoredCalc Number Indexed 1. It would be nice if, in the full list of fields in Manage Database, FileMaker indicated it was a Calculation (Number) instead of just Calculation.
So this is almost certainly what happened. A good practice to develop is to always specify the calculation result type before you start worrying about the actual formula. But we’re all human and forget, so when you see weird things happen (like sorts not working properly, relationships not matching up when you know they should, a question mark appearing in a field instead of a text string, or exports displaying the “wrong” result), go check the result type.
