How to validate data entry in second field?

[Jason Goldsmith]

I am trying to create what seems like a simple validation, but can't figure it out. I have two fields, both with value lists. When data is entered in field 1, then data must be entered into field 2. How do I create a validation to ensure that this happens?

Thank you!

[LaRetta]

Hi Jason,

Unfortunately, FM does not run scripts upon field exit (when exiting Field1, test whether it's empty and if not, place the cursor in Field2). And field validation (if applied to to Field2) only works once a User has entered the field and then attempted to leave it.

There are plugins such as ScriptIt which can activate a script upon field exit, so you could (using a plugin) attach a script to Field1 with a Go To Field [Field2]. Once that script is in place, assign a validation to Field2 (through Define Fields Validation) with Validation By Calculation not IsEmpty(Field1), uncheck 'Only If Field Has Been Modified', select Strict, Do Not Allow User Override and provide a message.

Alternatively, you can check the status of Field2 when performing some other task (via script), such as leaving the layout or changing to another record. In which case, you wouldn't need field validation on Field2.

LaRetta

[CobaltSky]

Hi Jason,

You don't need to use any plug-ins or scripts to do this. There is a relatively straightforward way to achieve it using a combination of 'Validated by calculation' options for both the fields (accessible from the validation tab of the Define Fields 'Options' dialog for each field).

In the Define Fields dialog for Field1, specify a validation calculation with the formula:

IsEmpty(Field1) or not IsEmpty(Field2)

Before you leave the calc dialog, disable the option at the lower left for 'validate only if field has been modified' click ok to exit the dialog, then activate the 'Strict' and 'Display custom message' options and enter something along the lines of:

If Field1 has a value, you must also enter a value for Field2!

into the custom message box.

Then for Field2, enter the same validation calculation formula - ie:

IsEmpty(Field1) or not IsEmpty(Field2)

Again disable the option for 'validate only if field has been modified' and again, activate the 'Strict' and 'Display custom message' options and enter the same message text as for Field1.

Once all the above is in place, users will not be able to leave the record if there is a value in Field1, unless they have also entered a value for Field2.

[LaRetta]

Wow Ray! That goes against everything I've read on all these forums for the past year and also against all of my tests and attempts to prove otherwise. Thanks for setting me straight.

LaRetta

[CobaltSky]

LaRetta said:...That goes against everything I've read...

Well, there are 'more things in heaven and earth' - as Hamlet remarked...

See attached example.

ValidationDemo.zip

[Lee Smith]

Auto-entered and imported data aren't validated.

Lee

[Anatoli]

IMHO Duplicated records are also not validated

[Lee Smith]

Wouldn't a true duplicate already have the information in the fields?

[LaRetta]

Hi Anatoli Hi Lee!

In this case, it was requested that, if a User types into one field, another not be left blank during the data-entry session on that record. Ray's solution will certainly stop a User from doing anything else until the second field contains a value.

Although imported records may contain data in Field1 and not Field2, that should be validated using a calc (and possibly corrected) via script. And duplicating a record (if Ray's validation is in place) simply won't contain records with data in Field1 and not Field2.

It has certainly opened my eyes to a thousand other possibilities (not only data validation as in this case) and my mind is swirling with ideas! It'll take me months to test all of them.

LaRetta

[Lee Smith]

Hi LaRetta,

My first quote was pulled right out of the Online Help. Validation

is not performed on import or with auto entered data.

Lee

[CobaltSky]

Anatoli said:

IMHO Duplicated records are also not validated

Well, not at the instant of duplication, perhaps, but their contents are - both before and afterwards.

Lee Smith said:

Validation is not performed on import or with auto entered data.

Rightly so, too. I don't think I'd want to deal with 10,000 validation dialogs during an import! The principle is that if the user explicitly enters data, then the user should be explicity alerted to a validity issue, otherwise not.

But no problem, as I think Jason was asking about a way to guide the data entry process.

[Lee Smith]

But no problem, as I think Jason was asking about a way to guide the data entry process.

and I have no doubt that your approach will do it for him.

I just wanted him, or anyone else, to know that there are ways that validations can be broken. If you know this ahead of time, than you can plan for it.

Lee

[Anatoli]

CobaltSky said:

Well, not at the instant of duplication, perhaps, but their contents are - both before and afterwards.

It was just general remark.

But afterwards -- only if someone will use that record for editing. If it is just duplicated, then nothing will happen.

In general sense, that is problem. In this case probably not, because the duplication will made copy of already validated record.

If there will be unique value required, that will be also not validated when duplication is made.

I guess it is always question "what, when and how" with validations.

[Jason Goldsmith]

Coincidently, I just came across a problem that requires the exact inverse solution:

I need users to fill field 1 before field 2, so I put:

IsEmpty(Field 2) or not IsEmpty(field 1)

in the validation calculation boxs of both fields, and it works perfectly!

This solution is really cool! Thanks Ray!!

I'll add that this 2nd solution is really important to me because as a filemaker beginner, I thought I had disasterouly painted my self into a corner. I have six sets, each containing five fields (a single patient can have up to five retinal diagnoses, five corneal diagnoses, etc). How was I going to automate the search of five fields for, say, all patients with a retinal detachment. My answer (perhaps not the most efficient) is to enter find mode and select the first find criteria (retinal detachment) in the first field, and then perform the following script:

Copy [select, "Field 1"}

Perform Find [Replace Found Set]

Enter Find Mode[]

Paste [select "Field 2"]

Perform Find [Extend Found Set]

Enter Find Mode[]

Paste [select "Field 3"]

Perform Find [Extend Found Set]

Enter Find Mode[]

Paste [select "Field 4"]

Perform Find [Extend Found Set]

Enter Find Mode[]

Paste [select "Field 5"]

Perform Find [Extend Found Set]

Go to Layout ["View found patients in Table]

Works great, unless the user, when entering data, skipped field 1 and put the diagnosis in field 2. Then, when I search using the above script, I get a dialog box stating something to the effect that no records meet the search criteria.

The solution: Validate that users use field 1 before entering data in field 2, and field 1 and 2 before 3, etc ...

I'm still curious though, how can I search multiple fields for the same value, without entering the value into each field?

Thanks again Ray!

[LaRetta]

Hi Jason,

Coincidentally, I just uploaded a demo using a concatenated calc combining several fields for searching for any text string (partial phone numbers or whatever) within it and then displaying the found set in a portal.

It's at Fusion DZine website in the downloads section called Search & Go. It might give you some ideas!

LaRetta

[CobaltSky]

Hello Jason,

A more efficient script to perform the multiple-field find would be:

Set Field ["gText", "Field 1"]

New Record/Request

Set Field ["Field 2", "gText"]

New Record/Request

Set Field ["Field 3", "gText"]

New Record/Request

Set Field ["Field 4", "gText"]

New Record/Request

Set Field ["Field 5", "gText"]

Perform Find [Replace Found Set]

Go to Layout ["View found patients in Table"]

It's not only shorter, but as it conducts the actual indexed search only once, it is likely to execute in considerably less time. It also has the advantages that it will not disturb the contents of the clipboard - and it doesn't require that the fields (except the Field 1, of course) be on the current layout. Note that it requires the addition of a global text field 'gText' as a temporary holding place for the search criterion, but that field need not appear on any layouts.

Alternatively, you could create a calculation field which concatenates the values of each of the five fields (each on a separate line) and then search only on that field. The formula for such a calc would be:

Field 1 & "

[Ugo DI LUCA]

CobaltSky said:

Alternatively, you could create a calculation field which concatenates the values of each of the five fields (each on a separate line) and then search only on that field. The formula for such a calc would be:

Field 1 & "

[Jason Goldsmith]

OK, I've taken your suggestions, and created two related files (see attached). The concepts of searching across multiple fields by searching a concatenated list (thanks Ray), and then viewing the result in a portal (thanks Ugo) is great!

I'm stuck at one point: In the script "Perform Search" in "MultiFindData.fp5", I can't figure out the last part of Ugo's script. If no records match the search criterion, then a message should return stating, " No records match your querry". I can't make this happen. Otherwise, this is a small miracle.

[Jason Goldsmith]

Sorry, apparently I need to get stuffit to create a zip file?

[Jason Goldsmith]

OK, hopefully this will include the files.

Multiple Field Find.zip

[Ugo DI LUCA]

Jason,

I've made some modifications to your file. I think (I'm not sure now) you had set your relationship reversed.

Also, I'd use a global field while you are using a text field, set with an auto-entered value of 1.

The 2 approached derived from my post are explained here, one with a simple portal linked to the Multikey, the other with a script triggering a go to related records, and a List View report.

Actually, you won't even need a second file, as you can play with self-relationship here, but you may build a find file if you want.

Tell if it's not clear enough.

Check the "search" layout in MultiFindData.fp5 and the second layout in the MultiFindPortal.fp5

HTH.

Multiple Field FindMod.zip

[Jason Goldsmith]

Thank Ugo! I have not yet had time to explore your changes. I'll do that this weekend.

Thanks again.

Jason

[Jason Goldsmith]

Performing a search by generating a relationship match, and then viewing the result in a portal has the advantage of being able to make the portal into a button that goes directly to a related record. But what if there are two fields that I am searching for? Can I search two or more fields and view the results in a portal? I've attached a file that illustrates the problem.

TwoFields.fp5.zip

[Ugo DI LUCA]

Jason,

You sure can do this....

If you want to search either for :

- Acute Retinal Necrosis

- Macular Degeneration

Shift-click each value from the drop-down menu, and those values would appear at left side as :

Acute Retinal Necrosis

Macular Degeneration

suitable for an "OR" find...