Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Slow List Array

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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
    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
    TYVM :) kenn

    Knowing what you can achieve will not become reality until you imagine and explore.

    Comment


    • #3
      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

      Comment


      • #4
        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

        Comment


        • #5
          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

          Comment


          • #6
            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

            Comment


            • #7
              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


              Comment


              • #8
                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

                Comment


                • #9
                  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

                  Comment


                  • #10
                    RE: Slow List Array

                    Mike:

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

                    Craig

                    Comment


                    • #11
                      RE: Slow List Array

                      Thanks again craig your a life saver.

                      Mike Lyon

                      Comment

                      Working...
                      X