Cross-Level Queries

[Craig]

I have not been able to formulate (what should have been) a simple query expression to select parent records based upon their child records. I�m sure that there is a simple answer. The problem will be easier to describe using the AlphaSports application.

In the User Guide (p. 93-94) cross-level queries are described as filtering out records at the parent level of the set. �� using the invoice form in the sample AlphaSports database application, you can use a Cross-Level Query to answer the question, �show me all invoices that include �UnoFold Dry Socks� as a line item.�

This is simple enough and I am able to do the same thing with both AlphaSports as well as my own application. However, I want to answer a similar, but different question, �show me all invoices that exclude �UnoFold Dry Socks� as a line item.� In other words, I do not want to see any invoices (parent records) that include �UnoFold Dry Socks� in a line item.

Using the query genie, I checked the �Exclude records that satisfy this condition� box. This generated the following expression: .not.((("UnoFold" $ Product->Description)))

However, when I run this query all records are selected, including those that include �UnoFold Dry Socks�.

(Conversely, if I do not check the �exclude� box, only invoice #s 000013, 16, 18, 27, 28 that include �UnoFold Dry Socks� are displayed.) Curiously, when I use the same kind of expression with my own application, the �line items� that should be excluded are excluded, even though the parent records are still selected.)

So, either I have misunderstood how to use cross-level queries, or perhaps there is an alternate way to achieve the same result. In either case, I would appreciate any suggestions.

[Tom Cone Jr]

RE: Cross-Level Queries

Craig,

I have no inside information on how the cross-level query
functionality was implemented.

I have always assumed that what they do is to step through
the composite records for the set, examining the filter
criteria on each fetch. Every qualifying record would
be returned.

If my assumption is accurate when you run a query without
negating the filter, each composite record is examined, and
the only ones that are returned are those which match the
filter expression. None of the other composite records
meet the filter criteria so they are all excluded. This
makes no difference for the desired records since the one
matching occurrence is enough to include that primary table
record in the returned recordset.

If the filter expression is negated and the same sequence
is followed ALL parent table records will be included if they have any records that match the filter expression. i.e. if they have child table records with descriptions other than duofold socks. Since most all of the composite records will meet the filter criteria (they don't have duofold socks in the description field) they are all INCLUDED in the returned recordset.

The single composite record containing duofold
socks in the description is excluded, but the other composite records for the related primary table record are ALREADY included in the return recordset.

I think maybe you've stumbled into an area that hasn't been studied much before.

Unless anyone can offer a better idea, you might think about approaching this in two steps.

Step 1 mark all primary table records that have duofold socks in the description of a linked child table record

Step 2 filter only the unmarked records.

-- tom

[Craig]

RE: Cross-Level Queries

Tom,

Thanks very much. Your explanation is very plausible and your solution did the trick. However, I'd be even happier if there was only one step (so to speak) within the cross-level query genie and/or expression.

Craig

[Tom Cone Jr]

RE: Cross-Level Queries

Craig,

You're welcome.

You might try another approach, which will be a single step solution (if it works!).

Try defining your filter expression using the exclude option, but define your expression to use an indextag based on the description field... and the exist() function.

Your goal would be to exlude records from the query list where the invoice number matches the current parent table record AND the duofold socks appear (exist) in an index list for the child table.

-- tom

[Tom Cone Jr]

RE: Cross-Level Queries

Craig, I tried to implement my second idea using the Exist() function, and it won't work. Because the product_id field value for the duofold socks IS in the in index for the table, the filter expression returns true for all records... Should have seen that before suggesting. mea culpa.

-- tom