2 Replies Latest reply on Aug 1, 2009 8:29 AM by geeaka

    How to limit the value list of one field based on the value of a previous field



      How to limit the value list of one field based on the value of a previous field


      Hi all!


      I have been working on a database to store my bird watching records. I have been using Access to do so up until now. I have three tables that I am using - a table of locations, a table of a list of all the birds that are found in the US, and the main table with all my trip records.


      The Location table consists of LocationID, Location, State/Province, County, and Country

      The Bird List table consists of BirdID, Bird Name, BirdType, BirdCode, and Region

      The Trip Records table consists of the RecordID, Date, Location, BirdName, Status, Season, and Comments and three unbound fields - Country, State, and


      The main layout is for the TripRecords table. On this layout I want to add two fields - Country and state - to use to limit the locations field to only those of a specific country and state. For example, If I visited a location in Colorado, I would want to select the US as the country which would limit the state field to only those states in the US that I have visited. I would select Colorado from the list which would then limit the Location field to only those sites in Colorado that I have visited.

      Also, on this main layout I want to be able to limit the the Bird Type field according the region that the location is in. From the Bird List table I would limit based on the region field and the word Both. For example, in Colorado, the bird type list would consist of all birds found in the west region and of birds found in both the east (rarities and vagrants) and the west. I would use this list of bird types to limit the bird name field to only the selected bird type in the bird type field. For example - I could select Colorado from the state list. This would set the region field to west. This value of west and the word both are used to limit the bird type to types that are found in the west. I could select the bird type = eagle from the list and this would limit the bird name field to only Bald Eagle and Golden Eagle.


      In Access I did this through using queries to populate the drop down lists for the Country, State/Province, BirdType, and BirdNname fields. Since FMP 10 Advanced does not use queries, I am having a hard time figuring out how to do it in FMP 10 Advanced. Can Someone help me?


      Gary Ackert

        • 1. Re: How to limit the value list of one field based on the value of a previous field

          Hi geeaka, and welcome to the forum.


          Have a look at this post, should help you with solving this.




          If you have any questions just ask. 

          • 2. Re: How to limit the value list of one field based on the value of a previous field



            I think I understand the solution you gave me, but I am still confused. The solution you proposed requires the use of two tables of information. I have only one which is my location table.


            I tried adding two more TOs of my location table and related them through the country field. When I added the field for country to the layout it did not work. It came back with an "undefined field" when I went to browse mode where the country field was.


            So I tried to connect these two TOs to my main location table and they worked, but this always changed the country of whichever location was currently being viewed. For example, if I was looking at a record from Littleton, CO and changed the country from US to CAN (Canada), it changed the country of Littleton from US to CAN. So this wasn't working.


            So I thought maybe using global fields might help and tried those and they did not work.


            I went back to my original setup that did work and found that they only populated when looking at a current record. If I tried to enter a new record I would not even get a blank dropdown list when I clicked on the arrow ( the same thing happened when using the global fields).


            In Access I used two unbound fields to do what I am trying to do here. I do not want the country and state values to have to be stored as part of my triprecords table. Using the unbound fields allowed me to make this happen. FMP does not seem to allow unbound fields on a layout. If it does, what am I missing?


            Am I going to have to break up my location table?