We have a form with a tableframe on

[harrymossman]

We have a form with a tableframe on it. People scroll or search through the tableframe until they come to the project they are looking for. Then, in a separate tableframe, they enter data.

I would like to filter the first table on the basis of one of two key fields - Year. Bad choice of field name, made long before I arrived. It is actually in the form YR-X, where YR is a 2-digit fiscal year and X is project type.

Yes, I know that we could break the field up into 2 separate fields. That is not going to happen right now.

I want to show only projects that match YR-F. I.e., year doesn't matter, only project type. The records are not contiguous, so can't use SetRange.

Should I somehow use an array to get only the project type? Not sure where to begin. I'm open to completely different solutions, however this seems like the format that people doing the data entry can handle.

Harry

 

[footpad]

Harry,

>> I want to show only projects that match YR-F. I.e., year
>> doesn't matter, only project type.

Ouch, this limits our choices severly because you can only use wildcards at the end of setRange criteria. There is an easy answer that's slow when run or a could of harder answers that will result in more speed, but require more work to set up.

Your idea of using an array is a good one, however, the only way to use an array to filter records is to store the array results in a temporary table and then use that table as a master table in a second form. It can be done and can work well, but let's look at a different alternative that may be less work.

The easy answer is to use setGenFilter() instead of setRange(). It's slow though, especially when working on large datasets stored on a file server. It can be unacceptably slow.

The reason it's slow is because Paradox (or BDE, actually) has to pull down each record of the table and then decide whether or not it fits into the current "view" of the filter. There's no way to know in advance how many records will match and (in an attempt to optimize the process), BDE only evaulates filter matches long enough to fit the current view. That is, if your table frame displays ten records, BDE will find the first ten records that match and then stop.

This is why scrolling is so painful when using general filtered. Every movement action requires so much processing in the background that there are noticeable (and frequently painful) pauses while the matching record is located (assuming it exists).

Ranges, on the other hand, are faster because they're already sorted. Because you generally start with a sequence of characters indicating the first few matches, BDE can quickly determine the set that matches. It's a little more complicated than that, but that's generally why ranges are faster than filters.

So, the trick is, how do we add the benefits of filters without reworking the existing data structure? I would consider creating a second table. One that contains the existing table's primary key filed(s), as well as two additional fields: one for the year and one for the project code.

You would maintain this either by running a TCursor scan loop to populate the second table with the existing key values and the parsed version of your current project code. Next, add two secondary indexes. One that orders the table by Year and then Project code and a second that reverses those fields.

This, then gives you something you can range.

To provide the results of that ranging to the user, you'll probably have to rework the interface slightly. I'd probably add a Find Project Type function that displays a dialog box that lets the user enter a year and/or a project code (Perhaps using drop downs or list boxes populated with a summary query run against the second table). When the user hits OK, you then open a second form that uses both tables in the data model.

Your second table is that form's master table and your existing table is linked to the second table using the primary index.

In this scenario, you would determine and apply the necessary index on the second table at runtime (to display the results in order of the user's desired criteria), then call setRange() to use the user's values against the parsed versions of the project code.

It seems complicated and does take some time to setup, however, I think this will give you the flexibility your looking and the performance that your users will expect.

Now, you can also use a query as a document's master table, but this doesn't help much with the performance issue as the query must be run before the form is fully opened and there's little control over the processing. (Remember that filters are essentially record-by-record queries run behind the scenes).

It's hard to provide a functional example, for I don't have one readily available, however, if you'd like to see samples of the interim steps, please let me know.

Hope this helps...

-- Lance