Calculation, Lookup, or what?

[wolfe]

I am working on a database to keep track of car maintenance for a fleet of 20 cars. If a car requires being towed in, I have to use a rotating list of three different wrecker services. Currently my dispatcher has to look on the current wrecker list to see which service was used last. Then he knows to use the next wrecker service on the list. Say I have three wrecker services, Service One, Service Two, and Service Three. I need to be able to create an unlimited number of records regarding car maintenance and if I need a wrecker for one of my cars, I want the dispatcher to be able to look at the screen and see the next service to be used automatically displayed.

[Ender]

There are probably a few ways to do this, but this is what I came up with:

A self join relating to all records that have been assigned a wrecker service. Then use the Last() function to show the Wrecker Company of the last record in that relationship.

Relationship fields:

gOne (global, number) = 1

WreckerAssigned? (calculation, number result) = not isempty(Wrecker Company)

Relationship:

SelfJoin by WreckerAssigned =

gOne = WreckerAssigned?

Now the display field:

LastWreckerUsed (calculation, number result) = Last(SelfJoin by WreckerAssigned::Wrecker Company)

I suppose this could be done without the display field (showing the actual 'SelfJoin by WreckerAssigned::Wrecker Company' field on the layout,) if the relationship were sorted by RecordID (decending).

[transpower]

I suppose you could have three tables: car, towing_service, and car_towing (the join table). You could then lookup by date (using a calculation) the three most recent car_towing records and display the third (least recent) to use.

[Ugo DI LUCA]

If you just want a display, then you could just use 2 TO's CarServices and Services

Fields in Services-->Serviceserial and ServiceName, set in the correct order

Fields in CarService--> CarID, Serviceserial, NextServiceSerial (calc = ServiceSerial+1)

Link the 2 tables with NextServiceSerial tied to ServiceSerial in the Services Table

Add a field in CarService, autoenter text value with calculation set to replace existing values with a formula

Lookup (Services::SerciceName; "Last Service to be used in list" )

You could also grab the serial with this same technique if needed or control a record creation with it.

Uncheck the "do not evaluate if empty" in the NextServiceSerial, so that the first record for this car would be set with your first service.