5 Replies Latest reply on Oct 21, 2013 11:41 AM by philmodjunk

    Postcode lookup using value list



      Postcode lookup using value list



           I've recently setup a postcode lookup, using a table of about 15,000 records = 'Postcodes' (three fields: code, state, suburb) and using a TO of this table 'Postcodes for Home Lookup'

           The home_suburb field on the layout uses a Value List 'home_suburb' set up as per screen shot, but only the first of each suburb name is being displayed. For example there are three Rivertons, but only the lowest PC# displays.

           what am i doing wrong? apologies for the scruffy screen shot

           (the State and Postcode fields are then auto-enter lookup fields from the same TO)


        • 1. Re: Postcode lookup using value list

               Value lists that display data from a field automatically omit duplicates from the specified sort field. (Sort values using First field). If you sorted on the first field, it would be worse as you'd then get only one suburb for each state due the duplicate state values being entered.

               Set up a text field that uses an auto-enter expression such as: Suburb & " " & State

               Select a Unique Values validation option for the field to make sure that this combination of values is unique.

               Use this as your second field and specify it as the only visible field. Your state field can remain the first field.

          • 2. Re: Postcode lookup using value list

                 Thanks Phil

                 That works to a limited extent, now all 'duplicates' display, but when for example the third 'duplicate' is selected, the lookup fields fill with the state and code from the first 'duplicate'....

                 I'd really like to get a drop down of all suburbs, with auto-complete from value list, then state and postcode auto-fill from selection

            • 3. Re: Postcode lookup using value list

                   Use a unique ID number from your look up table as field 1 and the combined name field as field two.

                   Base your relationship on the ID number instead of the names.

              • 4. Re: Postcode lookup using value list

                     Hi Phil - but not getting there

                     now pared right back to minimum fields in two tables only to try and get this right:

                     Table 1 - Contax

                     Fields = Home_suburb ; home_state ; home_postcode

                     Table 2 - Postcodes

                     Fields = PKID unique ; postcode ; suburb ; state ; suburb_state (text field of concatenated suburb and state)

                     all I want is to have a drop down in the home_suburb field that displays the suburbs and states, with auto-complete, to select a suburb....

                     Then I can use lookup to enter state and postcode

                     I've tried numerous variations of value lists, TO's and a variety of relationships, I either get 'no vales defined' or choosing say the second of three identical suburbs in three different states only inserts the first name

                     Your last post refers to a relationship - between which tables?

                • 5. Re: Postcode lookup using value list

                       Your last post refers to a relationship - between which tables?

                       Well you only have two tables described at this point.

                       Contact::_fkPostCodeID = Postcodes::PKID

                       And _fkPostCodeID is the field that you would format with your value list.

                       That gives you everything but "auto-complete" which can't be used on this type of value list unfortunately.

                       To add auto-complete, you may want to adapt one of the methods used in these example files:


                       FileMaker 12 users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
                       Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7