Validate By Calculation

[BeckyMN]

I'm embarrased to ask this because I asked a similar question before but I have such a hard time understanding how validation by calculation works.

I have 2 fields.

Field A is a number field.

Field B is a text field.

What I want to do is require the user to add a note--enter some text into Field B--if Field A contains any number. This is so common in other databases that I have encountered yet I just can't seem to figure out how to make this calculation work. I just don't seem to understand how to think about the problem.

The note at the bottom of the define calculation says that it must be boolean which I think means 0 or 1 or true or false. But what does that mean to me and how do I think of it in terms of what I am trying to do?

Can somebody explain this in plain english for me? I would love to get past this learning hump. I feel like such a dope.

[comment]

Plain English? I'll try. First, you describe a situation where there are TWO valid options:

EITHER:

FieldA is empty;

OR:

FieldA has a value AND FieldB has a value.

Translated to Filemakerese:

IsEmpty ( FieldA )

or

not IsEmpty ( FieldA ) and not IsEmpty ( FieldB )

This could be simplified a bit, but let's look instead at the opposite side - what is NOT a valid option, namely:

FieldA has a value AND FieldB is empty.

Translated:

not IsEmpty ( FieldA ) and IsEmpty ( FieldB )

This is simpler, because there's only one state to specify. But this is an invalid state, so we need to inverse it by "not":

not ( not IsEmpty ( FieldA ) and IsEmpty ( FieldB ) )

Now, since FieldA is a number, we can simplify this to:

not ( FieldA and IsEmpty ( FieldB ) )

[Raybaudi]

great !

[comment]

Oops, this trips me every time: the last simplification assumes zero is not a valid entry into FieldA. IOW, if FieldA contains 0, it is considered as empty by the validation calc, and no error will be flagged if there's no accompanying note in FieldB.

[Conartist]

:qwery:

I'm glad you asked this question! So I'm going to try to explain it in "plain english" to see if I understand. Please correct me if I'm wrong.

With a validation by calculation field the boolean part means that we are defining the TRUE condition (or the result = 1). The calculation will be used to test the data to see if it creates a TRUE condition based on the calculation. If a FALSE or 0 is the result than the data will not pass validation.

Which field would you put the validation on? Field A makes sense, but would there be a reason to attach the validation to Field B as well? The validation calculation will only be triggered when the field it is attached to is modified. right?

How would you trigger a custom error message in this situation?

Great example Comment!

[comment]

The validation calculation will only be triggered when the field it is attached to is modified. right?

Not really. That's what the checkbox "Validate only if field has been modified" is for. Uncheck it, and the field will be validated at record commitment.

In fact, this box MUST be unchecked even if the validation is on FieldA. Otherwise, one could delete the note in FieldB, and since FieldA was not modified, the validation would not trigger. With the box unchecked, it doesn't really matter which field is validated - it could be even a third field.

Validate.fp7.zip

[BeckyMN]

Thank you Comment! Your calculation works perfectly and I think I understand how you arrived at the shortened version.

Sincerely,

Becky