Defining query w/ value of child

[Susan Avery]

Another question:

I frequently would like to define a query on a set with something like 'Give me all the parent records that have a child w/ a field value of 'x'. Normally ALpha 5 only allows the query expression to contain fields from the parent record. Can someone tell me what I'm missing? (For example, the parent record may be a family, the set may be children in the family, and I want 'all families with children more than 8 years old').

This type of query seems pretty basic, so I'm guessing I'm missing something fundamental here...

Thanks

[Tom Cone Jr]

RE: Defining query w/ value of child

Susan,

When working with forms and version 4 or 4.5 you will need to use the flattenquery() function or simply create a new set, with the tables inverted so that the original child is now parent and vice versa.

-- tom

[CALocklin]

RE: Defining query w/ value of child

To find info on the Flattenquery() function, read the documentation on ".Query_create()". The instructions for the Flattenquery() option are about half way down.

[JerryBrightbill]

RE: Defining query w/ value of child

Another method that works well on forms is query_by_form. This allows the use of any field on the form to create a query, even if the field references a child record field. Assume the form has a field with the object name "childname"

parentform.query_by_form()
parentform:childname.text = "is not blank"
parentform.query_run()

This will find all child records with an entry in the field and will work even if the field is hidden as long as query is allowed on the form. The object also must not be read only or disabled.

Jerry

[Tom Cone Jr]

RE: Defining query w/ value of child

Jerry, I'm not sure I agree with you.

The terminology is treacherous here, but there's a difference between:

a) searching the child records and returning only those parent records that are linked to matching child records; and

b) searching the child records and returning only those child records that match.

In the latter case all parent table records remain in scope. If they are not linked to a matching child record then there are no child records visible. If they are linked to a matching child record then the matching children are shown. This is what you are describing.

In the former case, only parent table records that are linked to matching child table records are in scope. This is what the first message in this thread was looking for, or so it seemed to me. It is this case where flattenquery or an inverted set is necessary.

-- tom

[Susan Avery]

RE: Defining query w/ value of child

Tom, you are correct that I want the result to be a group of parent records.

I haven't been able to find the documentation on flattenquery - I have the Version 3 Reference - am I missing something?

Another twist is that I might want to define a query based on both a child attribute and a parent attribute (child attribute - kids over 8; parent attribute - live in a certain state). Will flatten_query do this as well?

Thanks for the tips...

Susan

[Tom Cone Jr]

RE: Defining query w/ value of child

Susan, I think flattenquery() was added in vers 4 of Alpha Five. It may not be available in vers. 3.

Given the additional search criteria you may face, I recommend you create an inverted set, and use a 1 to 1 link. You'll find it very easy to work with. All fields in both tables are available to your query.

-- tom

[Susan Avery]

RE: Defining query w/ value of child

Tom -

I think I'm still missing something. I'm trying to use the Query feature from a browse, or an independently defined query. I have an inverted set, but it is not 1-1 - since the original set in 1-many, this set is many-one.

Although Alpha5 gives me the option to use a field from the child in this case (the parent from the original set)when I perform the query I get 'no records found'. I have tried very simple queries to make sure I'm not making a typo or logical error in the query conditional.

Also, I have version 4.5, but only the documentation from ver. 3 - is there more available? (Or should I just wait for version 5).

[Tom Cone Jr]

RE: Defining query w/ value of child

Susan,

If your original set was one to many, when you create a new set and invert the tables, the original child becomes parent, and the original parent becomes child. In the new set the link must be defined as one to one, do not use one to many in defining the link in the inverted set.

The effect of a one to one link in the new set is, of course, that many parent records are linked to the same child record... but the link itself, from parent table to child table is one to one, because each parent record is linked to only one child record.

Confusing, I know, but that's the way it is.

If you're having trouble framing the query in a one to one set, perhaps you should post an example database here, or feel free to zip it and email it to me, so long as you include a text file explaining the specifics of what you are seeking to do.

-- tom

[Susan Avery]

RE: Defining query w/ value of child

Tom - I used an existing inverse set, and it turns out I defined it incorrectly...
Once I fixed that, it works just fine.

Thanks to you and everyone else - I have some new tools to play with now.

Susan

[Tom Cone Jr]

RE: Defining query w/ value of child

Susan, no problem. Been there. Done that. Have the tee-shirt. Glad it worked out. -- tom

[forskare]

RE: Defining query w/ value of child

Hi Susan,

There is documentation for v4 and 4.5. Its a matter of how you install it. There are boxes to check during installation that allows you to install the User's Giuide and the Xbasic Reference Manual.

kenn

[French]

RE: Defining query w/ value of child

...or you can simply insert the A5 CD in the drive and find the .pdf or .doc files (same documentation, once in Adobe Acrobat format, once in MS Word format) and either use them from the CD or copy them to your hard drive for future access. Sorry, bad word. Replace "access" with "availability".