Table Design and Report Guidance 1

[Auguy]

I have a Truck Trailer leasing App I wrote a few years ago that has a Unit table (Trailers) that has about 30 fields that define the trailer. Things like: make, model, length, year, type of doors, type of roof, weight, axles, etc. In recent years I added a Unit Properties table where they can add & define other descriptive fields and their valid values. This table obviously can have many entries for each Unit and I only save records in this table where the user has entered a value. These are presented in a scrollable list on the Unit form. I did this so I wouldn’t have to change the Unit table every time they came up with something new to define the Unit.

When reporting on these units I let them select the fields and the properties they want to print and create a custom format from the default report on the fly. The reporting screen allows them to enter “Filter” conditions for the Unit fields. I use those to build a Where clause for the Select statement using the “AND” operator for the selected fields.

Now it’s time for an update and they want to add more fields or properties that only apply to certain types of trailers. I’m thinking I should change most of the fields into properties. This wouldn’t be too hard to do, but I’m worried about how to do the Select on the Unit table with a join to the Unit Properties table where I need to use the “AND” on the properties to get the results I need. An example would be I need a 48 foot trailer with side doors not more than 5 years old and not currently on lease. I could use an “OR”, but then I would have to do some more processing after the initial Select to get the individual Units and the properties into one record. I haven’t used pivot tables much, is that a possible solution? Looking for some guidance on the table setup as well as the reporting procedures.


Auguy
Northwest Ohio

 

[Auguy]

I think I need to use a CrossTab. Any suggestions on using Vfpxtab?


Auguy
Northwest Ohio

 

[Olaf Doschke]

To transform the many records of unit properties to one record to apply the overall where clause is of course a way.

But how about this?

*trailers
Create Cursor curTrailers (iTrailerID I AutoInc, nYear N(4))
Insert Into curTrailers (nYear) values (1995)
Insert Into curTrailers (nYear) values (2007)
Insert Into curTrailers (nYear) values (2010)

* additional properties
Create Cursor curProperties (iPropertyID I AutoInc, cName C(40))
Insert Into curProperties (cName) values ("length [ft]")
Insert Into curProperties (cName) values ("# side doors")

Create Cursor curTrailerProperties (iTrailerPropertyID I AutoInc, iTrailerID I, iPropertyID I, iValue I)
* trailer1 additional properties
Insert Into curTrailerProperties (iTrailerID, iPropertyID, iValue) values (1,1,50)
Insert Into curTrailerProperties (iTrailerID, iPropertyID, iValue) values (1,2,1)
* trailer2 
Insert Into curTrailerProperties (iTrailerID, iPropertyID, iValue) values (2,1,60)
Insert Into curTrailerProperties (iTrailerID, iPropertyID, iValue) values (2,2,4)
* trailer3
Insert Into curTrailerProperties (iTrailerID, iPropertyID, iValue) values (3,1,10)
Insert Into curTrailerProperties (iTrailerID, iPropertyID, iValue) values (3,2,2)

Select curTrailers.* from curTrailers;
inner join ;
(Select props.itrailerID, Count(*) nCnt from;
   (Select * from curTrailerProperties ;
     Where (iPropertyID=1 AND iValue>48) ;
        OR (iPropertyID=2 AND iValue>0) ;
    ) props;
    Group By 1;
    Having Count(*)=2 ;
) TempResult ;
on TempResult.iTrailerID = curTrailers.iTrailerID ;
Where curTrailers.nYear >= Year(Date())-5;
Into Cursor curResultTrailers

Select curTrailerProperties.iTrailerID, ;
   curProperties.cName, ;
   curTrailerProperties.iValue from curResultTrailers;
Left Join curTrailerProperties ;
on curTrailerProperties.iTrailerID = curResultTrailers.iTrailerID ;
Left Join curProperties ;
on curProperties.iPropertyID = curTrailerProperties.iPropertyID ;
Into Cursor curResultTrailerproperties

Select curResultTrailers
Browse nowait

Select curResultTrailerproperties
Browse nowait

The part you need to create dynamically is the inner where clause for additional properties and the outer where clause for the trailer properties directly in the trailer table.

The trick is to use OR (as you also suggested) and then count how many result records are there per trailer. Trailers that fulfill both criteria have 2 results, therefor their count(*)=2.

So that 2 depends on how many criteria you filter for.

Bye, Olaf.

 

[Auguy]

Thanks Olaf, have a star. That is really a detailed answer. I was getting close to what you have suggested. I was missing the "count" part and was having to do one or two more selects along with a crosstab to get to the final results. Your way will be faster and easier to understand. Thanks again for the example.

Auguy
Northwest Ohio

 

[Olaf Doschke]

This still has some work in it plus it might not scale so well on large amounts of trailers. But of how many o we talk here?

It might be a little better to first select all the trailerids via the part of the conditions that you can read directly from the trailers table, then inner join that temp result with the trailerproperties subselect with the complexer where clause on all the extended properties.

What you could also do is order by Count(*) descending to have something like best fit for a search, eg trailers fullfilling all, all but one, all but 2 conditions.

Bye, Olaf.

 

[Auguy]

There are about 10,000 in the table, but only about 5000 are active, and most of the time they are searching for trailers not on a lease which greatly reduces the count. My first select will probably be just as you indicated to reduce the number of records by using as many of the fixed fields as possible and then applying the properties filters. Your last suggestion on using the Count(*) might come in handy too. I'm going to talk them into having a set of common fields that apply to all of the trailer types and store these in the trailer table. All of the others will go to properties table. Thanks again for all of the help.

Auguy
Northwest Ohio

 

[Olaf Doschke]

Well, in that range it should work quite fast, especially as you intend to first filter for normal properties, then extended properties. Perhaps index the temp result.

* first filter all main properties,...

Select curTrailers.iTrailerID from curTrailers;
Where curTrailers.nYear >= Year(Date())-5;
Into Cursor curTempResult Readwrite

Index On iTrailerID TAG xID 

* ...then all extended properties.

Select ExtendedProperties.iTrailerID,;
       Count(*) nCountExtendedPropertiesMatching from;
      (Select curTempResult.* from curTempResult ;
       inner join curTrailerProperties ;
       on curTrailerProperties.iTrailerid = curTempResult.iTrailerid;
       Where (iPropertyID=1 AND iValue>48) ;
          OR (iPropertyID=2 AND iValue>0) ;
      ) ExtendedProperties;
    Group By 1;
    Order By 2 Descending ;
Into Cursor curResultTrailers Nofilter

* results sorted by
* number of matching extended properties descending

I put the inner join into the inner subselect, so the curTempResult does indeed filter, what extended properties are looked through and optimises that.

Bye, Olaf.

 

[Auguy]

Thanks Olaf. The more I think about this, the more I think I can apply this to other tables such as the Customer table. Being able to add properties on the fly that weren't in the original design would be great. Examples: hours of operation, preferred trailer length. Could be almost anything. Once I get the Selects working for the Trailers I may try and create a class so I can use it on any table. My property setup table is already designed to handle linking to other tables, I just haven't used it for other tables. It allows character, numeric, logical, or date fields to be defined and it has fields for the property label, length, report width, input mask and report alignment. I already have the class that I can drop on a form that displays all of the properties in a scrollable grid that can be resized. If this works as well as I believe it will, I may start using this in my other projects.

Auguy
Northwest Ohio

 

[Imaginecorp]

small world; I have been doing this for the last 12 years. Wrote and marketed Truck, Trailer and overseas container rental software.
The way we did it was create a table with the specifications, this could be changed by the users. We had a class (list box) that would use the "spec" table to present choices. Right clicking on it would allow the user to enter more choices.
The whole app (ours) was a "little" more complicated.

 

[Auguy]

Thanks Imaginecorp, that sounds interesting. If you did it I feel much better about using this approach in my app and I'll bet it was more than a "little" bit more complex too. Although some of the pricing rles and calculations they have me using are causing some headaches.

Auguy
Northwest Ohio