I have a core table called "Clients". It has related a table called "Fleets" and another table called "Jurisdictions". There is another table that is related to "Fleets" called "Units". "Units" has a table called "trips".

Each "Fleet" is authorized to travel in a certain group of "Jurisdictions" (states). Each "Unit" (truck) that belongs to a "Fleet" and travels in a "Jurisdiction" has to keep a "trip" record.

I use my database to record these "trip" records.

Upon entering the "state" field of a new trip record, a value list appears that is based on the "jurisdictions" that are related to the client. This helps avoid user error by inadvertantly entering data for a state that the client does not have jurisdiction in.

But I want to go a step further. For example, If trip number one begins in california, then that only leaves four possible places where trip number two begins, California, Oregon, Nevada and Arizona. If trip number two begins in Nevada, then that leaves only six possible places where trip number three can begin; Nevada, California, Oregon, Idaho, Arizona and Utah.

Can anyone tell me how I can do this? If there are 48 jurisdictions, do I need to have 48 different value-lists? Is there a single validation calculation that can be written?

Send Help.

I don't have time to spell it out fully, but the trick will be to have a multi-line field in your Jurisdictions file, called "Neighbors" where you use a checkbox list of all state abbreviations to indicate the jurisdictions from/to which a truck can contiguously travel starting in that initial place. (Then, you could set up a relation between each record in Jurisdictions table and any record in that same table that lists it in the "neighbors" field, but...)

The tricky thing is using your trip ID to make sure you can reference "the last" trip by that truck. (You'll need a relation between any trip and any other trip by that same truck (I assume you have a truck ID), and you can add in the ">" relation on the trip # to make it so that any trip relates *only* to lower-# trips, and you can sort the previous trips (in that relation definition) in *descending* order. That way "the" related record on the "previous-trips" relation will be the very most recent previous trip by this truck.

There's gotta be a faster way, but for now, put a "neighbors" field in the trips table, and have the trip record fetch the "neighbors" values with a Lookup of the related jurisdiction's neighbors.

That way, you can just make a "Most-Recent-Same-Truck-Neighboring" self-join relation among trips (MRSTN for short) more strict:

It's better not to use IsValid. Use not IsEmpty instead.

Thanks for chiming in, Queue.

It seems there ought to be a better way than having the neighbors list actually stored (I guess it could be calculated, but even so...) in the trips table, but I couldn't think of it quickly enough to spell it out...

I think I have it. Can you exand a little on "multi-line" fields?

Currently my value list is populated by the "jurisdiction" records that belong to a client (client jurisdictions). It sounds like I would need a second value list of "jurisdictions" that belong to (or touch) other jurisdictions (contiguous jurisdictions). That value list is what would populate the "Neighbors" field?

If so, how do I limit the contigous jurisdictions to the values in client jurisdctions?

example:

My client only has jurisdiction in California, and Arizona. The contigous jurisdictions will not be limited to CA and AZ. It will list CA, OR, NV, UT, CO, AZ and NM.

Sometimes it's best to get people to do the tinking, and let the database just store what the decision is.

My problem is that the data entry process is very rapid. In fact the mouse is rarely touched. When we enter the data, for many different clients, this feature will alert us to problems in the records supplied by our clients. If the records they supply are not contiguous it helps to be alerted to it at the data entry stage and not at the oh no I'm being audited stage.

If you want to solve this problem by calculation try this:

1) Create a text calculation field "PreviousState" = State field. The "State" field is the field your client enters in a new trip record.

2) Create a text calculation field "ValidStates" = If(PreviousState = "CA", "CA#"& "OR#" & "NV#" & "AZ",

If(PreviousState = "NV", "NV#" & "CA#" & "OR#" .....you will need to test here every possible combination of PreviousState field with if statements, but first I would just try two states.

Above the # should be substituted for paragraph symbol (end of line) .

3) Then create one value list based on "ValidStates" field.

As long as the trips are entered in sequential order this should work.

In my previous posting ignore the step 1) Create a text calculation field "PreviousState" = State field.

Instead, create global text field "PreviousState" and set it, with a script, equal to "State" field right before creating a new trip record.