First Occurence

[Idahomail]

Newbie here, but would appreciate some guidance.

There is a bus stop in front of my apartment that 26 different Vehicles stop at throughout the day. (Table:Vehicles Type: Bus or Car, VehicleID: Letter A-Z) and notes about each Vehicle.

There are a total of 100 pickups at this stop (buses and cars) throughout the day. (Table: Pickups, Pickup#, Vehicle Type, VehicleID)

For my purposes, the VehicleIDs of these two tables are related.

I need to accomplish two things:

1. Create a list of all vehicles and the FIRST TIME each vehicle picks up at the stop. (ie, what is the Pickup# of the first time each VehicleID picks up at the stop.)

2. Create a list of the all vehicles and FIRST TIME each vehicle picks up at the stop FROM a certain Pickup# forward. (ie what is the first time after pickup #25 that Bus E picks up passengers)

This is simplified from my actual data, but if I can wrap my head around the logic I think I can make it fit my own data. Basically, I want to be able to print a bus schedule (or car schedule) at the beginning of the day, or at any given point during the day forward.

Thanks so much. Hope this is a fun challenge to someone. Basic knowledge here, so the simpliest find or script would be appreciated.

Thank!

[comment]

what is the Pickup# of the first time each VehicleID picks up at the stop

Since your Pickups table does not have a Time field, I assume the Pickup# reflects the order of the pickups. Therefore, a calculation in the Vehicles table =

Min ( Pickups::Pickup# )

would return the first pickup# of each vehicle at the stop.

If the relationship sorts the pickups in ascending order, you can simply place the related Pickup# on a layout of Vehicles - it will show data from the first related record.

what is the first time after pickup #25 that Bus E picks up

Define another relationship between the two tables as:

Vehicles::VehicleID = Pickups 2::VehicleID

AND

Vehicles::gPickup# < Pickups 2::Pickup#

gPickup# is a global field, holding the value of 25 in your example. The rest is same as above.

If this is for display only, you could use a one-row portal based on the existing relationship, filtered to show only records where Pickups::Pickup# > Vehicles::gPickup#.

[Idahomail]

Thank you! Your quick and accurate reply is a nice gift for a new member!

I understand the first half of your answer, and it works beautifully. I think I'm missing a bit on the second half:

RE: what is the first time after pickup #25 that Bus E picks up

Define another relationship between the two tables as:

Define relationship? Am I adding a second calculated field in :Vehicles and then creating a relationship in the relationship tab?

Vehicles::VehicleID = Pickups 2::VehicleID

AND

Vehicles::gPickup# < Pickups 2::Pickup#

Is this a calculation in a second field in :Vehicles or is this in a script somewhere?

So, I think I am confused as to what "Pickups 2" is.

Thanks again- this is most helpful!

[comment]

Step by step:

1. Create a new field named gPickup# in the Vehicles table. Set the type to Number and storage to global.

2. In the relationships graph, add a new occurrence of the Pickups table (this will be initially named "Pickups 2" - you can change it to whatever you like).

3. Link the two tables (Vehicles and Pickups 2) as shown above.

You didn't clarify whether you need the value for further calculations or just for display - so I will pause here.

[Idahomail]

Thank you so much. This worked PERFECTLY! It took me a while to figure out on my own that when creating the relationship2 I needed to add a sort to put the pickups in a numerical order, but once I did that it worked great.

If I may indulge in one more step:

Show me all the pickups after Pickup #15

If there are any Vehicles that have already picked up the last pickup (ie, have no more records after Pickup#15) include the most recent (the last bus that I missed) record and somehow identify it as before Pickup #15 (I'm thinking a conditional format or a new status field that indicates Future or Past)

Thanks!

JP

[comment]

Show me all the pickups after Pickup #15

This is very easy: instead of placing the related field directly on the Vehicles layout, use a portal to Pickups 2.

If there are any Vehicles that have already picked up the last pickup (ie, have no more records after Pickup#15) include the most recent (the last bus that I missed)

For this, I would add a calculation field in the Vehicles table =

Min ( Count ( Pickups::VehicleID ) - 1 ; gPickup# )

and use this field as the matchfield in the relationship to Pickups 2 instead of gPickup# (or create a new relationship for this).

For the conditional formatting, compare the Pickups 2::Pickup# value to Vehicles::gPickup#.

[Idahomail]

Comment, thank you so much for all this. This has helped me learn a great deal and opened up new challenges.

I'm trying to spend some time understanding portals, as it may be the key to this, but would it be possible to perform a find on the Vehicles table BEFORE I seek out the first occurences (as above?)

For example, in the Vehicles table, I would like to omit all the records where the vehicle type is not equal to Air Conditioned.

Thanks!

[comment]

A relationship is a kind of "permanent find" and it doesn't depend on the found set in either table. So no, you cannot "postpone" the relationship until you create your found set - but it shouldn't make any difference.