easy way to calculate kilometer difference between two record and two différent fields

[curious99]

Hi all,

We run a rent-a-car company. We need to know how many kilometers were driven by our employees between rentals to retail customers. In order to do that, I need to deduct "km in" from the previous contract with "km out" on the curent contract. It sounds easy but for some reason I cannot manage to create a calculation that does that...having two different records to compare is were I hit a snag. Can someone light my way?

Thanks in advance

-)

Luc

[Slobey]

Are the km in and km out fields in different records? If they are, then you will need to create a relationship between the two records.

[Sanjai]

Luc, you may have to create a self join relationship based on vehicle registration number. This would help to find all the previous records in your database for that particular vehicle. Now the key is when a new record is created in the database for a new customer, how to find the last most recent kmin using the selfjoin relationship which is little complicated and I don't know a good solution. I have seen that there are several posts which describe how to use the last most recent record using the relationship. I saw a post from slimjim where Fenton and Queue had helped him. If you want to continue on that post, the link is:

http://fmforums.com/forum/showtopic.php?tid/170474/

[curious99]

Thanks for the feedback.

I am creating the relationship but what is or are the fields you recommend I link?

-)

Luc

[curious99]

Hi again,

As I was posting, the new post was written refering to other previous posts. I'll have a look at those and will let you know if it solved my problem.

Thanks once more.

Luc

[Sanjai]

I have worked on a rental application for hertz and we had used the vehicle registration number to keep track of the vehicles. You may have to create a self join relationship with the same table using registration number. Now the second question is the calculation field to show the value of last kmin for that car such that we can substract the it from kmout to find the distance when the car was driven by employees.

[curious99]

Yes, that is exactly what needs to be calculated last km in minus new km out. As for registration as a reference, we use an "inventory" or stock # that I will use for the self join.

Thanks for all your help.

-)

Luc

[comment]

IIRC, in version 6 you can create a value list of related values only.

If so, define a relationship SameCar:

CarID = SameCar::CarID

Define a value list of ContractID, using related values only (from SameCar). The value before last will be the ContractID of the previous contract for the same car. Extract this value using ValueListItems() into an unstored calculation field cPrevID.

Define a relationship PrevContract:

cPrevID = ContractID

Note:

I have never used version 5/6, so this may work or not.

[Sanjai]

Thanks Comment,

It is helpful for me to understand. But I am afraid that adding the items to a value list would automatically sort the values and hence the second last value may not always be the previous contract for the same car. Please correct me if I am wrong.

[comment]

1. If the value list only shows related values from SameCar, then it cannot be another car.

2. If the value list is a list of serial numbers, then the second last value will be the last record created for this car, excluding the current (new) record. Of course, this (or the data derived from this) needs to be auto-entered, because when yet another record is created, the current record will become the second last.

While this is an interesting puzzle, I cannot help wondering about the purpose of the excercise:

Every contract has a StartMileage and an EndMileage. That is all that is required, as far the contract goes. Now, there is some extra mileage accumulated between contracts. If one would note the mileage of the car when it was first bought (in the parent Car record), it would be very easy to know the total of the extra mileage. Does it really matter WHEN was this extra mileage accumulated?

Another thought: if you'd enter the data for a new contract into global fields, you could - via a sorted relationship, or Last() - get the last contract data very easily. Then create the actual new record by a script.

[Sanjai]

Every contract has a StartMileage and an EndMileage. That is all that is required, as far the contract goes. Now, there is some extra mileage accumulated between contracts. If one would note the mileage of the car when it was first bought (in the parent Car record), it would be very easy to know the total of the extra mileage. Does it really matter WHEN was this extra mileage accumulated?

There can be more than one reason which may matter to a rental car company. One of them could be to question their own representative if they observe that the distance is more than 25 miles. Like Luc, I have worked on an application for rental business and they wanted to see every information instantly other than the reports.

I was confused about the sorting of value list because I though the value list would directly list the KMIN field.

I think Luc should create a relationship based on a field which uniquely identifies a vehicle.

A value list to select the stock# based on above relationship.

A calculation field to select the second last value from the value list.

A self join relationship A based on the above calculation field to find the KMIN for that stock#

A calculation field to show the difference between the new records KMOut and A:KMIN

[comment]

to question their own representative if they observe that the distance is more than 25 miles.

Yeah, I suppose that makes sense - policing the workforce seems one of Filemaker's strong points...

I still think drafting the record on global fields would be much easier, and probably have an additional advantage in preventing user conflicts.