Searching a child Database from the Parent Form

[John White]

Can Anyone Help Please ????
I am trying to search a child database embedded in a parent form. Unfortunately I am not that familiar with the Xbasic language as yet and could use some assistance.

Thanks in advance

John White

[Tom Cone Jr]

RE: Searching a child Database from the Parent For

John,

This can be tricky.

There's a difference between finding a particular child table record among those linked to the current parent table record

and

finding a particular child table record whether or not it is linked to the current parent table record.

Which are you trying?

-- tom

[John White]

RE: Searching a child Database from the Parent For

Hi Tom,
I have a set with the header database which I call SectionsHeader with a child database which I call sectionsdetal.
The header contains the usual things like id no, section, signaturees etc.
The detail database contains items that are booked to that particular person and section. What I am trying to do Tom, is search in that child database for items associated with this entry or any other entry in the entire database. I hope this clarifies my problem Tom, if not I will forward a zip of the system.

Thanks again Tom

John

[Tom Cone Jr]

RE: Searching a child Database from the Parent For

John,

Here's a working example I've prepared for you.

It shows one way of doing what you desire in the context of a set using customer table as primary, and transactions as child. there are others.

-- tom

[John White]

RE: Searching a child Database from the Parent For

Hi Tom,
Thanks very much for the demo file which was very helpful but!!! The next problem I encountered was I have to search on a "Description" field in the child database which means that there may be more than a single word in this field. The file you kindly sent down I studied and made sense of but it will not return any records in my database, have you any suggestions.

Thanks Again Tom.

John White

[Tom Cone Jr]

RE: Searching a child Database from the Parent For

John,

I've extended the example to show one approach to such a problem.

Have fun dissecting the script.

Unzip the attachment to an empty folder.

-- tom

[John White]

RE: Searching a child Database from the Parent For

Hi Tom,
I have studied and dissected the file you sent me. I still have a problem though Tom. I have tried all that I know to rectify the problem (which is not a lot).
When I run the script in my program, I get an error message which reads :- OBJECT METHOD ERROR. A PARAMETER MAY BE INCORRECT OR MISSPELLED. The problem lies in the "Unmark" line I think. If you could come up eith an answer, it would be very much appreciated.
Also Tom, is it possible to search for the description in the child database which is related to a customer in the parent, bearing in mind there may be quite a few entries for the customer in the parent database.

Once again Tom, Thanks ever so much for all your time and help.

John White

[Tom Cone Jr]

RE: Searching a child Database from the Parent For

John,

Sorry, no. I've pushed my little example as far as I can.

To go further we need to see YOUR database, with step by step instructions on how to replicate the error you're getting. And, you need to furnish concrete examples of the kind of searches you want to run, and the kinds of data against which it will be run. It's time for specifics.

-- tom

[John White]

RE: Searching a child Database from the Parent For

Thanks for reply Tom, I will post the database tomorrow and hopefully you may be able to sort for me.

thank you

John White

[John White]

RE: Searching a child Database from the Parent For

Hi Tom,
Please find attached my programm I am having trouble with.
The for you need to run is "Sections DataEntry"
A brief description. A company has employees who withdraw items from the stores (The employees is the field "Signed By". These employees will have several entries on the "Header Form" of the database. The main objects they would take from the stores are generally tools and keys for different departments they do work for.
In the "Section Detail" database (child) is where the stock movement is performed and this is where I need to search for items by "Description".
So my sonario is that I need to search for Employees ("Signed by") and also search for a particular item by ("Description") in the child database. I hope this is clear enough Tom.

Thanks Again

John White

[Tom Cone Jr]

RE: Searching a child Database from the Parent For

John, Yes, it's getting clearer.

Give me a specific example.

You're describing what you want to do, but not furnishing an actual example I can shoot for.

Specify a search. Define it the way you would want to actually use it. Don't worry about trying to get the syntax right, just give me a real world example of a specific search you might want to run.

In the meantime, I'll be familiarizing myself with your file.

-- tom

[Tom Cone Jr]

RE: Searching a child Database from the Parent For

John,

Turn off 'cascade deletes and changes' in the set design for the Sections Set.

Then substitute this script for the Description button script in your form. Because of it's length you'll have to use the regular code editor instead of action scripting.

'dim global new_desc as C

' check mode of form.  Block script if enter or change is pending
mode = parent.mode_get()
if mode  "VIEW" then
			ui_msg_box("Oops","Form Must Be In VIEW MODE")
			end
end if


'unmark records in header table of set
:mark.run_silent ("UnmarkSection_Header")
'----------------------------------------------

' now step through the set, and mark any record that contains the search term in the Description 
' field of the Section_details table

tbl = table.current()
tbl2 = table.current(2)
tbl.index_primary_put("")
tbl.fetch_first()
srch_key = ui_get_text("Search Description Field For What Word","Enter The Search Term.")
tbl.batch_begin()
found_match = 0
while .not. tbl.fetch_eof()
	if *ANY(upper(tbl2.Description),Upper(srch_key)) then
		found_match = found_match + 1
		tbl.change_begin()
		tbl.mark()
		tbl.change_end(.t.)
	end if
	tbl.fetch_next() 
end while
tbl.batch_end()

if found_match = 0 then  'no matches were found
	ui_msg_box("Oops","The search term does not appear in the Description field of the Section_Details table")
	tbl.fetch_first()
	parentform.resynch()
	end   'end script, no need to run query, since no records were marked. 
end if

'--------------------------------------------
'Run Query to show only the marked records
'--------------------------------------------

tbl.fetch_first()
query.filter = "Marked()"
idx = tbl.query_create()


' no need to check size of query list just to see if it's empty
'if idx.records_get() = 0 then 
'	trace.writeln("oops, no records have been marked")
'	idx.drop()
'	tbl.index_primary_put("")   'record number sequence
'else
'	trace.writeln(str(idx.records_get())+" customer records were marked")
'end if

tbl.fetch_first()

parentform.resynch()

end

-- tom

[Tom Cone Jr]

RE: Searching a child Database from the Parent For

John,

One more thing. You haven't added the Unmark operation to your database yet. You'll need to create and save it. Use the same name that you are using your script.

Sorry for the multiple posts.

-- tom

[John White]

RE: Searching a child Database from the Parent For

Tom,
Thanks for the above script and help.
You asked for a specific example. Here it is :

I need to be able to search for Employee Name ("Signed By") in the Main Form Ex: "Alan Jaycock", he may have had a "digging Fork" from the stores ("Description" in the child database), but, he has supposed to return it to the stores after he has finished with it.
It is a method of checking to make sure it has been returned. I hope this makes sense to you Tom.
p.s. (No wonder I could not get the earlier script to work if I did not define the unmark operation)

Thanks again for your help Tom

All The Best.

John