4 Replies Latest reply on Jul 11, 2012 4:35 AM by brian.curran

    Can I Auto-complete a Drop-Down list using a Calculated field?



      Can I Auto-complete a Drop-Down list using a Calculated field?


      I've been struggling with this for hours now and have ended up really confused after trying so many different combinations of relationships and value lists. I really would like to use a DropDown list rather than a Pop-Up as I prefer the auto-complete method of data entry.

      In an 'Employees' table, I have:
      __kpEmployeeID (Number) Indexed, Auto-enter Serial, Can't Modify Auto
      NameFirst (Text)
      NameLast (Text)
      _cNameFull (Calculation) NameFirst & " " & NameLast

      I've created a Value List called "Employees", which takes its values 'From Field' (top option):
      Use values from first field:

      Also display values from second field:

      Show values only from second field is ticked.

      In a 'KeyDetails' table, I have:
      ReceivedBy (DropDown)
      Values from "Employees"

      Up to this point, everything seems to work fine. I can choose an Employee from the dropdown list by either typing, using the arrow buttons or by mouse selection.

      What I would like to do next is to overlay the Employees::_cNameFull over the top of the KeyDetails::Receivedby field so that the name is displayed rather than the __kpEmployeeID

      If anyone could advise me on the relationship required between these two tables and if there are any errors with the above, I would be very grateful...


        • 1. Re: Can I Auto-complete a Drop-Down list using a Calculated field?

          Looks like you are about to discover a trick used in Filemaker to achieve this effect:

          The trick is to put two fields on top of each other. (You wouldn't of have guessed that would you?) Tongue out

          First you put the IdFk field on your layout, you format it as a dropdown list where the first field is the ID and the second field the c_FullName. (You already have this, so far so good.) Then, in the inspector under the data tab, make sure this field has field entry in browse mode, but not in find mode.

          Then you copy this field (Ctrl-Drag on windows) so you have a second field of the exact same dimensions.

          This copy will become the c_FullName field from the Employees table.

          In the inspector under "Data" make sure this field is not a dropdown but an Edit Box. And change the Field entry so only Find Mode is selected and Browse mode is not.

          Now select oth fields and use the align tools to put the two fields on top of each other.

          This way: In browse mode you will get your dropdown list and can select an employee.
          In Find mode you can enter a full name in the c_FullName field and search an employee like that.

          OK, so far so good.

          Only issue: if you are using filemaker 12 then you have probably run into a bug. If you enter browse mode it's possible you are still seing the ID instead of the c_FullName.

          Workaround for this bug (very silly I know) Enter layout mode again. Select the c_FullName field. Hit the right arrow key on your keyboard to move the c_FullName field one point to the right.
          Enter Browse Mode again, you'll see it's working now.
          Go back in Layout mode and nudge this c_FullName field one point back to the left with the left arrow so it's perfectly on top if the IdFk field again.
          Go back to Browse mode and all should be fine.

          Isn't Filemaker 12 great??

          • 2. Re: Can I Auto-complete a Drop-Down list using a Calculated field?

            Hi DaSant,
            Thanks for the detailed instructions, I already had the _cNameFull field working but what I failed to mention was that I also want to use two dropdown lists on the same layout.

            When we collect customer keys we need to log certain details including the name of the person who 'received' the keys. Likewise, when we return the keys we need to log who 'returned' the keys as well. This is dealt with by two separate fields using the same dropdown list as detailed above.

            This means that when I choose a name for the ReceivedBy field, this action incorrectly updates the ReturnedTo field as well. I think I need two separate relationships between the 'Employee' and 'KeyDetails' tables...

            Does this sound right?

            • 3. Re: Can I Auto-complete a Drop-Down list using a Calculated field?

              Two different dropdown lists for two different entries do indeed require two different fields.

              EDIT: You might be able to get away with just adding a new field in your KeyDetails table and creating a second relationship from that field to the OEmployees table. If that doesnt work try:

              In terms of relationships it also requires a new Table Occurrence.

              Sounds scary, but don't worry.

              It's not hard at all.

              In your KeyDetails table create a second field "ReturnedByIdFk" this is a number field.

              In your Relationship view "File" - "Manage" - "Database" -"Relationships" select the Employees table and hit the icon at the bottom with the two green plusses:


              Name the new Employees TO (Table Occurrence) something that makes sense, like: "Employees_ReturnedBy"

              All the rest is just the same as what you already have: create the relationship:


              On your layout you might as wel just copy the astructure you already have, the Idfk field and the c_fullName field.

              You just have to point them to the correct fields in the New TO. You can still use the same dropdown.

              Voila, now you can safely select two different kinde of employees.

              Remember that we DID NOT copy a table. There is still only one Employees table and all Employee data always still goes in that one and the same table.

              We just made a new occurrence of that table so we could make a different kind of relationship.

              • 4. Re: Can I Auto-complete a Drop-Down list using a Calculated field?

                Superb! It took me a while to figure out what you meant but it's working great now.