Alpha Video Training
Results 1 to 12 of 12

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

  1. #1
    Member kthibeault1965's Avatar
    Real Name
    Keven Thibeault
    Join Date
    Sep 2007
    Location
    Boston Mass USA
    Posts
    108

    Default Copy database using cross level query, child table values found in parent memo field

    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

  2. #2
    Member kthibeault1965's Avatar
    Real Name
    Keven Thibeault
    Join Date
    Sep 2007
    Location
    Boston Mass USA
    Posts
    108

    Default Re: Copy database using cross level query, child table values found in parent memo fi

    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

  3. #3
    "Certified" Alphaholic Tim Kiebert's Avatar
    Real Name
    Tim Kiebert
    Join Date
    Jul 2004
    Location
    Geelong, Victoria, Australia
    Posts
    2,785

    Default Re: Copy database using cross level query, child table values found in parent memo fi

    Quote Originally Posted by kthibeault1965 View Post
    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
    Personally 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 tables
    Checkout 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.
    Tim Kiebert
    Eagle Creek Citrus
    A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

  4. #4
    Member kthibeault1965's Avatar
    Real Name
    Keven Thibeault
    Join Date
    Sep 2007
    Location
    Boston Mass USA
    Posts
    108

    Default Re: Copy database using cross level query, child table values found in parent memo fi

    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

  5. #5
    Member kthibeault1965's Avatar
    Real Name
    Keven Thibeault
    Join Date
    Sep 2007
    Location
    Boston Mass USA
    Posts
    108

    Default Re: Copy database using cross level query, child table values found in parent memo fi

    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.

  6. #6
    "Certified" Alphaholic Tim Kiebert's Avatar
    Real Name
    Tim Kiebert
    Join Date
    Jul 2004
    Location
    Geelong, Victoria, Australia
    Posts
    2,785

    Default Re: Copy database using cross level query, child table values found in parent memo fi

    Quote Originally Posted by kthibeault1965 View Post
    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
    Your 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.:(
    Tim Kiebert
    Eagle Creek Citrus
    A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

  7. #7
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Michigan
    Posts
    4,163

    Default Re: Copy database using cross level query, child table values found in parent memo fi

    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
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

  8. #8
    Member kthibeault1965's Avatar
    Real Name
    Keven Thibeault
    Join Date
    Sep 2007
    Location
    Boston Mass USA
    Posts
    108

    Default Re: Copy database using cross level query, child table values found in parent memo fi

    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

  9. #9
    Member
    Real Name
    Martin Horzempa
    Join Date
    Oct 2005
    Posts
    224

    Default Re: Copy database using cross level query, child table values found in parent memo fi

    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

  10. #10
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Michigan
    Posts
    4,163

    Default Re: Copy database using cross level query, child table values found in parent memo fi

    Quote Originally Posted by kthibeault1965 View Post
    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.
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

  11. #11
    Member kthibeault1965's Avatar
    Real Name
    Keven Thibeault
    Join Date
    Sep 2007
    Location
    Boston Mass USA
    Posts
    108

    Default Re: Copy database using cross level query, child table values found in parent memo fi

    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.

  12. #12
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Michigan
    Posts
    4,163

    Default Re: Copy database using cross level query, child table values found in parent memo fi

    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.
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

Similar Threads

  1. passing field values from a parent to a child grid
    By finditqwik in forum Application Server Version 8
    Replies: 0
    Last Post: 03-28-2007, 11:30 AM
  2. faster query results than with cross level
    By Cheryl Lemire in forum Alpha Five Version 7
    Replies: 9
    Last Post: 09-29-2006, 08:43 PM
  3. copy field data from parent to child
    By Allen Krause in forum Alpha Five Version 6
    Replies: 12
    Last Post: 04-19-2005, 01:03 PM
  4. Concatenate memo field from child file to parent
    By Bob Flanagan in forum Alpha Five Version 6
    Replies: 1
    Last Post: 04-15-2005, 02:51 AM
  5. Parent table based on query of child table.
    By edward.mattison@scbhn.org in forum Alpha Five Version 5
    Replies: 1
    Last Post: 02-25-2005, 03:14 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •