Looking for a Global field solution regarding mileage..help?

[AbsoluteVoice4u]

I'm thinking a global may be required to accomplish the folowing, but I'm baffled.

Four fields in play = Start Mileage, Ending Mileage, Total miles traveled and Highest Mileage To Date field.

Start and Ending fields are manually entered and result shows in Total Miles Traveled field.

I'm seeking a button that captures the Highest Mileage To Date ( existing in any record ) and auto enteres it into the current record's Start Mileage field. Seems so simple, yet the creation eludes me. Solution anyone, please?

[LaRetta]

Hi, good to see you around although I wish it wasn't for a problem, LOL.

It seems only two fields are relevant here (start and end mileage) so it is possible that I am misunderstanding your need. Please speak up if so. The 'HighestMileageToDate' would then be the largest EndMileage, right - because you want the ending mileage to be the beginning of the new record?

Please see attached (v11). Note that I am using Cartesian product for the join (X) which relates all records. If there are different vehicles, you will need to change X to = and use the unique vehicle ID in the relationship instead. Note the relationship is sorted descending on EndMileage so that the first related record (which is a self-join table occurrence) is the largest end miles and then also note the auto-enter of StartMileage.

I would also add validation to your EndMileage field that it cannot be less than the start, something like:

EndMileage >= StartMileage

... producing Boolean true so the validation passes.

UPDATE: Added the blue

Mileage.zip

Edited July 9, 2012 by LaRetta

[AbsoluteVoice4u]

LaRetta = :-) You are Gold! (Always have been) It's been toooo long...hope all is well with you and yours.

...and yes, the 'HighestMileageToDate' would be the largest EndMileage.

I'm opening your mileage.zip now and will get back to you..

Thank you soooooooooooo much!

[AbsoluteVoice4u]

Helo again......your solution is 99.999% what the Dr. ordered. The only variation I see is the necessity to not have the start mileage auto-enter.

Case in point.....out of thousands of records, I would choose what record to address individually...so...I need to touch a button to enter the LargestEndingMileage for that record..........other than that....all looks perfect.....in the mood to do another zip?...huh, huh.huh? :-)

[LaRetta]

Another file probably unnecessary ... let me know after you try this:

1) Remove the auto-enter from the StartMileage

2) Create a button with one step: Set Field [ StartMileage ; Mileage~highest::EndMileage ]

This will fire against the current mileage record you are on.

[AbsoluteVoice4u]

Thank you, LaRetta....working with it right now :-) Yer awesome!

[LaRetta]

I am not where I can try it but with version 12 one could eliminate the self-join with something like (pseudo-code)

ExecuteSQL(

SELECT MAX ( EndMileage ) AS EndMileage FROM mileage

)

... and there might be other functions besides MAX which could be used. This is sure going to reduce the graph clutter if speed holds up and that is hard to predict with only the first version which is always a bit slower and sloppier in everyone's designs. I am holding off on speed -testing until after the first updater.

... I just had to mention this for folks on v12.

CODE IS UNTESTED ... the AS isn't required

[AbsoluteVoice4u]

Someday ver 12 will be mne... :-) ..and ty for letting me know.