Jump to content

Finding First and Last Related Record


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

Recommended Posts

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!

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 6673 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
×
×
  • Create New...

Important Information

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