Parent/Child querying by child field

[aporta]

Hello all,

I have a parent/child set that I need to query by a date field that is in the child table. I am able open and iterate through a set using a pointer to each table, as shown in the documentation, but am I not sure how to filter by a value of a field in the child table.

One alternative seems to be to open the parent and child tables, and then using tbl.relation_add to create a master/child relationship. When I invoke that function I get an error "Not an open index". The relevant code is below, executed against the Alpha's Sports demo database:

parenttbl = table.open("invoice_header.dbf")
childtbl = table.open("invoice_items.dbf")

child_index = childtbl.index_get("Invoice_Nu")
relation.link_type = LINK_MANY
relation.index_child = child_index
relation.order_parent = "INVOICE_NUMBER" 
relation.ref_integrity = 0
parenttbl.relation_add(childtbl)

The error happens at the parenttbl.relation_add invocation. I have tried different values for the argument in index_get but the error is always the same.

So, two questions:

1) What is the proper way to query a child table that is part of a set based on values in one of the child's fields?
2) What does "Not an open index" mean in this context?

Thanks,

Armando

[Ted Giles]

Re: Parent/Child querying by child field

In a Set, build the Query using the built in Query option but query the records at the Child table level. The automatic setting is Cross Level. Change that to the Child table.
Then, run the query and save it.
Then, change the query so the you can use a variable if you wish for a specific date.
This is the code which the Query builds using today's date in a "person/date" set;

dim filter as c
dim order as c
dim options as c
dim description as c
dim show_all_flag as L
dim layout_name as c
dim layout_type as c
filter = "date = ctod("23/01/2019")"
order = "recno()"
options = ""
description = "Saved Query: Q_Date"
show_all_flag = .f.   'user cannot turn off the query
layout_type = "browse" 'to open a form, set this to "form"
layout_name = ""   'This will open the default layout. You can also specify a named layout here
dim args as sql::arguments
if eval_valid("arguments") then 
	if typeof(arguments) = "P" then
		 args = arguments 
	end if 
end if 
a5_open_layout(layout_type,layout_name,"persondate.set",filter,order,description,options,show_all_flag,args)

[aporta]

Re: Parent/Child querying by child field

Thank you Ted, I'll experiment with that.

Armando