9 Replies Latest reply on May 11, 2010 12:31 PM by aammondd

    Creating New Records in a Related Table

    Woodzie

      Title

      Creating New Records in a Related Table

      Post

      I'm sure I'm missing something obvious here (as I'm new to FMP), but I'm at wits end...

       

      I'm working on a deployment data base to track the deployment of police officers in peacekeeping missions. I have three tables Personnel, Experience and Missions. The experience table is the join table that can host multiple records for each officer for multiple missions (e.g. three deployments to Darfur, one to Croatia and two to Somalia). The Missions table holds all the mission specific information (location, mandate etc.)

       

      I currently display some of the Experience information via a portal in a layout for the Personnel table. What I want to do is click a button to edit / create new experience records. Editing a record is no problem, using the "Go to Related Record" script. However, I run into trouble if there is no pre-existing record for that officer...as I can't seem to call up a blank experience record with the Officer's name (or universal ID # key field) already filled.

       

      I've tried the following solution, but may be barking up the wrong tree.

       

       

      If [ Experience::Universal ID # = Personnel Records::Universal ID # ]
      Go to Related Record [ From table: “Experience”; External; Using layout: “Edit Mission Record” (Experience) ]
      [ Show only related records; New window ]
      Else
      Perform Script [ “Add New Experience Record” ]
      End If

       Where "Add New Experience Record" is

      Commit Records/Requests
      Set Variable [ $UID; Value:Personnel Records::Universal ID # ]
      Go to Related Record [ From table: “Experience”; External; Using layout: “Edit Mission Record” (Experience) ]
      [ New window ]
      If [ Personnel Records::Universal ID #
      
      Experience::Universal ID # ]
      New Record/Request
      Set Field [ Experience::Universal ID #; $UID ]
      Go to Field [ missions::Mission Title ]
      End If
      May

       Of course, as soon as I request a new related record, there isn't one...

       

      I'm working with Filemaker Pro 11 on Windows XP.

       

      Thanks for any help.

       

      w.