Hi this is Matt at AppWorks, and today we’re going to do a best practice video about field validation. So FileMaker has had the ability for many many years, in the schema, where you define your fields, there’s a tab, the second tab in, for validation. If we take a look at a couple of examples here, I can for example require that a company be a unique value so I can’t have two records in the database with the same company name, which seems reasonable, right? Why would you have two records for the same company?

A second example would be a credit limit where you have a certain number and it has to be in a certain range, so right now, you don’t have any customer with a credit limit beyond 50,000 dollars.

So this is why I think these are a bad idea. It basically comes down to the experience for the user. A lot of your databases, your users don’t have a ton of training, they just use the database and expect it to work like other systems out in the world that they encounter. If I try to look at a company record and type something to try and correct it, and say, you know AppWorks, and then tab, tab out if it. It says company is defined to contain a unique value. Allow this? What do I do? I can say yes or no. If I say no, it’s going to do something – I can’t even actually say no, so that’s really frustrating. I just can’t get out of it, right? So “revert” – the user very frequently doesn’t even know what that means. You can also even not even allow them to override it. So sometimes what you’ll find is you will force quit FM to get out of that particular dialog box because there’s so little control of what you can do.

These features – I know that probably not 100% of developers would agree with me. At AppWorks and with my long experience with FileMaker, I believe that all of the things on this tab are not really a best practice from the perspective of user interface and how you can do it.

That said, how can we do this? How can we make this work better for users? Here’s a couple of examples. If you have a field that you really want like the office phone number and you look at a record where it’s empty, you can use conditional formatting to color the field as yellow. So every time a user comes back to this record, they say, hey there’s this thing we really want. You don’t have it in there, it’s out of range. You can have a little message pop up that tells you you really need a value in there. Another thing you could do is, which I believe I put on the website field, is you can have a script that runs with a script trigger. On this one you can see the little star. On this one I can see there’s a script trigger called test value.

If I type in a website address, like, it will test my value to see if it works. It says hey, this needs to start with www – do you want to fix it or cancel? If I say fix, it corrects my value for me, assisting me and teaching me the correct way to use the database. That’s my favorite way to do it, because you get all of your control in FileMaker’s most powerful area, which is the script engine.

Here’s what that script would do. It first checks if it’s empty. If you’re deleting a website because it’s wrong, you certainly don’t want it to tell a user that it’s wrong because it has to start with www. Then if you have a value that a user does not put in www, it shows a custom dialog box and allows you to fix a particular value. The only thing about using script triggers is you have to make sure that all of the layouts that a user can interact with this particular field and change its data, that you call that same script trigger. I would probably say that most fields you should probably only allow to be edited in one place or a handful of places, but that’s definitely something you have to think about if you’re going to use a script trigger method. That’s it – thanks for your time.