Does the mileage field record miles traveled since last reading or just the current odometer value?
I believe you are just recording odometer values.
You have this relationship:
vehicles::vehicleID = Mileage::vehicleID
Let's add another relationship:
Mileage::VehicleID = MileageStartYear::VehicleID AND
Mileage::cYear = MileageStartYear::cYear
In Manage | Database | relationships, make a new table occurrence of Mileage by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box MileageStartYear as I have done.
We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.
Now this calculation field, cMilesYTD, can be defined as:
OdometerReading - MileageStartYear::OdometerReading
define cYear as Year (yourdatefield)
Thatnks for your reply. I've been out of it so long I forgot about selfjoin relationships :S
The above worked just fine although I have removed the additional cYear references in the new relationship as that wouldn't work with the data we have as some customers visit us just once per year.
I have now set up a calc which works out the annual mileage based on the first record in the relationship(effectively mile 0) and whatever the current record is
Each mileage record now has an updated calculated annual mileage amount which alters from record(odometer reading) to record(odometer reading) which is fine.
The only difficulty I have now is getting the last calculated annual mileage into the main vehicle record which I think would be ideally stored with the vehicle record as many scripts / calc will be looking at this information in the future.
What am I missing for this part of the problem...?
Many thanks again..
You mentioned a need for yearly mileage, which was the purpose for including the cYear field so that you could calculate a total mileage for each year.
Removing that part of the relationship means that the mileage you get from the mileage table will not be annual mileage. I'm guessing that you are using a different calculation to divide by the number of years elapsed since the year recorded with the first mileage entry to compute an average annual mileage.
Generally, it's best not to store such a value separately like this. If you do, you have to take great care to keep it correclty up to date--and this is why such denormalized data is best avoided.
But a script can use set field to update such a number field on command using your calculation for average annual mileage. Unless I observe a significant slow down, I'd just use the calculation directly.