Creating New Records in a Related Table
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.