PDA

View Full Version : Copy database using cross level query, child table values found in parent memo field


ABC123

kthibeault1965
09-25-2007, 10:06 PM
This puzzle is a good one, but my solution is not working. Any help would be very much appreciated!!

I have two tables.

1 = product information table with product description MEMO fields: 4000 rows
2 = a keyword lookup table with keyword CHAR field:500 rows
The need is to

Find the occurance of ANY of the 500 keywords in the product information description memo field.
Then, copy the unique ID from both tables to a third table, creating a reference link between the two tables.

I tackled the problem this way.
I made a one to many SET out of both tables, parent table was the product table. I made a linking id field in both tables, filled all rows in both tables with "1". This means every product record is linked to every keyword record.

this way I can run a CROSS LEVEL query that looks like this

containsi(product_parent_table->prod_desc_memo,keywork_child_table->keyword_char)



Sample Data

Parent product table
Prod_ID Prod Description MEMO
111 red toy wagon with black wheels
222 red toy wagon with white wheels
333 red toy wagon with silver wheels

Child keyword table
Keywd_ID Keyword CHAR
aaa red
bbb black
ccc white

DESIRED OUTPUT

Prod_ID Keywd_ID
111 aaa
111 bbb
222 aaa
222 ccc
333 aaa

kthibeault1965
09-26-2007, 08:49 AM
by the way - I was also thinking I could write an xbasic code that would step through the keyword database one by one and somehow copy records out to a third resuld table. this would seem very slow I imagine.

A lot of views on this topic, any ideas anyone??

thanks a million

Tim Kiebert
09-26-2007, 10:03 AM
by the way - I was also thinking I could write an xbasic code that would step through the keyword database one by one and somehow copy records out to a third resuld table. this would seem very slow I imagine.

A lot of views on this topic, any ideas anyone??

thanks a millionPersonally I would go with the scripted method as you suggest. I was going to suggest it but you beat me to it.

The script would
open the three tables you want to work with.
loop through the keyword table
within the keyword loop loop through the product table checking the memo field for the current keyword
if a match then write a record to your third table.
Close three tablesCheckout the batch_begin() and batch_end() functions to optimise the loops.

I think it might be quicker than you think the record count you are talking about is not all that high.

Have a go at the script and if you get stuck, holler. Some one will come to your aid. If/when you do holler include what you have tried, either the script or a sample database.

Good luck.

kthibeault1965
09-26-2007, 10:55 AM
Tim

thanks for your thoughtful reply, I will upload a zip file of what I come up with.

btw, I was in Melbourne for 4 months last "summer", your summer, best of luck with the terrible drought! I was consulting to Telstra, my best friend is a marketing consultant who lives in M.

cheers

kthibeault1965
09-26-2007, 11:00 AM
I have attached a small sample db to illustrate what I am talking about.

the approach I have built today does work for small dbs, not for my size of 4000 parents and 2000 child records unfortunately.

Tim Kiebert
09-26-2007, 11:05 AM
Tim

thanks for your thoughtful reply, I will upload a zip file of what I come up with.

btw, I was in Melbourne for 4 months last "summer", your summer, best of luck with the terrible drought! I was consulting to Telstra, my best friend is a marketing consultant who lives in M.

cheersYour welcome.

We have had a bit of rain locally. Just enough to make the grass green and fill the water storages a little bit. However we are still on stage 4 water restrictions and inland Ie where our fruit comes from they're still doing it tough. Many farmers are pruning their trees with a bulldozer.:(

Mike Wilson
09-26-2007, 12:17 PM
Kevin,
Attached is your sample with the script Tim was describing. I had one for this already in my code library. I encourage you to work on this first and then look at the script keyword_probe, since you stated is was a good exercise, and is, and a good learning tool. The one hint I can give you that is not intuitive.... when you capture the keyword from the productkeyword table into a variable, you need to alltrim(), or trim() it for your query to the memo field in the productdescription table. I can't tell you how many times my query filters have failed ( and the frustration level skyrockets) because of this small and easily overlooked need.

Also, the documentation for batch_begin() will not come up under index search. You have to go to 'tables', and scroll down to 'batch'

Good luck.

Tim, Bulldozers... Arg! Hope you guys are OK

kthibeault1965
09-26-2007, 12:24 PM
thanks MIKE !!

speaking of frustration, when the key word is actually two words: "RED HAT", do you know how I get the query to ignore records with the descriptoin" RED BOOTS and BLACK HAT" in the search string? this is giving false positives.

and thanks again for your code.. i really really appreciate the help-

Keven Thibeault

martin horzempa
09-26-2007, 12:24 PM
Hi Kevin


i have done something like what you are
thinking but approached it a little differently

i needed to be able to search a product table(flower arrangements) for
1) a particular flower and/ or for a particular color
2) these text descriptions could be in any of 4 fields in the product table


i created queries for each individually - and saved the results to 2 tables
serachcolors and serachflowers then to use this data as the basis
of a search tool for the web i created a web component based on a VIEW of

products
-serachcolors
-serachflowers

link field productID

the results are here

http://www.jollygreenthumb.com/search.a5w

not elegant but it works for me and i only have a handful of flowers and colors
to search for

hth

Mike Wilson
09-26-2007, 01:01 PM
thanks MIKE !!

speaking of frustration, when the key word is actually two words: "RED HAT", do you know how I get the query to ignore records with the descriptoin" RED BOOTS and BLACK HAT" in the search string? this is giving false positives.

and thanks again for your code.. i really really appreciate the help-


Kevin,
Look into remspecial(). This strips out things so Van Buren = VanBuren, and O'conner = Oconner.

kthibeault1965
10-02-2007, 04:31 PM
Mike thanks - I actually need the space in this case.

"Not tested on animals" needs to be found in that form in the search field. removing the spaces would not work in that case.

Mike Wilson
10-02-2007, 05:12 PM
keven,
I am afraid your going to have to be a little more specific about what the problem with "Red Hat" and "Black Hat" is. Is it those specific words or descriptions with those or some specific words, or that there is more than a single word in the filter, or what? As specific as you can be.