Alpha Video Training
Results 1 to 11 of 11

Thread: Slow List Array

  1. #1
    MIKE LYON
    Guest

    Default Slow List Array

    Hi,

    I am trying to set up a lookup / list array which looksup a supplier table based on the users selection and show all the unique ranges available in that table.

    I have tried using a conditional lookup i.e if 'Qil_Supplier' field says '1' use 1.dbf if '2' use table 2.dbf etc but it also needs to take out any duplicates in the lookup for the field 'range'. The lookup only needs to show one field in it, the ranges available, but as each table has approx 68,000 records in them showing all the different ranges (43 in all)
    about 1600 times (not good). I need to only show each different range once and select that range to go into a single seperate field for later use.

    After failing to find a way to filter out the duplicates in the lookup I then tried a UI_GET_LIST_ARRAY set up on the on arrive of a field on a form please see code below.
    ------------------------------------------------------------
    dim number as n
    dim shared response as c
    dim ranges as c
    dim rangesselect as c
    dim LISTBOX(200) as c
    dim LIST as n
    dim FLAG as n
    FLAG = 0
    LIST = 1
    tbl = table.current()
    If ISBLANK("tbl.Qil_Supplier") then
    ui_msg_box("ATTENTION","Please select SUPPLIER first",48)
    goto end
    Else
    End if
    table_name = UT(tbl.Qil_Supplier)
    tblranges = table.open(table_name, file_ro_shared)
    tblranges.fetch_first()
    number = tblranges.records_get()
    LISTBOX(1) = tblranges.range
    FOR COUNT = 1 to number
    rangesselect = tblranges.range
    FOR IER = 1 to LIST
    If rangesselect = LISTBOX(IER) then
    FLAG = 1
    End If
    NEXT IER
    If FLAG = 0 then
    LIST = LIST + 1
    LISTBOX(LIST) = rangesselect
    End If
    FLAG = 0
    tblranges.fetch_next()
    NEXT COUNT
    tblranges.close()
    response = ui_get_list_array("Please select a range", 1, "LISTBOX")
    tbl.Qil_Range = response
    tbl.Qil_Range.resynch()
    if response = "" then
    goto end
    Else
    end if
    end
    end:
    end
    ------------------------------------------------------------

    This does what I want except - yes you have guessed it - it takes at least 5mins to run through the table and A5 halt appears locking up the form which is not acceptable.

    Help - Is there a simple method via a filtered lookup or is my script not efficient.

    Many thanks for all who try.

    Mike Lyon



  2. #2
    "Certified" Alphaholic forskare's Avatar
    Real Name
    Ken Nordin
    Join Date
    Apr 2000
    Location
    Walker, MN
    Posts
    7,545

    Default RE: Slow List Array

    Mike,

    I did a search on "filtered lookup" and cme up with 125 hits. I only looked at one in depth but don't nkow if it's something you're looking for or not but try a search and check out:

    RE: Filter Lookups - Glen Lillo

    HTMLThe article I am referring to is the one titled Filtered Lookups (version 3) dated 61198. Here is the address http://www.learnalpha.com/filtered/filtered.htm. The database and code I am using
    Date: 04-22-2002 3:10 PM

    There may be other interesting infop for you there as well.

    kenn

  3. #3
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,300

    Default RE: Slow List Array

    Mike,

    I don't have much experience with databases as large as you face, but if it were me, I'd try maintaining separate lookup tables for each desired range, each with its own index. I know this may violate database normalization principles, since the same data may be present in multiple tables, but it would make the lookup lightning quick. Disk space is cheap.

    An alternative would be to preload all the arrays, and keep them active throughout the current session, by declaring them global. There would be no need to rebuild the lookup list array with each new transaction, since they'd already be present in memory. Depending on the number and size of these arrays you may find yourself exhausting local resources, but memory is cheap, too.

    -- tom

  4. #4
    MIKE LYON
    Guest

    Default RE: Slow List Array

    Thanks ken and tom for your replies as always,

    Unfortunately all the lookup filtered ideas listed that I have looked at to date do not seem to go far enough or are no quicker than current thinking - thanks anyway Ken.

    Tom - Your first idea I think would be fine except that currently I am only working on one catalogue with 43 ranges and 16000+ items per range, BUT (yep theres always one) I hope to add numerous catalogues (20+) to this application which makes me worry how the set would cope with these let alone 20 cats X 43+ ranges.

    Your second idea about a open array sounds great - but then you lose me (could be brain fade after trying to sort problem). How would I do this and surely it would still lock up with A5 halt as before??
    Bearing in mind that the catalogues change every so often with append updates and more than one catalogue may be used on a single quote.

    Sorry to put the problem back to you but I'm losing hair by the bucket load here help!!

    Many thanks

    Mike Lyon

  5. #5
    MIKE LYON
    Guest

    Default RE: Slow List Array

    Tom,

    Just had another thought (oops brain might be switched on), could I generate a seperate table for lookup from all the catalogue tables that just shows the 'suppliers' and the
    'ranges' and then a delete duplicate record operation leaving just the unique ranges and their suppliers which I can then use to lookup the range I want?
    The join etc operations only allow for 2 tables to 1 though????
    And it would have to be able to update itself each time there is a catalogue change.

    The two fields on the form select the supplier required and then the range required which is then used in a browse lookup as a selection/filter criteria so it will be quick with LQO.

    Tom I know you like challenges and you have not let me down yet mate so over to you (hehe).

    Mike Lyon

  6. #6
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,300

    Default RE: Slow List Array

    Mike,

    It's a common misconception but the lookup table does NOT need to be in the set used for data entry. In fact, they often work best when they are not. So having lots of potential lookup tables should not be a particular problem, other than getting a bit messy when a new entry needs to be added to several.

    -- tom

  7. #7
    Member
    Real Name
    Craig Schumacker
    Join Date
    May 2000
    Posts
    687

    Default RE: Slow List Array



    Mike:
    There may be more effective ways to create the
    array. Try this:
    Create an index called "range" for each
    lookup table. As an order expression, use the range field, ascending,
    unique.
    Replace the blue sections below with your form
    name.
    This script may, or may not work right off the
    bat, and the odds are good that it won't. I, or others, will help you
    refine it.
    Oh yeah, this goes on the OnArrive of the same
    field, and will only run in change or entry mode.
    Craig
    tbl = table.current()

    if tbl.mode_get() > 0

    If isblank("tbl.qil_supplier") then
    ui_msg_box("ATTENTION","Please select SUPPLIER first",48)
    :YOURFORMNAMEHERE:qil_supplier.activate()
    end
    End if

    table_name = alltrim(tbl.Qil_Supplier)
    a_tbl = table.open(table_name)

    indx = a_tbl.index_get("range")

    rec_count = indx.records_get()

    a_tbl.fetch_first()

    dim listbox(rec_count) as c

    for x = 1 to rec_count
    listbox(x) = a_tbl.range
    a_tbl.fetch_next()
    next x

    a_tbl.close()
    sort_array("listbox")

    response = ui_get_list_array("Please select a range", 1, "LISTBOX")

    if response = " "
    end
    end if

    tbl.qil_range = response
    :YOURFORMNAMEHERE:qil_range.refresh()

    end if

    end



  8. #8
    MIKE LYON
    Guest

    Default RE: Slow List Array

    Wow folks,

    At last it worked as Craig suggested changing my script so as to utilize a unique index first to cut down the records.

    The only change I had to do on the script was add in an
    index_primary_put() line to make it work (without it the array still showed duplicates).

    Heres the finished script on the onarrive for QIL_RANGE.

    tbl = table.current()
    if tbl.mode_get() > 0
    If isblank("tbl.qil_supplier") then
    ui_msg_box("ATTENTION","Please select SUPPLIER first",48)
    :Quote Item Catalogues:qil_supplier.activate()
    end
    End if
    table_name = alltrim(tbl.Qil_Supplier)
    a_tbl = table.open(table_name)
    indx = a_tbl.index_get("range")
    a_tbl.index_primary_put(indx)
    rec_count = indx.records_get()
    a_tbl.fetch_first()
    dim listbox(rec_count) as c
    for x = 1 to rec_count
    listbox(x) = a_tbl.range
    a_tbl.fetch_next()
    next x
    a_tbl.index_primary_put("code")
    a_tbl.close()
    sort_array("listbox")
    response = ui_get_list_array("Please select a range", 1, "LISTBOX")
    if response = " "
    end
    end if
    tbl.qil_range = response
    :Quote Item Catalogues:qil_range.refresh()
    end if
    end

    This works like lightning less than a second to list unique ranges from a table that has 67232 records in AMAZING THANKS CRAIG.

    TOM - I have also taken my tables off the set as you advised thanks mate (you learn something new everyday and sometimes it sticks).
    There is just one slight problem one of the tables
    only holds a few records all with the same range and so when it calls the array it comes up with an error message - invalid array size - if I change one range to something different it works fine (I take it an array only works on 2 or more?) Is this something I have to put up with or can I work around it somehow??

    Right just that and then I am ready for the next stage - using the selected range etc to filter the browse lookup.
    This should be fun.

    Thanks guys for your time so far.

    Mike Lyon

  9. #9
    Member
    Real Name
    Craig Schumacker
    Join Date
    May 2000
    Posts
    687

    Default RE: Slow List Array

    Mike:

    I would adjust the script to handle these three scenarios:

    rec_count = 0 then end

    rec_count = 1 then after the a_tbl.fetch_first() just load the one range into listbox(1)

    rec_count > 1 loads the array in the for next as above in your updated script.

    Glad it all works OK.

    Craig

  10. #10
    Member
    Real Name
    Craig Schumacker
    Join Date
    May 2000
    Posts
    687

    Default RE: Slow List Array

    Mike:

    You will also need to bypass the sort_array() if there is only one record in the array.

    Craig

  11. #11
    MIKE LYON
    Guest

    Default RE: Slow List Array

    Thanks again craig your a life saver.

    Mike Lyon

Similar Threads

  1. A5 localhost slow – is real world that slow
    By oldtony in forum Web Application Server v6
    Replies: 4
    Last Post: 07-20-2005, 09:38 AM
  2. Diff. between List & Record List Xdialog box?
    By Bob Pickle in forum Alpha Five Version 5
    Replies: 3
    Last Post: 12-21-2003, 09:52 AM
  3. X-Dialog Refresh Record-List List-Box
    By Louis Nickerson in forum Alpha Five Version 5
    Replies: 7
    Last Post: 02-20-2003, 07:11 AM
  4. useing a checkbox in a X dialog – record list-list
    By Daniel Weiss in forum Alpha Five Version 5
    Replies: 1
    Last Post: 12-09-2002, 01:49 PM
  5. Variable Array in Combo-List Box???
    By Geoffrey Hollander in forum Alpha Five Version 4
    Replies: 4
    Last Post: 10-01-2001, 09:08 AM

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
  •