3 Replies Latest reply on Apr 26, 2011 10:37 AM by philmodjunk

    Require Record on Lookup



      Require Record on Lookup


      If I have field 1 from table A, looking up and matching to Field 1 on table, how do I cause entry into table A to REQUIRE a matching record be found in table?  Right now, if there is no match, it just allows entry.  I don't want a user to be able to enter into table A if there is no matching lookup in table B for the matched field.

        • 1. Re: Require Record on Lookup

          Please expand in more detail on what you want here and (as LaRetta advised in a different post) try not to refer to tables by generic letters. It's much easier to give good advice if we know the actual names and purposes assigned to each table involved.

          • 2. Re: Require Record on Lookup

            Ok, same question with names instead of variables.

            Table CLIENTS has fields FileNo, FileName, address, city, state, zip.

            Table CHECKS has fields FileNo, FileName, checkamt, checkdate.

            I have table CHECKS set so that when field FileNo is entered, it does a lookup from CLIENTS, and transfers the field contents from CLIENTS->FileName, into CHECKS, FileName.

            What I want is that IF the FileNo typed into CHECKS does not match to any record in CLIENTS, that CHECKS table does not allow the other fields to be completed in CHECKS.

            • 3. Re: Require Record on Lookup

              I probably wouldn't "transfer the field contents from CLIENTS->FileName, into CHECKS, FileName."

              Did you know you can just add the field from Clients to your checks layout? No need for two fields storing the same data unless the data in Clients::FileName may change and you don't want the file name shown in existing checks records to automatically change to show the new name.

              Easiest way to limit access is to set a validation rule on Checks::FileNo with this calculation:

              Not IsEmpty ( Clients::FileNo )

              This will catch the error at the time the record is comitted. If you use this same test in a script that performs OnObjectExt or OnObjectValidate, you can catch the error at the time it is made. OnObjectEnter triggers can perform a script that makes this check when the other fields are entered, but that shouldn't be needed if you use the check on the FileNo field.

              Another approach is to place a portal to Checks on your clients layout. With the right settings, the matching Fileno can then be auto entered each time you enter data into a new row of the portal--which eliminates the need for any error checking here.

              Hmmm, FileNo sounds like a field that stores a value that comes from somewhere external to FileMaker. If so, I'd change my relationship here to link with an auto-entered serial number defined in the Clients table. I'd keep the FileNo field here, but use it just as a data field defined in Clients.