1 Reply Latest reply on Mar 27, 2010 6:16 AM by Jade

    Relating Tables

    MarkyMark

      Title

      Relating Tables

      Post

      I am fairly new to FileMaker. I am a novice programmer. Here is the issue I'm having using FileMaker 9 Pro Advanced on XP.

       

      I have setup two tables. One table is called Market Profiles - a table containing information about any given city. The other is called Venue Profiles - a table containing information about hotels and such. This is what they look like:

       

      Market Profiles

      kMarket_ID - a numeric key generated upon the creation of each City or Town

      MarketName - user enters the name of the Town or City

      MarketProvState - user enters the name of the Province or State the City is in

       

      Venue Profiles

      zMarket_ID - used to relate the two tables

      VenueName - user enters the name of the Hotel

      VenueCity - user selects the City or Town from a Drop Down List populated by entries in the Market Profiles table

      VenueProvState - I want this field to be chosen automatically based on what the user selects for the VenueCity field

       

      This probably is an easy fix, but I can't seem to get my head around how to accomplish it. Any help you can give would be much appreciated. If there's any info I left out, please let me know.

       

      Thanks

       

      Mark

        • 1. Re: Relating Tables
          Jade

          Hi Mark,

           

          Unless you need to keep a duplicate copy of the city and province/state on the Venue Profiles table, this is what I suggest:-

           

          Market

          kMarket_ID - a numeric key generated upon the creation of each City or Town

          MarketName - user enters the name of the Town or City

          MarketProvState - user enters the name of the Province or State the City is in

           

          Venue

          kVenue_ID - an auto-enter serial number generated upon creation of the Venue record

          zMarket_ID - number field used to relate the two tables

          VenueName - user enters the name of the Hotel

           

           

          Add a value list (Manage>Value Lists… in the File menu) for Cites using values from kMarket_ID and also display values from the second field, MarketName.

           

          On the relationship graph (Manage>Database… in the File menu) draw a relationship between kMarket_ID and zMarket_ID (one-to-many).

           

          On the Venue layout (or Venue portal on the Market layout), add a zMarket_ID field.  Make it a drop-down list using values from Cities.

           

          When the user selects a city from the drop-down list, FMP will put the kMarket_ID value into the zMarket_ID field thereby relating the two records.

           

          Now you can use the fields Market::MarketName and Market::MarketProvState on the Venue layout or portal since they are related fields.  You do not need VenueCity or VenueProvState.

           

          If you're interested in more details on how to set up drop-down lists, here is Phil's tutorial.

           

          Hope this helps…