Tricky calculation field.. I need help.

[BowDown]

Ok here's the overview.

On an order record we can assign a trailerID, and a dropID. Both are drop-lists and contain text ID's.

Ex: PB5, PB6, PB7 (trucks)

Ex: F7, F8, F9 (trailers)

Right now I have it setup so when you select a truck from the drop down list it will lookup what trailer would normally go with that truck and place that into the trailer field. Otherwise you can overwrite it by clicking on the trailer and selecting a different one from the drop down list.

At this point you select which drop the order is going to be on. This is a series of letters A-> E.

So here's an example truck portion of an order record:

PB6 F7 A

PB6 F7 A

PB6 F7 A

PB6 F7 B

PB6 F7 B

So as a summary this means that the PB6 truck is delivering 3 orders on it's A drop, then coming back and will be reloaded with 2 orders to deliver on it's B drop.

What I would like to do.. is if you assign PB6 with trailer F7 to a B drop on the same day it should insert text into my trailer flag field suggesting that an alternative trailer be used because it's already out on an A drop. Mainly we want to have trailers preloaded and ready to rock. If we assign a trailer to an A and B drop it will have to be reloaded with a truck idleing.

My main issue in my logic is checking the 'order' records for the current day to see if that trailer is assigned any other consecutive drops? If I could figure that out then I would know to suggest a different trailer.

Is there anyway to use a calculation field to compute this without having to run a script to check all fields? Like a function that will search all resulting records for a given criteria?

[mdpres]

Why not create a trailer equipment list table, that way you could keep track of m-t trailers as well as loaded trailers and assign them from there.

[sbg2]

Not sure if this is what you are looking for, example attached.

A self-join relationship where trailer = trailer AND date = date might and a calculation field of:

If(

not IsEmpty(Drop) and

not IsEmpty(truck 2::ID) and

truck::Drop ≠ truck 2::Drop;

// Then

"This trailer is already assigned a drop for this date")

truck.zip

[BowDown]

Not sure if this is what you are looking for, example attached.

A self-join relationship where trailer = trailer AND date = date might and a calculation field of:

If(

not IsEmpty(Drop) and

not IsEmpty(truck 2::ID) and

truck::Drop ≠ truck 2::Drop;

// Then

"This trailer is already assigned a drop for this date")

Well that's pretty cool. Pretty much exactly what i'm looking to do! I see you had 2 occurances of the truck table. How and why does this work?

[coconutt2000]

Think of a table occurrence as a "point of view".

In fact, consider the table occurrences as if they were each representative of a different door to the same building. Stand by any door and the view is different. That path leading to and away from the building from each door are the relationships. Some paths go around the building and come back in through another door.

Sometimes it helps to imagine each relationship's perspective as if you were standing on top of the table of occurrence and peering down the relationship at the other table occurrence. Just as with visual perspective, the further away you are from an opening, the less of the interior you can see.

Think of a portal as a method of placing yourself right outside a window or door. A portal shifts your perspective forward along the relationship graph, toward the data that you're trying to view, as if you were using a telescope in real life to get a good look at a distant object.