4 Replies Latest reply on Jan 13, 2015 1:05 AM by Stu412

    Staff and Projects scenario



      Staff and Projects scenario


      Hi there

      I have a scenario where I want to be able to select a member of staff from a dropdown and apply their name to a specific project.  I'm performing this in a portal based on the project table.  I need their PK ID to appear on the project table as a FK ID so I can use it for other joins further down the line.

      What's happening is that the staff name is populating to the field in question, so to the user everything would look ok.  However, I'm having issues getting the staff ID onto the project table.  Simple right?

      I would have thought that in the value list set up I'd use values from Staff:PK, also display values from Staff:Name and Show only values from second field.

      For some reason this is not working for me.  The dropdown correctly displays the names but upon clicking away the staff name is replaced instead by the staff ID.

      What I need is both the StaffID (FK) and Staff Name to appear on the project table.

      EDIT: One solution which would help further down the line on other work would be to simply get the FK of the staff member as a variable and Set Field with this value on the Project table.  However, I cannot find out for the life of me figure out how to tell Filemaker to go fetch the FK from the staff table for the previously selected staff member. 

      So far I've got Set Variable [$StaffID;Value;Projects:StaffID] based on the logic that it would look at the selected staff (now listed against the project) and go fetch the FK.  This isn't working obviously.

      This seems too simple so I must be missing something dumb!

      Thanks in advance

        • 1. Re: Staff and Projects scenario

          For some reason this is not working for me.  The dropdown correctly displays the names but upon clicking away the staff name is replaced instead by the staff ID.

          Believe it or not, this is normal behavior for a drop down list. You can switch to a pop up menu format where this does not happen, or you can add a field from the staff table to show the name once the ID has been selected. One trick is to use behavior settings on this name field to deny access to the field and then position it on top of the drop down list field. When you click on the name field, the drop down list in the field behind it deploys to let you select a name. When you select a name, the drop down list disappears and the name field that hides it shows the correct name.

          • 2. Re: Staff and Projects scenario

            Hi Phil

            Yea, I'm still reconciling myself to trying to believe it!!

            I've been trying to get the other method to work - it's odd that on this occasion it doesn't.  I select the value list from the foreign table and select a second field from the same table (in fact, the same field as that which the value list is based on), yet this only populates once - it doesn't update if I change the staff member for a second time.  If it worked, I'd just move it over the top of the dropdown, set text colours accordingly and that's sorted.

            The field I am dropping down on is within a portal which may have something to do with it, I'm not sure, although I've not seen this before.  The relationship is very simple from parent to child with the child being the portal.

            A bit stuck on that one.



            • 3. Re: Staff and Projects scenario

              It sounds like either this field is from the wrong table occurrence or the relationship is wrong for what you need here--which is much the same problem.

              You need a table occurrence where the field that gets the ID entered by your drop down list is linked to an occurrence of the table from which your value list lists values. It sounds like this is not the relationship you'd use in a portal as this relationship should match to only one record in your table and that would produce a portal that never showed more than one portal row. ;-)

              • 4. Re: Staff and Projects scenario


                Thanks for your help on this - a couple of staff table instances to track a project as it moves from person to person and the use of popup menus have solved a load of grief!

                Great stuff :)