Thursday, March 30, 2017

Filemaker hiding field object in Table View

There are times when we don't want to see some fields based on the values of another. In form view you can easily set it to "hide when" and set the condition to hide it.

However, in table view, the field is not hidden (up to V15). What are other means to hide the field?

I used a simple "conditional formatting" setting and set the text color to the background color. It is still shown at least not distinguishable to user.

It is a work around for Filemaker Table view which is unable to hide the object despite the "hide when" setting is set.

Thursday, March 16, 2017

Filemaker Custom Function

According to Steve Lane CTO of Soliant, Todd Geist said "he prefers to avoid custom functions whenever possible, preferring to use scripts instead". Steve himself said "everything you can do in a CF can be done in a script." Now  that is an anti-climax when the title above is about Custom Functions. My purpose here is to make sure you get the right perspective about CF (custom function) before even jumping into it.

Unlike most common understanding of a function, FMP custom function is severely limited in its feature. It is like a formula function. The only thing it could do is return a result of a calculation. It works exactly the same as FM functions. It is even less functional than what a Plugin can do.

Let starts with how to create a CF. First of all, you need to purchase Filemaker Pro Advanced. This means that not all users can do maintenance on CF regardless of their rights.

Open FM as normal. Go to File Menu,  Manage, Custom Functions. You will see the following popping up. If you have any CF in other FM, you can import the CF into the FM that you are working on.


Click on "New" the next popup will show.


Change the "New Function" to your function name.

Add any function parameter names as you wish. The line below it will show the structure of the function. You can swap the parameter orders. One thing to note is that the parameters' value cannot be skipped.

You can call the function with the function name and semicolon delimited parameters if you have more than one parameter.

Since CF are like formulae, the available list of FM functions for your CF are all available, Plugin functions are available if you have one or more. CF function is also available if you created more than one in current file.

What functions can be done? Well practically anything as long as it results in a value. You would probably want to do some formula that you used very often in the current or other FM files. Ideally, it should not refer to any fields and tables directly.

There is one very important thing you must remember. There is no loop or recursion. Loop is actually a step not a function. Well, it is not entirely that FM leaves you without some recursive means.  You can actually call the function itself and provide all the parameters that allows it to function. The important thing to remember is that you must have some means of breaking out of the loop otherwise it will loop 10000 times and return a "?". The maximum number of recursion is 10000 times. The following is an example of recursion.

Let([
nvalue = nvalue+15;
ncount = ncount + 1
];
case(ncount > 100; callme(nvalue, ncount); nvalue)
)

This example, coincidentally, is using a method called "tail recursion". If you need to call the function in the middle of calculation (like callme(nvalue, ncount)+somevalue) then you are limited to the FM stack size limit of 10000. The example has all the calculations contained in the parameters itself so FM don't have to remember the result of the function. It therefore allows you to do up to 50000 recursions. Not much, but at least you have 5 times more.

Notice that the example actually uses "LET" and "CASE".  "Let" allows you to performs a number of calculations and still return one value. "Case" can be used to return different values depending on the test result. It is therefore, a valid function with the use of the two. "IF" can also be used if your result is one or the other depending on the test condition. Just remember that "Case" and "If" must be assigned to a variable unless it is a formula by itself

In actual fact, "Let" has to be used in almost all CF except when your calculation is actually a formula itself like param1 + param2. Naturally, your need to create CF is not just that right?

One thing you must remember in using "Let". The very last item in the multiple assignment must not end with a ";" while other previous assignments must have ";". See the example above. If you forget you will get a mind boggling error message something like "cannot be assign to a list".

One more thing to remember is that if your formula ends with error, it will return a "?" as result. Only when it is a syntax error then it has a better response. The debugger in FMPA only steps through script steps.

There are plenty of example CF in the internet. Search for them and see what they do. You will get to know how to code a CF in no time.