Filtering on multi-table forms

[bobsmallwood]

Is there a way to filter in a form on variables in the parent and (one or more) child tables (all child tables connected one-to-one with parent) presenting parent records that meet the filter criteria placed on all the tables?

 

[footpad]

bobsmallwood,

Not using filters, no. Typically, something like this is handled through multiple queries. I usually use an ObjectPAL approach along these lines:

1. Create a form for the user to choose the fields that needs to be searched and enter the search criteria.

2. When the user hits the OK button on the form, you examine what they entered and then build a series of queries (I prefer tCursor ranges) for each child table that's been selected. Each query returns the linking value (preferably one that has the name field name in all tables.)

3. You run each query and return the linking values that match that child table's criteria.

4. The result set from each table is then added to a temporary table in RIV:.

5. When all queries are run, you open a second form that uses the RIV: temp table as the master and the original master as a detail; the link is from the linking field in the temp table and the corresponding field in the master table.

An advantage of this approach is that is provides far more control over the "filtering" process. For example, you can design your approach to return only records that match all conditions or to return records that match any condition. You can also provide a great deal more feeback to the user while the "filtering" is being run. (Since feedback tends to keep users from rebooting their machines in the middle of long operations, this is a good thing.)

It's a bit of work, but the flexibility gained by full control is very liberating and, in the long run, more powerful than the native features provided by the GUI.

Hope this helps...

-- Lance