Jump to content

Lookup value over three tables


TLM
 Share

This topic is 6519 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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 wink.gif 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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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. wink.gif

Link to comment
Share on other sites

  • Newbies

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.

relations.gif

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

Link to comment
Share on other sites

This topic is 6519 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.