AnsweredAssumed Answered

Value lists, drop-downs

Question asked by smashingly on Apr 29, 2010
Latest reply on May 1, 2010 by smashingly


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

Networking/sharing: no

FMP user level: medium


Database description:

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).