Finding First and Last Related Record

[HiredGun79]

Hello,

Say you have a paper route database. You have a related database that has the current day's itinerary. Each stop is identified with a stop number.

How would you write a script to place the first stop and the last stop in two other records, first_stop and last_stop?

To rephrase that, the record with the first stop number (always going to be 1) goes in first_stop, and the record with the last stop number (could be 10, 20, 62. . ) in last_stop.

I think it's possible to go to a layout, enter find mode, go to the stop layout, find all stops related to the day we're looking at, sort them by stop number ascending, take the first record and put in record first_stop, go to record (last), put that in last_stop.

Is there an easier way to do this?

Thanks!

[sbg2]

Min(TO_WhereDateEqualsDate::StopNumber)

Max(TO_WhereDateEqualsDate::StopNumber)

[HiredGun79]

Will that return the primary key of the first and last stop off the stop table?

Wait, I just tried it - that returns the actual number value of the stop. . That's cool, but what I'm really trying to do is get the location of the first and last stop. . . For instance, you have a name for the each stop, first stop, Mr. Pink, second stop, Mr. Orange, third stop, Mr. White.

I want first_stop and last_stop to be Mr. Pink and Mr. White. .

Sorry I didn't phrase my question correctly. . .

Edited August 24, 2005 by Guest

[sbg2]

It returns the StopNumber, you tell me if that is the primary key for the Stop Table.

[HiredGun79]

No, that's not the primary key. . I have a unique, auto enter serial number. . . And I have a foreign key for each record in the stop table that is from the "day" table. . so I can relate to do lists and calendar stuff to each day as well. . .

So in the stop table, there are stops from each day, probably all mixed up. . the stops that have the same foreign key is the subset that I'm looking at. . I want to determine the first and last location of all related records to a given day. .

This way, I can store each stop location in a seperate, "location" table and avoid data entry redundance. So the stop table has two foreign keys. . . the "day" key and the "location" key.

In other words, it's a join table. . .

[SlimJim]

Filemaker will pick out the first one for you (if your sorting is right) simply by referring to the related field: stop::name and you can get the last one by using the Last function Last(stop::Name)

[HiredGun79]

Will that work when I'm getting the name out of a distant table?

I want the info displayed from the Day point of view. . I want to display info from the Location table, they're joined by Stops. .

So I want to set this. .

day::first_stop --> stop_table --> location::name

So I should make first_stop = location::name

and last_stop to Last ( location::name )

Is that right?

How do I specify sorting in this context?

[SlimJim]

I didn't read your post carefully enough I thought the name was in the stop table. I have only tried Last through one relation so I don't know if it will work but it has to be worth a try and yes to the field definitions. I would have thought the sorting should take place in the stop table since it is the first and last stop that you are looking for.

[HiredGun79]

Ok, that didn't work - It seems as though the first and last locations are being sorted in the location table. .

I guess the way that filemaker sees the related locations is just as being related, not related through the stop table. . so when I put in last, it's finding the last location record that's related to the day. . which, after a while, there could be hundreds of locations related to a day.

Go on back up to my first post and let me know if that's a valid solution (awkward as hell though)

Thanks for the response.

[SlimJim]

Your first post sketches out a possible scripted solution and at this point I have nothing else to offer.

[HiredGun79]

Ok, I'll work on that first method. .

If anyone else out there has a more elegant solution - let me know! This manual find scripting is not very cool. .

Wish you could put field data into find requests without directly using a layout. . .

Thanks for the response.

[sbg2]

Attached is an example showing two ways to calculate or show what I think you want. The first way uses Min and Max calcs and a relationship the other uses portals showing only 1 row and sorted. If this is not what you want then I would sigguest you might want to post a sample of your tables.

FirstLastStop.zip