Conditional multi-criteria relationship challenge

[JariV]

Hi,

I've just started to convert a v6 solution to v8, but taking full advantage of new features is still quite challenging for a v8 newbie.

The solution has quite many tables, among them "Drivers" and "VehicleData".

Drivers contains "static" truck driver information (DriverID etc.)

VehicleData contains large amount of trip data collected from trucks. Each record contains DriverID, trip start/end dates,fuel consumption, speed etc. data from one trip.

The solution has a feature for summarising driver-specific vehicle data from specified time range. For more refined comparisons, in the Driver table there is also three additional qualities, which may be assigned to drivers (DriverSpec1-3).

DriverSpecs are also looked up into VehicleData records via DriverID-DriverID relationship.

The search interface in Drivers table has global fields for:

- SearchStartDate

- SearchEndDate

- gDriverSpec1 (opt)

- gDriverSpec2 (opt)

- gDriverSpec3 (opt)

I have been trying to create a conditional multi-criteria relationship for summarising data in Drivers. (to avoid searching/looping..)

The relationship in Drivers is as follows:

............Drivers......................................VehicleData

_______________________________________

...........Drivers::DriverID..................=....VehicleData::DriverID

AND..Drivers::SearchStartDate...≤....VehicleData::DriveStartDate

AND..Drivers::SearchEndDate....≥....VehicleData::DriveStartDate

AND..Drivers::gDriverSpec1........=....VehicleData::DriverSpec1

AND..Drivers::gDriverSpec2........=....VehicleData::DriverSpec2

AND..Drivers::gDriverSpec3........=....VehicleData::DriverSpec3

The idea with gDriverSpec1-3 is to provide additional capability to narrow down the group of drivers, for which the summary data is shown.

The above works fine if all the 3 gDriverSpecs in Drivers search interface are used. The problem is when some or all of them are not used (the field is empty), then this relationship will find no matches. Ideally, when gDriverSpec1-3 have no values, they would be "ignored" (as in finds).

If this kind of functionality could be achieved somehow, any ideas would be very much appreciated.

Jari V.

Edited August 13, 2006 by Guest

[bdonelson]

It is hard to tell with out looking your structure, but it sounds like you are trying to do to much on the drivers table. Having a global field in a table that performs comparisons like this on the same table, plus I assume a summation of some sort is rather complicated.

I would try to break it down into smaller pieces, atleast until it works.

Bill