Bizarre Auto-Enter Calculation Behavior for Numeric Global Fields Where Field Validation Calcs are Also Used

Heads-Up!
,
1

Hi to everyone,

I’ve been trying to figure out some really bizarre behavior for a set of global fields I’ve set up for purposes of taking user inputs that are then translated into parameters for eSQL functions and Execute Data API script steps.

The global number fields in question are only ever intended to take quantitative values. But text that inadvertently makes it into these query parameters will mess up my eSQL results, so I set up an Auto-Enter Calculation to coerce any entry to a number. The Auto-Enter calculation is simply GetAsNumber (self) and “Do Not Replace Existing Value (if any)” is turned off.

If this was all I did for field options, there would be no problem, but I also had a field validation calculation. It really doesn’t matter what the calculation is, but the whenever there is this combination of both the auto-enter calculation and a field validation calculation, the latter will determine the auto-enter value in certain circumstances and will not use the value determined by the actual auto-enter calculation.

I’ve attached a copy of the file so you can see for yourself.

In the file “NumericGlobalField1” is defined with the aforementioned GetAsNumber (Self) function for the Auto-Enter calc, and has a Field Validation Calculation of 2+2 (and again, this could be any value).

The weird behavior is this: if you enter (any) value into NumericGlobalField1 AND DO NOT YET COMMIT THE RECORD and then go to NumericGlobalField2 but DO NOT ENTER ANYTHING and keep everything uncommitted, and you then go back to NumericGlobalField1 and change the value to something else, it will change the entered value of NumericGlobalField1 to the Field Validation Calculation of 2+2. It looks as if there is a bug where FileMaker conflates the value of the Auto-Enter calc versus the Field Validation calc.

Has anyone else encountered anything like this? Fortunately I can work around this (after all, it’s FileMaker!), but had I not checked this carefully, it could have resulted in a lot of bizarre query results.

Anyway, apologies for the long post. Best wishes to everyone for a happy, healthy and prosperous 2025.

Peter Gerlings

Temp.fmp12 (228 KB)

1 Like
2

Running V 19.6.3.302:

  1. I type 24 in Global Field 1
  2. I bring the focus to Global Field 2 either using the mouse or Tab key
  3. Bring back the focus to Global Field 1, and the contents is not changed

Note that the calculation for the validation returns a boolean, and in FileMaker, 0 is false and any other numerical value is true. What do you mean by

In my test I have 24 in Global Field 1 which is true.

Two questions for you:

  1. Contents of Global Field 1 changes to what value ?
  2. What version are you running ?
3

There's a step 4: after going back into field1, change the entry and then move back out. At this point it will revert to the calculation validation result.

I'm using 21.1 on macOS 15.2. Same result local hosted or remote. Also the same in Windows 11 23h2.

Thanks for the reply!

4

Sorry I don't get that result.

Can you make a movie on a computer where the issue is occuring ? If so please have the image large enough so I can see everything on the toolbar.

Thanks

1 Like
5

After a few more hours down this rabbit hole, I think I've pinpointed the problem, and it's almost certainly limited to 21.1. I was mistaken in my original post about it only affecting numeric global fields; unfortunately it seems to impact all global field types. Non-global fields are not affected so far as I have seen.

All types of global fields in files using 21.1 that have BOTH auto-enter calcs enabled, as well as a field validation calc set up, will have the value in the field validation calc used for the Auto-enter value

I have made a short mp4 video to answer planteg's questions and highlight all of this (complete with toolbars), but I can't figure out how to post the video!!! It's around 30mb (already compressed). Can I DM it to you? hmmmm

AutoEnterStrangeness.fmp12 (224 KB)

6

You can simply drag a file on the text you are writing. But I guess the software we use may have a size limit to dragged file.

One way would be to host the file on Google Drive for example, and add to a post a link to that file. You need to set permissions on the file so everyone can download it.

Lets tag our Community Admin: @Bobino, is a 30 M file too large to be dragged to a post ?

7

There is a file size limit for uploads, and you should be getting an error message that explicitly mentions that when attempting an upload that is over the limit. A 30 MB file will exceed the limit. Best practice for video or large files calls for sharing a public URL where the resource can be downloaded.

8

Hi - sorry for the delayed reply and thanks for your follow up. I've managed to spend a few more hours going down this rabbit hole, and put together this latest video trying to demonstrate the problem:

Video showing auto-enter/field validation calculation issues with global fields in FMP 21.1

I've also updated the file I've been using as a testbed.

AutoEnterStrangeness.fmp12 (240 KB)

As I indicated earlier, this appears limited to the latest release of FMP (21.1) and is at least coincident with the new data validation custom message feature. I was wrong to suggest it's limited to only numeric global fields (you get the same problem no matter what data type); it does impact all global field types, number, text and otherwise. However, I was correct in that it's limited to global fields as regular fields work just fine.

It also appears limited to situations where both auto-enter and field validation calcs are used, and only when data validation is set to "always" with user-override disabled.

Where it gets really strange is that the choice of auto-enter calculations matters, too. Coercion functions like GetAsNumber ( Self ) trigger the bug, where algebraic functions like 4+5 do not trigger the bug.

I feel like I stumbled into a pretty rare use case for when this would manifest itself, but had I not figured this out it would have caused a real issue for my scripted finds and sql queries, given these unintended changes.

Weirdly, it doesn't matter whether or not you use the new custom data validation error message feature, but I have to believe that it's not just a coincidence that this showed up. I'm now going through other apps I've developed to see if I have similar instances where both field validation calcs and auto-enter calcs are set up, because I'm worried that upgrading to 21.1 will have exposed a bug that wasn't previously a problem.

Ah, FileMaker. I swear the rabbit holes are half the fun. When I have time, that is. Thanks to anyone who took the time to read this, and of course to Giles and Bobino for their replies (so far).

Happy New Year to All!

2 Likes
9

If you don't have too many of these (and even if you do??) then you might want to script this instead of relying on FM Autoenter and Validation. Matt Navarre has a blog post and video about how "Field Validation is Broken" and I think he makes some very good points - see here: https://www.navarre.training/post/field-validation-is-broken

You could add a script trigger to your global fields to set the global value into a variable, check for invalid entry, clean up the variable (aka GetAsNumber) and then reset the Global to the "acceptable value"- or better yet, use the Variable instead of the global field for whatever process you are using. Scripting allows you to give the user a precise description of any error and what they should do next. You can also easily log the error for your own use.

10

Thanks for the reply and link to Matt's article.

This is something I used to accomplish via script triggers. All other things being equal, I would prefer to have data validation embedded in the schema and not dependent on the interface, but FM's validation has needed attention for a while. Since it's FileMaker, I have some workarounds I can rely on.

I think the new custom data validation error message capability is meant to address some of the user experience issues Matt raises in his article, but clearly this feature isn't fully ready yet.

11

I'm able to replicate this using your Temp file in FM21.1 but not FM20.

FM20.3.2.201

Put cursor in field one, type "1234"

image
image530×474 20.2 KB

Tab to second field. Result is as expected:

image
image570×208 6.5 KB

FM 21.1.1.41
Same steps, different result:

Put cursor in field one, type "1234"

image
image568×264 10.5 KB

Tab to second field. Result is wrong:

image
image548×226 5.92 KB

The resultant value of "4" just happens to be what you have in the field validation formula:

image
image720×180 10.7 KB

Seems like a bug to me, in that the calculation engine is somehow using the validation calculation (2+2) in place of the auto-enter calculation (GetAsNumber(Self))

Edit to add: tested on macOS 15.2 with an M4 MacBook pro.
For FM20 I do have MBS Plugins installed, but for FM21 I have zero plugins.

Edit 2: also, I can not reproduce the bug in the second file you uploaded (the one named AutoEnterStrangeness.fmp12 Perhaps you have some file mixup?

12

I see another issue, which I believe you pointed out in your video, which is that the new Custom Validation message feature in FM21 which allows a formula, the formula is the wrong type:

image
image1228×966 93.3 KB

It's not logical that a formula to display a custom message should be a Number - it should be Text, right?

13

Dear Xochi,

The reason you didn't see the same result in the second file is that I forgot to change back the validation options to "always", and having user override turned off. I will upload the correct version here, as well as the first version on the FM site, so that people can see what you're referencing. Thanks for taking the time to respond and confirm that it's not just me.

1 Like
14

FileMaker has acknowledged the bug and is working on a fix. Thanks very much for helping move that thread along over on the FM website.

I'm avoiding using both field validations calculations and auto-enter calculations for global fields at the same time until it's officially safe to do so.

3 Likes
15

@Dutchman and @xochi : Thank you both for your work to get Claris to see and acknowledge the bug.

16

Not to discount the reported bug (although I wonder if execution order is in play here) ....... this truly needs to be investigated and resolved.

FWIIW I avoid field validation from the field options dialog. I detest "nagware" where a standard dialog pops up..... bad UX IMHO

And field validation is obscure code, hidden from common developer patterns. Always better in a visible, maintainable script.

Field validation for me always ends up in an OnCommit script trigger (or a field level trigger if the content has further entry dependencies) scripted action or a script controlled navigational exit from the layout, where multiple fields can be checked, and error messages can be verbose and descriptive, sometimes even offering the UI elements to be presented in the error window to be resolved.

OK, so it is more work, but users - from my experience in this scenario - learn quickly and input errors become less prevalent.

17

Dear Kirk,

Thanks for the reply and for the record, I hardly ever use field validation for all the reasons you list. I will say that the new custom message dialogue option makes it a lot less bad than before. This whole adventure into FM's field validation was the result of me wanting to trap any SQL injection attempts (purely a proof a concept as the risk of someone maliciously accessing the file is close to zero). Previously I would have done this via field level script triggers, but here I tried to trap for some specific pattern count results, and then update a global variable I have set up to track errors that occur in my scripts and functions, finally triggering the new custom message.

I hear you about it obscuring the code, but I try and make up for this in my field comments box.

Anyway, all this has been fascinating and ultimately solvable.

Best wishes for a fine day.

Peter

PS -here is a screenshot of the end result of all this:

Snip20250115_3
Snip20250115_31962×1023 160 KB