Relating Tables

[Vinny Stabbone]

I am fairly new to FileMaker. I am a novice programmer. I apologize if I missed the solution to this problem on another post.. 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

[TheTominator]

VenueCity and VenueProvState do not need to be selected by the user. The user only needs to select the zMarket_ID value. The City and State info is then displayed by FileMaker automatically.

You can set up a data entry menu for zMarket_ID so that it displays the City/Town associated with the kMarket_ID value you are selecting. This is done by making a Value List. Configure the field to present menu using that Value List.

When setting up the Value List you would "Use values from field:" where you have specified that it should use kMarket_ID in your Market Profiles table.

Specify to "Include all values".

Choose "Also display values from second field". Select the City/Town name field.

Choose "Show values only from second field".

You probably also want to "Sort values using:" Second field.

An example of a Value List configured in this way is found in the Event Management.fp7 template that comes with FileMaker Pro. The "Contacts" value list in that solution uses Contact ID and Full Name instead of your kMarket_ID and City/Town name.

To have the selected market's City/Town and State display on the layout, you can put those related fields from the other table directly onto the layout. If you don't want to see the zMarket_ID value displayed, you can use the technique shown in the Event Management template and stack the City/Town name on top of the zMarket_ID value. Change the Field Behavior for the City/Town field so that you cannot enter it in Browse mode so that your clicks will go into the menu field under it.

Edited March 27, 2010 by Guest

[Vinny Stabbone]

That's very helpful dude. Thanks a lot.

[Vinny Stabbone]

On the same topic:

I've created a Provinces table, a Regions table (containing the different divisions of a Province ie/ Essex County) and a Market table (containing info on Cities and Towns)

In the Market table, the user enters the name of the City or Town then selects the Region it is in using a drop-down list propagated from the Regions table. Now, I want the State to be displayed on my Market layout automatically based on the Region the user selects. I tried the lookup function, but I couldn't get it to work.

Mark

[Vinny Stabbone]

I actually was able to solve my issue using a Calculation field. It seems to do what I need, but if anyone has any other suggestions, I'd be open to them