Comparisons Among Related Records

[dmbennett]

Good afternoon,

First I wanted to thank the regular posters here as they have helped invaluably as I am converting our small business database created in 2.0 into a relational model.

I have two tables "Transports" and "Legs" set up in a one to many relationship.

Our main working view - "In Progress" shows the "Legs" active for the day that come from multiple transports. I am trying provide and indicator to remind our employees to have modifications to one leg carry over to other legs. (If the first leg is running 2 hours late we might need to address the time on the other legs manually)

What I am struggling with is showing the "Leg" record number relative to the total number of "Legs" in an individual transport. ie displaying the data that would say 1 of 3, 2 of 4 ect.

I can get the total amount of legs relatively easily by doing count() function from the "Transport" table, but i am struggling mightily with the first part.

Ideally It would say the leg number 1 if that is the first one in chronological order, which may be different from the input order.

I have tried a couple different things including: comparisons among getNthrecord time values; setting up a sorted self join ect but cant seem to get it right. Does this have to be a script that runs off a trigger?

Dan

[comment]

Ideally It would say the leg number 1 if that is the first one in chronological order, which may be different from the input order.

Is there a chronological order? That is, do you have a date/time field in the Legs table that indicates when a leg starts or ends?

If the first leg is running 2 hours late we might need to address the time on the other legs manually

Permanently? Are you sure about that?

[dmbennett]

comment,

First thank you for taking the time to look at the issue.

Yes there is a chronological order, there is: "Local Time"; "Local Date"; "Reminder Time" which converts local time to our time zone; "Reminder Date" which does the same to date, and then the field your probably looking for "Central Time_Date" which combines the Reminder time and date to give something that can be compared to other leg files.

We don't track end times of transports, so we would only need the start time arranged chronologically.

As far as the second yes we make the changes permanent, We deal with Aircraft mostly so if a plane takes off 2 hours late we no longer need our transport on the receiving end to be there at the originally scheduled time. We need the updated time to reflect in the field so we can check to see if everything is going OK at the updated time.

Dan

[comment]

I am still a little confused here regarding what comes first in your workflow. I would expect each leg to have an ordinal number and a duration; then the exact arrival/departure times could be calculated from that and from a starting time in the parent record - see something similar here:

http://fmforums.com/forum/topic/80092-get-previous-record-breaks-if-no-previous/page__view__findpost__p__372681

It sounds like you want to do this in the opposite order?

[dmbennett]

comment,

I think I have have used a misnomer when labeling "Transports" it might be better thought of as "Passengers".

We get a call from a customer who then goes on to request a transport of Mr. X from Chicago to Miami. A "Transport" Record is created.

Leg (s) are created in Chicago to achieve the result. Each Leg represents one vehicle with a specific individual timeline that might overlap other legs timeline's.

There might be similar Leg (s) in Miami with variable relationships to how things went in Chicago for example.

I hope that helps explains the workflow. We work primarily off of a list of active "Legs" that shows all the legs that need to be monitored across a multitude of "Transports".

But my main goal is to figure out how to rank those individual legs chronologically relative to their "brother or sister" records and have that data display inside of a larger found set of "leg" records.

I apologize if I am being confusing or missing something relatively basic. Thank you again for your time and congrats on 20k, I know i have been creeping these forums for a while and have found your posts to be incredibly helpful.

Dan

[comment]

I am afraid I am even more confused than before. Perhaps a worked out example, preferably with 2 "transports" of several legs each, would help.

[dmbennett]

comment,

Attached is the problem solution created outside my horribly complicated main one. I have created a yellow box over what I am hoping to get right. Ideally it would display 1 of 2, 2 of 3, ect based on chronological order in relation to its brother or sister records.

Thanks again for taking time to look at my issue.

Dan

Transport Legs.ZIP

[comment]

Well, that shouldn't be too difficult.

Problem Outline1.zip

[dmbennett]

comment,

Thank you for your patience, I don't have a lot of experience with multi-conditional relationships, but your solution works perfectly and has been very educational.

Thank you again for your help.

Dan