Lookup value over three tables

[TLM]

Just playing with a trial version of FileMaker Pro 7 and i got a question.

For a solution i made for myself (in-house so to speak i have three tables like this with the relevant fields;

Results (ID, Category)

Results_Placings (Results_ID, Place, Points)

Results_PointScales (Category, Place, Points)

In earlier version i needed some multi-field keys to lookup a value in Results_Placings::Points based on the Results::Category and Results_PointScales::Place values.

It should be possible (i guess) to solve this in FMP7 with relations but i seem to be unable to accomplish this. Wrapping my brain around the new relational scheme of FMP 7 seems to be hard this evening.

Does anyone have some pointers/solution for me i can use to work on

Thanks in advance

TLM

Version: v7.x

Platform: Mac OS X Panther

[BruceJ]

You can jump tables unlike previous versions. You have to check to conditions though:

1. Of course, allow the creation of related files.

2. Put the third level field inside a portal based on the second level.

This second step is the one it took me a while to get.

I experimented with three tables:

People (ID, name, etc..)

Address (PeopleID, street, ZIP)

Zip (ZIP, city, state)

In the people file I created a portal to the Address table, then in the address table, included the street & ZIP, and also included the city and state from the ZIP file.

[CobaltSky]

Hello TLM,

What you are calling 'Multi-Field keys' (which were achived in v6 and earlier using calcs to concatenate values to create composite keys) can be achieved very simply in v7.

How it is done is by defining 'multi-predicate' relationships. This means that you specify more than one pair of match fields for a given relationship.

However I am not clear whether you need a multi-predicate relationship in this instance, since from the detail you've provided so far it doesn't appear that you are using multiple fields to create a match between Results and Results_Placings (given that you've only mentioned the existence of two fields in total in Results).

From what you've said, it sounds as though by relating Results to Results_PointScales based on Category = Category and then relating Results_PointScales to Results_Placings based on Place = Place, you'll then be able to define the lookup you require directly from Results_Placings::Points into a field in Results.

[TLM]

Well seems it's a bit more 'complex' than i initially explained (i had to split up the Results_PointScales tables in two -> Scales and Scales_Points), sorry about that.

Here is an image of a part of the database with the significant tables.

So the 'flow' would go like this;

- Results is the table where information is entered, like Year, Date, Category, Startplace, Finishplace, Distance, Racing Time, ... for a particulair race.

- Results_Placings is where the names of the riders are entered who placed themselves in a race with the following information, Place, riders name (retrieved from another table), Time behind winner, Points received for this particular race, ...

So if a particular finishes as 2nd in a race of category GO then he needs to receive the points as such as they are in the Scales and Scales_Points tables.

Don't know if i need the Points field (u_placings_Points in the image) in the Results_Placings file, guess it could be solved with 'multi-predicate' relationships like CobaltSky said. But i will probably need more than one and that's were i'm stuck for the moment

Hope this is clear enough, to understand what i want to retrieve.

TLM

Version: v7.x

Platform: Mac OS X Panther