Value List Based on Fields - returning the wrong data & editing the related field!

[Agent Ska]

Simple database.

Tables

Customers

Jobs

Orders

Staff

I have two customers in this sample database. Related to the Jobs table with League ID.

On the Jobs table, I want to select the "League Name" which is the Customer's Business Name for practical purposes. I set up a Value List based on fields, with a drop down box - selecting the League ID & League Name and enabled the "return second value only" which in my case is the League Name.

When I select one of the League Names, it was returning League ID instead. And, in the Customer's table, it was editing the League Name to the League ID.

Now, I can't even get the drop down box to show.

Can someone take a look at this and let me know what I am doing wrong.

Thanks.

Template2.fp7.zip

[LaRetta]

The League Name on the layout is the Customer's League Name. While in layout mode, see the two colons in front? That indicates it's from a related table. If you double-click it, you'll see it's from Customers. ^)

[Agent Ska]

Um, yeah... that is the point. I am trying to pull the data from the Customers Table into the Jobs Table. I don't want to have to remember League ID's all the time, so I set up a drop down keyed on the League ID, but to display the League Name.

Is this not the way it is supposed to be set-up?

[LaRetta]

Ah. Well the FIELD must be Jobs::LeagueID.

Here's the thing ... drop-downs will display the LeagueID after you exit the field - popup menus will continue to display the league name. Can you live with using a popup menu here? You aren't the only one to question this behavior. I use poup menu mostly. But in a few places, I've needed drop-downs because list was longer (and I needed type-ahead functionality). I ended up stacking the fields in those instances. If you wish to try it, here's how:

1) Remove the drop-down from your Leagues::LeagueName field and turn off 'allow entry in Browse'.

2) Attach drop-down to Jobs::LeagueID.

3) Select both fields and Arrange > Align > Center and Center.

4) Send Jobs::LeagueID to the back > Arrange > Send To Back so name is on top.

When User selects a LeagueName, FM will drop User into the Jobs::LeagueID field and open it. After selection, the name on top will change accordingly.

LaRetta

[Agent Ska]

Wait are you saying I need to Define a LeagueID field in the Jobs table?

I had not done that at all. I had simply added the related table.

[LaRetta]

Well, the version of your file I downloaded sure shows a LeagueID in Jobs and it's what binds the relationship between Jobs and Customers.

[Agent Ska]

Hmph... Had I not deleted that field, I might have been better off. I have it working the way I expected now, thanks.