Irritating Field Validated by calc!!

[LaRetta]

Ya'all helped me establish a validation on this numeric field (CGAS) which must be between 30-65; however, the bugger refuses to allow the User (currently me) to do anything else once accidently tabbed to ... in other words, it won't accept a blank once I land in the field -- irritating to say the least, as many times I dont' know this number for a few weeks. Validation calc is CGAS >= 30 and CGAS<= 65

How can I modify this formula to accept a 'blank', BUT if a number is entered, it must be within this range. I also have checked "strict do not allow user to override." I tried removing it from the tab order, but because there is so much on this screen, it's easy to accidently land there ... and THERE I AM! Help me out before I KILL IT!

[LaRetta]

** UPDATE ** I tried adding Case back; Case(CGAS >= 30 and CGAS<= 65,"") but it still insists on a valid number being entered! In fact, now it won't even let me go to layout mode or close the program using the Windows X ... hmmm, maybe I can learn from this ... if I ever DON'T want a User to exit until finished ... geeez! Even with a valid entry, it won't let me out! Oh, I fixed it good alright!

[CobaltSky]

In order to be able to leve the field blank, you will have to add another condition to the validation calculation. What you need is something along the lines of:

IsEmpty(CGAS) or CGAS >= 30 and CGAS<= 65

That adds an exta condition in which the field contents will pass validation - ie when the field is empty.

[LaRetta]

Thanks CobaltSky! Any way to get out of this, other than rebooting my computer? I tried [ESC] but it didn't work .. I even tried praying to the RAM Gods

[CobaltSky]

It's not letting you out even with a valid value because the Case function doesn't have a '1' as its result (you've set it up with a null result for all cases, and a null means invalid, so as it currently stands, it won't accept anything).

Try using the revert field button on the dialog. If that doesn't work, try using the 'Revert Record' command on the Record menu. One of thoes should get you out, so that you can go and edit the validation calculation formula .

[LaRetta]

Revert Record is greyed out ... and no Revert appears on the message box that comes up -- it just says "No way you're leavin' until you're done, idiot." Sooo, I take it it's a reboot time

[CobaltSky]

I'm not able to replicate that here. Based on what you've said, I've entered your case validation formula on a field, and have put scrict and custom messages on it, but I am still getting revert field and revert record options. I even tried it with a script paused and it is working.

Is the layout you are editing it on in the same database as the field, or are you editing it as a related record? Is there anything else you've done that you've not mentioned above? It would be a pity to have to do a reboot in case it trashes your current data (how long is it since the last backup??!).

[LaRetta]

Well, I rebooted -- however, a normal restart wouldn't work -- message box kept popping up!! So I had to power down. What a pain!! As for backing up ... that's one thing I do before I EVER make a change .. I (rightfully) don't trust anything I do! I'm afraid to go back into Clients. No, it was just my General Information screen -- go figure! I will lose (possibly) that clients data.

[LaRetta]

It said it wasn't closed properly ... no kidding. The current record is gone. I had also added an enrollment date so CPMS was created. A blank CPMS record is there with CPMSID but no CPMS# (auto-enter#). I think I'm pretty lucky. I'd better fix it before I accidently tab there again. Oh, yes, I LOVE formulas!!

Possibly the Revert Record didn't work because I was creating a new record. I have a copy of the pesky thing set aside, but I've corrected it in my backup copy of Clients.

[CobaltSky]

OK, now that you've given me some more information, I've been able to replicate it.

It has to do with two factors operating in combination. One is that I suspect that you have the option to 'Validate only if field has been modified' (at the bottom of the validation by calculation formula window) turned off.

The other factor is that you were creating a new record, which cannot be reverted (there being no previous 'version' of the record, there is nothing to revert to).

Once you correct the formula - ie replace it with "IsEmpty(CGAS) or CGAS >= 30 and CGAS<= 65", the situation should not occur again - not with that field, anyway

Notwithstanding that, you might want to enable the 'Validate only if field has been modified' - unless there is a reason to do otherwise. It is a 'failsafe' in a situation like the one you encountered!

[LaRetta]

Okay, everyone, I can't delete all of these posts. I'll just say that no one on this forum would have been dumb enough to do what I did ... and I can't even cover my tracks by deleting it So, I'll just suggest ... don't EVER do this!!!

[CobaltSky]

Providing you're using the "IsEmpty(CGAS) or CGAS >= 30 and CGAS<= 65" formula, you should get the effect you want (ie values outside of range rejected on first entry or on editing an existing value) even if the 'Validate only if field has been modified' is enabled.

Try it out and you'll see!

[Aussie John]

no way Laretta- that is hardly dumb. Anyway we can all learn from our mistakes. especially if they are yours

[LaRetta]

Hey Aussie John!! I LIKE that ... Well, there's enough posts generated on this one that EVERYONE will learn from my mistake!! Thanks for your understanding and support ... and particularly your HUMOR Yes, indeed, my role in life is to be an example of what NOT to do ... and I take that role very seriously

[DykstrL]

Don't feel bad, LaRetta - Anyone who has been doing serious development in FileMaker for a while has done similar (dumb?) things - even locking themselves completely out of a database forever - and if they deny it, they're liars! It's part of the learning process - we've all done it.

[semele]

help?!

have a calcualtion and i need to be able to modify the field..

why cant i do this?

[LaRetta]

Hi Dykstrl!

Well maybe everone's done it ... but I don't see too many of you developers creating 15 posts to point it out to everyone Thanks for the nice words, though!!

[LaRetta]

Hi Jgt!

I just noticed your post tacked onto the end of Irritating Calc. Let's try to bring it to the front so you can get your question answered!

I CAN tell you that you can't modify a calc by trying to enter its field. You must change the formula for the calculation through your File:Field Definitions or change data in a field which is represented by the calc. Please say more what your calc looks like, and the Forum will help you

[CobaltSky]

A calculation which is achieved using the Auto-Enter Calculated Result option on a standard data field can be edited (unlike a calculation field which by definition always returns the result of the calculation).

If you copy the formula from your existing calculation, then redefine the field as text (or number date, etc as appropriate) and enter the formula into the Auto-Enter by Calculation dialog instead (accessible by clicking on the 'Options...' button for the field, then going to the Auto-Enter tab), you will likely get the outcome you are looking for.