Get previous record - breaks if no previous

[Charity]

We deliver parts and we manually create the delivery stops now. We have dispatch who creates each stop of a run. We time how long it takes between two points (called two Stops) and marry the two stop fields along with Time in a Travel Times table. I am trying to automate it and hopefully have it create the run line time sequencing automatically.

It seems to work except for the first record and I can’t figure out why. I want the first record, if it is the first record, to grab the start time from the Pull Out Time in the Run record what you call parent. otherwise use the prior Run Line’s record’s end time. I hope this is possible because it looks like it will save a lot of manual work if I can get it working.

Actually, it is fine if it doesn’t count the first record because we always have the time from the shop or yard to the first stop. But any record I put as first won’t calculate so I must have this broken first record. I can’t make Start Time regular field even if auto-enter without checkbox because it doesn’t update or change as I add and change the schedule. But calculation does not seem to be the answer either.

Ideas appreciated and very open to changes. Oh and I must enter return trips also as Travel Times because sometimes it is a different time going back. They usually go back the way they came but not always. I feel like which comes first, chicken or egg when trying to jumpstart this calculated schedule.

Parts.zip

[comment]

have it create the run line time sequencing automatically.

Try it this way, perhaps?

Trips.zip

However, there's something wrong with the way the trips are being built, with the responsibility to enter consecutive legs resting on the user. They should be allowed to build a trip by entering stops only.

[Charity]

Wow, much to study. As to why I created the Legs value list for selection instead of selecting individual Stops was because I sat Dispatch down (only one of five) and he would select two Stops which had no times (were not a leg) and then when he corrected it by adding a Leg and inserting the time, the Run Lines (TripLegs) wouldn’t change. It might now! And he found it awkward to go back and forth. So I tried fancy scripting to make it easier.

I could show you my prior attempts but I like you too much lol. I would ideally let Dispatch select each stop and if there is no associated Leg, tell him and let him add the time (into the Legs table in new window?) and script also create the reverse direction then allowing him to change either time in the two new Legs before then continuing the Run build. I kept getting messed up on record locking, on refresh, second window for creating new Leg would get lost in back. I even tried using relations allow created but I failed quite poorly incredibly well. I read once that get the structure right first before the User stuff so I stopped scripting attempts and focused on the relations. Since you think the selection process is incorrect, I will work to change it but it shouldn’t require changing this structure you provided for me, should it? I hope not!

So Dispatch builds the standard Run (Trip), googling for times and adding a Leg if necessary. Once a Run is created, it probably won’t change but I just realized it might. Sometimes they add an additional stop in a run (requires adding a TripLeg). All I can think is that the entire run lines (from point of insertion on) would have to be rebuilt. Maybe I can use a sequence number which can rearrange existing Trips and base the order of display on that. But I don’t think the calculations would know to use that order instead of the order they are created in. These Runs (Trips) become templates and the TripLegs will be imported into Daily Runs where they will be assigned a date and Driver.

Running totals eh? Cool. And you got my End Time figured also. Thank you very much, Comment. :)

[Charity]

BTW, drivers are assigned legs in the Daily Runs. And they divvy up chunks of legs sometimes with other drivers; they get paid by the legs they complete. I tried only using one side, the destination but relations would break without both sides. I will try anything you recommend on any part of this at all.

[Charity]

I tried creating field Sort number in TripLegs and sorting relationship TripLegs but the portal didn't change. Back to drawing board study mode.

[comment]

Since you think the selection process is incorrect, I will work to change it but it shouldn’t require changing this structure you provided for me, should it? I hope not!

What I think is irrelevant here, since this is a business decision. My preference as a dispatcher would be to plan a trip by its stops - but that's just me. And yes, it would require a different structure (I think).

[Charity]

I just got back from showing Dispatch. He is blown away by it. Adding the sort field and sorting the relationship did work, I just had something wrong before. So I explained that he only needs to sort if he adds a new TripLeg later out of order and needs to re-sequence them.

He found the pop-up selection showing both start and end fine. Heck, Dispatch has been doing all of this on spreadsheet paper before! But I have put on my list to get better at this stuff and change it to planning trips by stop when I release version2. I suggested that they continue doing it on paper for at least a few days to be sure it all works fine. I entered all the data last nigh. But they decided to begin using it. I wished them luck. If successful then maybe I can get out from behind the parts counter and maybe even get a raise!

A toast to the wonderful help I received. And a toast to Comment for such a pretty solution. :beer: