getting values tied to a max and min calculation

Hello all,

I have a database with a table called Line and a table called Points.
A line has many points, and a point can be a part of many lines. I
have an intermdiate table called Points.Lines to clear up the many to
many relationship.

Anyways, in the Points.Lines table, I have a field called Order_Num
that lets a user choose which order on the line he or she wants to
view the points in a portal.

In the Line table, I have it set to monitor what is the lowest
Order_Num on the Points.Lines table and what is the highest one using
the min and max calculations. If a line has 3 points, these would be
1 and 3, respectively.

What I am having trouble doing is this:

In the Lines table, I want to have two fields called Start_point and
End_point. This will be automatically calculated based on what the
highest and lowest values are in the Points.Lines table. Now I can
get the highest and lowest values (1 and 3), but how can I set the
calculation to tell me what point_id is tied to these positions?

I want it to say "Ok, the lowest value is 1. What point belongs to
this 1?" and the same for the highest number. I want the field to
display which point has that value.

I would like to think this is an easy calculation (I would like to
keep it scriptless). However I've been pulling my hair out on this.
Any suggestions?

Re: getting values tied to a max and min calculation

In article <1193851154.887690.15350@v23g2000prn.googlegroups.com>, EP
wrote:

> Hello all,
>
> I have a database with a table called Line and a table called Points.
> A line has many points, and a point can be a part of many lines. I
> have an intermdiate table called Points.Lines to clear up the many to
> many relationship.
>
> Anyways, in the Points.Lines table, I have a field called Order_Num
> that lets a user choose which order on the line he or she wants to
> view the points in a portal.
>
> In the Line table, I have it set to monitor what is the lowest
> Order_Num on the Points.Lines table and what is the highest one using
> the min and max calculations. If a line has 3 points, these would be
> 1 and 3, respectively.
>
> What I am having trouble doing is this:
>
> In the Lines table, I want to have two fields called Start_point and
> End_point. This will be automatically calculated based on what the
> highest and lowest values are in the Points.Lines table. Now I can
> get the highest and lowest values (1 and 3), but how can I set the
> calculation to tell me what point_id is tied to these positions?
>
> I want it to say "Ok, the lowest value is 1. What point belongs to
> this 1?" and the same for the highest number. I want the field to
> display which point has that value.
>
> I would like to think this is an easy calculation (I would like to
> keep it scriptless). However I've been pulling my hair out on this.
> Any suggestions?

Prepare for a possible "D'oh!" moment. :o)

The easiest way is to have two extra Relationships from the Line table
to the Points.Lines table. Theses Relationships are defined exactly the
way same as your current one linking these two table, EXCEPT that one
is sorted by Order_Num in increasing order and the other is sorted by
Order_Num in decreasing order.
eg.
rel_MinPoint {same Relationship as existing}
sorted by Points.Lines::Order_num, increasing

rel_MaxPoint {same Relationship as existing}
sorted by Points.Lines::Order_num, decreasing

Now whenever you use these relationships to access the related
Points.Lines data you will get the FIRST record data - which will be
the minimum or maximum, as appropriate. (Obviously you won't get the
first related record work if you're using a Script to loop through or
obtain user-chosen rows from a Portal that is using the Min or Max
Relationship.)

For example, any Calculation or Script that accesses the field
rel_MinPoint::PointName will return the PointName data for the related
Points.Lines record that has the minimum Order_Num, since that will be
first in the sorted order for that Relationship.







Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)