Value lists, drop-downs & pop-ups
Hi, I'll just start my post with some pre-lim information:
FMP version: Pro, version 10
OS: Mac OS X 10.6.3
FMP user level: medium
Table "Person" - purpose, to contain personnel records - fields such as name, D.O.B., passport number, widget serial number. Key=PersonID.
Table "Widget" - each Person can have a widget assigned to them. Widgets have a unique serial number plus a few other attributes. Foreign key field "PersonIDfk" stores the relationship between a widget and a person.
Table "Trip" - records of this table record details of a trip somewhere - departure date, trip participants, etc. A trip will relate to multiple Person records, so the relationship is many-many.
Table "TripJoin" - facilitates the many-to-many relationship between Person and Trip. PersonIDfk and TripIDfk values in this table store this information.
What does work the way I want it to:
Layout Trip - data source table Trip. When you create a new trip record you have a few fields to fill out, below which is a portal based onTripJoin. The portal rows are where you select who is coming on that trip by the pop-up field on the left hand side. This pop-up field is set up as follows: display data from TripJoin :: PersonIDfk; value list PersonFullName (which consists of Person :: PersonID and Person :: FullName, set to only display values from the second field - their full name). The net result is that you click the pop-up, select a person by name, and FMP sets the TripJoin :: PersonIDfk field to that Person :: PersonID. Now here is the crux bit: If I enter a bunch of names, and then decide to change one of them, I can do so - if I have a second window open showing the TripJoin table, I can see that row of the join table having its PersonIDfk value changed as I change trip participant #3 (for example) from Joe Bloggs to Peter Smith. That's great, that's the behaviour I expect...
What doesn't work and I don't know why:
Layout Personnel - data source is Person. Contains a tab with 5 fields relating to that person's assigned widget (if they have been assigned one - 80% of personnel do not get one assigned). The first of those 5 fields is Widget :: SerialNum, the remainder are other Widget fields. Filemaker Pro uses its 1:1 relationship with Widget to display this person's widget details, if they have been assigned one. That first serial number field is set up as follows: Display data from Widget :: SerialNum; type: pop-up; value list uses Widget :: SerialNum. If I scroll through some person records, I see Widget data appear for those who have a widget assigned. If I go into a Personnel record which already has a widget assigned, and try to pick a new one from the pop-up list, Filemaker Pro tries to overwrite the SerialNum value of that widget. Why is this behaviour different from when I change a trip participant in the first example? Why won't it just change the widget assigned to that person?
In a way I can understand why FMP does this but I don't understand why I *am* getting the desired behaviour with the Trip layout! I can see that by selecting a different Widget SerialNum value from the pop-up field in the Person layout, that Filemaker Pro interprets this as "please change that Widget's serial-number field to the value I have selected". But what I don't understand is why, in the Trip layout, I'm able to take an existing row in the portal (i.e a trip participant) and change that participant to someone else. Why can't I make this Widget assignment happen in a similar way? Is it because of the lack of join table with the widget relationship? Would it make sense to create a join table to do this (even though it's not a many to many relationship) ??
Help! PhilModJunk if you're reading this, you may recognise this as the database you helped me with so much last July/August (except that it is now 1 widget max per person instead of 2, making the design much simpler).