Transcription

Hi this is Matt at AppWorks and today I’m going to be talking about a field validation issue, in a default behavior in FileMaker Pro 17 and 18 that can sometimes be a problem for your users. Specifically, here’s what I’m talking about – in the default setting for the primary key, there’s two checkboxes checked – not empty and unique value. On the four fields for creation and modification, there’s one box checked that’s not empty, and it requires that those values be present, otherwise you cannot work with a record in the table.

Here’s how it would work normally – if you go in and make a record, I see a Primary Key gets generated, and I get creation and modification information, and I can go in and type whatever I want, like, whatever kind of data that I want in the thing. Also I can create related data. I can put the phone number in, and everything works fine. The problem comes in if you import data, so if I import something and I’ve got a little sample file here, in the sample file, I have primary key data, so I’m going to uncheck this box that’s normally checked by default in order to do auto-enter options, because I’m going to use the primary key that I have, and not have FileMaker enter a primary key. That’s because I want to use my primary key to relate to other data that I’m going to be entering later, which is a very common thing to do. That means that the creation timestamp info does not get set, because I don’t have that imported from my source. I’ll point out briefly that in FileMaker Pro 18, in the import dialog box, this problem is actually solved. There’s a way to do that here, which we’re not going to talk about – it actually does have that feature, but it’s more detailed. 

If I import my data, my 3 records come in, and I get no errors skipped because of any kind of a problem, but if I wanted to work with this first record and get any sort of a change, like change Mr. to Professor, FileMaker will not let me do it, because created by is defined to require a value. No problem, I can go to created by and put a value in, right? No, because it’s also not a modifiable field. The default behavior creates a problem for the user that the user can’t get out of except by force-quitting FileMaker, or by knowing that they can revert the record, in which case they still can’t work with the record.

Here’s my recommendation for how to fix this. If you go back to manage database, and you click on your primary field, uncheck these two. Also, uncheck this box for not empty on all four of these fields. And, then let’s see if that makes a difference. So now if I go in, it no longer requires that created by has a value, so I’ll change this from Mr. to Professor, and it’ll let me make the change. So it puts my name as the modifier and it doesn’t have a creator and will never have a creator, so that’s one bit of data that I’m not going to know, but when you import data, that’s what you get. Sometimes there are bits of data that you just don’t know. I’ll also be able to make related records for this one. 

The second thing that can happen, if I go back to Company, is this record has no primary key, so even though I imported it and thought that there was a key in my import file, there actually is not a key, so a missing primary key will actually prevent me from making related data. So if I try to put a phone number in here, it’s going to tell me I can’t, because there is no primary key. There’s not really a fix for this, I will point out, when I imported this data, it didn’t give me an error that there was no primary key, it just didn’t work when I started working with the data.

One way around this is to duplicate the record – go to record and say duplicate. That will make a new record with all the same data, but it assigns a primary key. I can go to my original record and just delete it – that’s what I do if some subset doesn’t have a key – you can add it after the fact. Then, on my duplicate copy of the record, I can do things like add a phone number. In summary, in the fields tab in manage database, or when making a brand-new table, the checkbox under the validation for not empty and unique really don’t serve much purpose, and I recommend that you just uncheck them for most situations. Thanks for your time.