3 Replies Latest reply on Dec 27, 2011 10:40 AM by philmodjunk

    Insert or search a related record



      Insert or search a related record


      Hi, I'm a FMPRO 11 newbie and I need your help for a probably easy task:

      I have some tables:

      person(CODE, name, surname, ...)

      terapy(ID, name, days, ...) autoincremented id

      request(ID, Person, Terapy, date, ....) with Person referencing person::CODE and Terapy referencing terapy::ID

      I created a layout for my requests that has autoincremented id and I added the fields for my 'person' table too. When I use the layout in 'use mode' I can enter new persons easily but I cannot search (= 'reause') an already created person since when I insert an already inserted person::CODE in the form it violetes the primary key requirements (unicity).

      How can I make a single form for 'person' in which when I write a new CODE it creates a new person and when I write an existing one it just displays all the other fields?

        • 1. Re: Insert or search a related record

          You appear to have these relationships (Correct me if I am wrong...):


          Person::Code = Request::Person
          Terapy::ID = Request::Terapy

          Why do you want to enter a person's code at all? Wouldn't it be simpler to select their name from a drop down list with a "new person" button to use if they do not appear in the drop down list? A drop down list can put the Code from Person in field 1 and their name in Field 2 so that you are selecting a person's name in the drop down list, but the field stores the Code of the person selected.

          (And if "Code" is not an auto-entered serial number, you should add another field to use as the primary key for Person and keep code as a data field in the Person table for searches and sorts.

          • 2. Re: Insert or search a related record

            Hi PhilModJunk,

            the relationships are correct : )

            Person::CODE is already a unique identifier that is given to each citizen in Italy, a drop down list will be problematic since there will be lots of name/surnames equal couples; whereas a dropdown list of CODEs will be pointless to search. The point is not to create a new person with the button but to retrieve an already created one if present.

            I've seen I can do this at the moment: use the Search mode to find another CODE if present and duplicate the record for the request. But I'd like to do all that in USE mode

            • 3. Re: Insert or search a related record

              Person::CODE is already a unique identifier that is given to each citizen in Italy

              We have Social Security Numbers here in the United States that uniquely identify each citizen. Stilil, I would not use such a value for your Primary Key. While it is unique, it comes from an external source and thus it is safer to use your own internally generated ID number. Imagine what might happen if you enter the wrong code for a given person, create some related records and then discover the error... Also, a person might attempt fraud by entering a false code, finding and correcting such issues after the fact can be difficult to do without losing your links to records in other tables. All of this can be avoided if you use an internally generated serial number as the primary key.

              You can keep Code as a data field in Person so that you can search for a person (and their related records) by using their code.

              Here's a scripted approach that would allow you to enter a person's code and it will either create a new record in Person (if not found) or link your new Request record to an existing Person record (if found). I will not be using the Code field except as a look up value in this example--relying on the interally generated serial number instead.

              Define a field gSearchCode and give it global storage. It can be define in any table in your database or in a separate table of Global fields. I'll put it in Requests for this example.

              This script can then be performed either when you exit this field or when you click a button placed next to it:

              Freeze Window
              Go to Layout [Person]
              Enter Find Mode [] //clear the pause check box
              Set Field [Person::Code ; Request::gSearchCode ]
              Set Error Capture [on]
              Perform Find []
              If [Get ( FoundCount ) = 1  // Person record was found ]
                Set Variable [$ID; value: Person::PersonID ]
              Else IF [ Not Get ( FoundCOunt ) // no person record was found ]
                New Record/Request
                Set Variable [$ID; value: Person::PersonID ]
                Show Custom Dialog ["ERROR, two or more person records with the same code were found"]
                Halt Script
              End If
              Go To layout [Requests]
              New Record/Request
              Set Field [Requests::PersonID ; $ID ]