Problem using < relationship (Scoring sailboat races)

[jim lee]

Setting up a yacht race scoring application.

3 tables

Boats - Boat ID, Boat name, handycap and other boat info.

Races - Race ID, Race name, start time, distance and other race info.

Timeslips - Timeslip ID, Boat ID, Race ID, finish time, corrected time and more..

So, the timeslip links the boat's to the races and gives the finish time nessisary to calculate the corrected time.

Sorting a timeslip porthole on a race layout by corrected time gives a list of boats in order. (IE the first one is the winner) A running summery field gives a nice line number as each boat's place.

This works fine when scoring a race. But when looking at a porhole of timeslips from a Boat's layout (Listing of all a boat's races for the season) it does not work. (Because it relies on the porhole ordering from the race layout.)

How can I calculate what place a boat came in on a race?

My initial idea was to set up a self relationship:

Timeslips that beat us

Times that beat us::corrected time < our::corrected time.

Place = Count(Times that beat us::Timeslip ID)+1

I can't make it work. Place is always blank. It seems it doesn't like the < relationship.

Ideas? anyone?

Help?

-jim lee

[JesseSFR]

I think you are definitely on the right track. Could you include the exact specification of the relationship. It sounds like you might have flubbed something up in it's definition. It should be

mySlip vs otherSlip

RaceID = RaceID

corrected time < corrected Time

Place = count( otherSlip::ID ) + 1

That should pretty much do it. The fact that it is blank is very strange because you should at the least get a one.

[jim lee]

I tried an experement..

Each timeslip has the boat's finish time stored in it. IE the time the boat crosses the finish line. Setting up the relationship like you said before <> works fine.

place = Count(times earlier than ours::Timeslip ID) + 1

This works great for giving a boats order for crossing the finish line.

But.. The actual corrected time is a function of the boat's handicap and the race's globals telling what formula and handicap choice to use in the calculation. IE The relationship must rely on calculations and globals. I think this is where my world falls apart.

There are 2 handicap values for each boat. YRA PHRF and the local PHRF. There are at least 2 ways to score the races Time on time and Time on distace. There is now talk of different calcualtions with different number tweakes.. This tool was designed to show "what if"s. So there's a few controls that need to set how things get sorted.

Thanks lots for the tips. I just wonder if I'm barking up the wrong tree in my approach.

-jim lee

[comment]

You cannot base a relationship on an unstored calculation on the 'other' side. However, you could try something like this (roughly):

Sort the related timeslips by corrected times, ascending. Define a calculation field in Boats that lists all related corrected times. From each timeslip, calculate the position of its corrected time in the list. Count the values in the short list, i.e. in the portion of the list before the position.