Jump to content

Conditional multi-criteria relationship challenge


JariV

This topic is 6427 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 by Guest
Link to comment
Share on other sites

  • 2 weeks later...

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

Link to comment
Share on other sites

This topic is 6427 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.