Grandparent-Parent-Child relations and Value lists

[RSGC]

I don't know if this has been asked a lot or ever. I don't know what I would search for to try and find it. Here is my situation:

I have three related tables: People (grandparent), Boats (parent), Results (child). In this database, a Person can own many boats, and a boat can have many results. No problem setting up the tables to have unique ID values (single user system) and relations to make that structure work. Here is the problem:

In the Results table, I need to populate an ID field (foreign key) with a value from the Boats table (primary key). Since the user should not have to remember the ID values, I am using a popup, based on a value list that has two columns. The first is the Primary Key from the Boats table. The second is the Boat name. Works great so far. Now for the problem:

It is possible (and happens that) there are about a half dozen in 400 boats that have the same name. The value list/pop only displays the first instance of a given name. So, for example, the boat name "Bearcat" exists three times, with unique primary key values in each record in the Boats table, but the popup only displays one entry. In this (and to date in all) cases the owner is different for each duplicated name.

I figured the simple thing to do would be to create a calculated field in the Boats table that was equal to: "Boat Name" & "/" & "People::Owner Name" and use that as the second field of the value list. Seemed simple, but it does not work . . . FMP reports that it will not work because the field can not be indexed. I tried adjusting storage options, but to no avail. It will not do this on the related field, apparently.

So . . . It is important that 1) all instances of the boats show up in the list, and 2) that the user has some idea of WHICH boat is owned by WHICH person as they make the proper selections.

Sorry that the question is so long. I can not think of a shorter explanation that would be clear. Of course I am making the assumption that the forgoing is somewhat more clear than MUD! I look forward to some expert guidance here.

Also, if I should post this in another area, please advise. I stuck it hear, realizing that the solution might have nothing to do with Value Lists, but that is where the problem is visible, and where the solution needs to end up.

My final note, as there are only a handful of instances out of a couple of hundred, I would prefer not to have a "nested" selected solution. In other words, to deal with these occasional instances, I would not like to require the user to click twice (once on the boat, and then once on the owner or vica-versa) in all selections. I know, picky, picky.

Thanks,

Bob

[djgogi]

You should denormalize your Db to achieve the scope.

Since boat name exists only if exists user in People, define an field boatOwner in file Boats as auto enter calculated value "People::Owner Name" ( otherwise define boatOwner as look up from field "People::Owner Name"), than define the second fileld for the list as Boat Name & "/" & boatOwner.

HTH

Dj

[RSGC]

Thanks . . .

I had tried making the value list refference a calculated field that refferenced a calculated field that refferenced the grandparent field. FMP is smart enough to prevent that from working too.

Lookuped seems to work. Sometimes the older technique is the best!I am confused that it automatically relooks up the value . . . I sort of thought that it would fill in the value the first time it had valid information to do a look up, and it would not do so in the future, and I would need to issue relookups all the time. It seems to do a relookup whenever the value in the foreign key is changed. Very cool. Thanks!

Bob