Log in
Hot & Trending on FMT

Double-Check those Calculation Result Types

Double-Check those Calculation Result Types Double-Check those Calculation Result Types

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 plenty of 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.

12ti Studios

12ti Studios was founded to bring exceptional software design and experience together with a venture capital-like approach to helping small businesses, and businesses-to-be, create innovative software products.

It all started when people came to founder Chad Novotny with ideas. During his career as Vice President of Technology for a custom software development company, he had been approached by many people, each with an idea for a product or service. In return for equity, they would ask, could he build their ideas for no charge? Unfortunately, he had to say no; that wasn’t the business his firm was in. But Chad always wondered what could have happened had he been able to say yes.

When Chad left his previous position and joined The Venture Capital Fund of New England as Entrepreneur-in-Residence, (after having personally been involved in several VC investment opportunities and “gotten the bug”), he decided to also launch 12ti Studios. The mission is to provide not just software development, but to partner with and assist in the development of businesses that traditionally would be unable to take advantage of venture capital.

12ti continues the tradition of Chad’s contributing to the FileMaker community — experienced developers and beginners alike — by sharing techniques, tips, and thoughts through its blog. We welcome your feedback and ideas.

To find many more in depth articles from Chad and some must have tools and services from 12ti Studios visit their site at the link below:

Website: 12ti.com