XML Web Query on Related Table

[bdam]

I am looking to export data from a layout called webdata in a database called Books from our FM Server 11 Advanced using the XML functionality of the Web Publishing Engine. I have a layout which is based on the main Books table with all of the fields I need. What I'm looking to do is to get a list of in print book records. We have a seperate table which has mutliple records for each book to track when the statuses were active. This table includes a field to indicate that this is the current status and have verification set up to ensure that only one record per book is marked current. Using a table occurance of the status table I am able to filter down to just the current status for each book. The filtering is not based on global fields.

The problem I am having is that when I use the related field (Current_Status::print_status_code) in an XML query to find in print (IP) books it returns every record in the Books table. If I do this query using the FileMaker client it works just fine and returns the correct set of records. If I do a query based on fields in the Books table it works as expected. Here is the query with the server info removed: .../fmi/xml/fmresultset.xml?-db=Books&-lay=webdata&Current_Status::print_status_code=IP&-find

I also tried using a pre-find script that would peform the find in the script but again that gave me every record. I assume that's because the script has no relevance for the XML query.

Any ideas?

Bryan

[beverly]

Can you do the find from the "child" (related) table? You can alway pull parent databack.

Beverly

[bdam]

Unfortunatly no; in troubleshooting I whittled down the problem to be simple. Ultimately we need to create a find that uses fields from the parent and possibly multiple child tables.

[webko]

Then run them as nested queries - Find parent, then for each parent go to the required child table(s) to retrieve the data...

[bdam]

Unless I'm missing something, that doesn't solve the problem. I am looking to to perform a find in the parent table using fields from one or more child tables. So I can't find the parent and then go to the child tables because without the child tables I can't find the correct records in the parent table.