4 Replies Latest reply on May 1, 2010 9:37 AM by smashingly

    Value lists, drop-downs



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


        • 1. Re: Value lists, drop-downs & pop-ups

          Yep, I remember that project somewhat. I see our "radioes" have transformed into "widgets" :smileywink:


          I also remember that this system is pretty complex with a lot of table occurences and join tables. I can only guess the obvious: that there is some detail different between the whay you've related your table occurrences that explains this difference. I can't guess more than that without being able to examine all the relationships/Table Occurrences involved.

          • 2. Re: Value lists, drop-downs & pop-ups

            Hi Phil!


            Yes, I used the term 'widgets' this time, I felt it was clearer to use a more abstract term.  


            I tried to isolate my description of the database to just the relevant tables, and also the structure has simplified a little.  For example, the client is now happy for only 1 widget max per person, so I've been able to simplify the relationship between Person and Widget (i.e. no join table required).


            The example I gave of desired behaviour (Trip, TripJoin) is actually quite simple - I'll try to draw it in text, using a < or > character to represent 'many' relationships:




            And the Widget relationship is:



            We did create some other table occurrences for Hotel & Flight requests but they're not part of this discussion and don't impact on it - the desired behaviour I am seeing with Trip participants is strictly between Trip, TripJoin and Person.  That said, if you want me to send you the relationship graph then I can do so, but I don't think the other tables would have any bearing on this issue.


            So I'm still puzzled as to why I can chop and change trip participants without issues, but cannot do the same with Person-to-Widget assignments. I have actually been wondering if I should maybe change the way I relate Person to Widget - create a WidgetID field and store that as a foreign key in the Person table, rather than the other way around (currently, the Widget table has a PersonIDfk field).  Thoughts?



            • 3. Re: Value lists, drop-downs & pop-ups

              It's coming back to me. Here's my best guess without physically examing your layout:


              You appear to have this relationship:


              Person::WidgetSerialID = Widget::WidgetSerialID


              It sounds like the field on your layout is Widget::WidgetSerialID instead of Person::WidgetSerialID. That would at least match the behavior you are seeing.

              • 4. Re: Value lists, drop-downs & pop-ups

                PhilModJunk wrote:

                It's coming back to me. Here's my best guess without physically examing your layout:


                You appear to have this relationship:


                Person::WidgetSerialID = Widget::WidgetSerialID


                It sounds like the field on your layout is Widget::WidgetSerialID instead of Person::WidgetSerialID. That would at least match the behavior you are seeing.

                Actually the relationship is Person:: PersonID = Widget:: PersonID  ... and the field was set to show Widget::WidgetSerialID.   But I've set it up now as you've suggested - changed the relationship to be Person::WidgetID = Widget::WidgetID, and set the drop-down field on the Person layout to show Person:WidgetIDfk and get its value list from Widget:WidgetID & Widget:SerialNum, showing only the 2nd value for user-friendliness.  I thought I had fixed it, it seemed to be working, but that was only because when I switched around the relationship like you suggested, this meant there were no stored assignments of widgets to people - all Person::WidgetIDfk fields were empty.  So I could sit there picking whatever widget I wanted, for this one person, and got no error message.
                What happens now is the same thing as before - I pick a different widget for a person, and FMP thinks I'm trying to assign that WidgetID value to more than one Person, so uniqueness validation fails.
                But all is not lost.  I went for a walk on the beach (always good for solving database conundrums!) and realised that the user will not want to assign widgets this way anyway - flicking through 130+ personnel records to modify a field on the Person layout makes no sense - it makes more sense to have a separate layout based on Widget , displaying all widget records in a row (using report layout mode) and allowing each widget to be reassigned using a drop-down or pop-up, including an "Unassign" button to clear the assignment.  In a way this closely models the "radio locker" thing we discussed last year.  The pop up uses my favourite trick you taught me last year - the pop-up displays data from Widget:: PersonIDfk using value-list values of Person:: PersonID & Person:: FullName, displaying only values from the 2nd field.  So the pop-up populates PersonIDfk in the Widget table with a Person:: PersonID value chosen by scrolling through peoples' names.  In order to make the pop-up list ALL person records I had to re-do the relationship *again* ... reverting back to the original setting, of relating Widgets to Persons via the PersonID key value - in the end, it was the pop-up value-list which dictated the relationship being this way.  Relating Widget to Person using a key value in the former and a foreign key in the latter, resulted in my widget assignment pop-up only listing people who already held widgets - not useful!
                So, hitting a wall made me stand back and consider the design, and realise that I was wasting effort trying to make something work, when that something wasn't the most effective way for the user to do what they wanted to do.
                The problems I have with this DB I can see are because I was handed a half finished DB rather than sitting down and analysing the needs, asking the user how their business processes work, and designing a structure to fit.  The upside is that it's teaching me a lot, which is good...