Alpha Video Training
Page 1 of 2 12 LastLast
Results 1 to 30 of 37

Thread: Progressive Lookup Function

  1. #1
    "Certified" Alphaholic
    Real Name
    jim chapman
    Join Date
    Apr 2000
    Posts
    1,779

    Default Progressive Lookup Function

    Hello,

    I put this in the newsletter this month (Oct 2005), but thought I'd post it here as well. This is a self contained progressive lookup function that is built for v6 and above. The function takes 4 arguments, the name of the lookup table, the name of an index based on the lookup table, the desired number of lines in the lookup control, and the desired width of the lookup control. So:

    vRecordNumberReturned = ProgressiveLkUp("customer","Lastname",20,40)

    Make sure your index key is in the format desired for the search string.

    This function should perform well on very large tables and/or over a network.

    Jim

  2. #2
    Member ChrisHawkes's Avatar
    Real Name
    Chris Hawkes
    Join Date
    Apr 2000
    Location
    Dorset, UK
    Posts
    295

    Default

    Jim

    An excellent function - Thank you

    A nice extra feature would be to pass an 'A' or 'D' for Ascending or Descending.

    I shall be using this function myself in an upcoming application where a lot of lookups will be required.

    Chris

  3. #3
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default

    Jim is of course more qualified to answer your request but if you will permit an observation...


    If you want the choice of ascending or descending sort, you could create an index for each and pass the appropriate tag name to the function.

  4. #4
    "Certified" Alphaholic
    Real Name
    jim chapman
    Join Date
    Apr 2000
    Posts
    1,779

    Default

    Hello Chris,

    Thanks for the kind words. I appreciate it.

    Stan is right on the money as always. This could be done 'on the fly' switching the 'same' instance of the Xdialog box back and forth.

    There are a couple things I would have liked to include in this routine:
    1) the ability to switch to various search criteria, on the fly. ie: change from a name search to an address search, etc

    2) I wanted to put the visual list box in a multi-column format for things like last, first name search. I think this would look a little nicer but would necessitate having 'parallel' arrays and or lists to handle the visual display versus the actual key value.

    But this is a freebie and I wanted to get it out this month as we didn't have a ton of content for the newsletter. Hopefully the Alpha community will pick this up, improve and share it back.

    Thanks again for the kind words. Let me know how it works for you. I'm especially interested to hear how it works with large tables and over less that stellar networks.

    Thanks,
    Jim

  5. #5
    Member
    Real Name
    Don Bartlett
    Join Date
    Apr 2006
    Posts
    8

    Default Re: Progressive Lookup Function

    Just a note to say thanks for a great function. I would say I am in the 'beginner/intermediate' class when it comes to xbasic and xdialog so modifying your code is a bit out of my depth without a little help. If you have time,
    could you give us a few pointers on how to set things up so that searches could be done on different fields ie: name, address etc.

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

    Default Re: Progressive Lookup Function

    Copy and paste the function. Then, edit it by changing the field names, etc. to reflect the new search field.

    kenn

  7. #7
    Member Alan Lucas's Avatar
    Real Name
    Alan Lucas
    Join Date
    Nov 2002
    Posts
    686

    Default Re: Progressive Lookup Function

    I looked at Jim's code and cannot identify where the field names are that need to be change. Everything seems to start with a v which I take to be a variable? Could you please identify in the code where the fields are that need to be changed. I'm sure that I'm not reading the code correctly!

    Thanks

    Alan

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

    Default Re: Progressive Lookup Function

    Jim's code works by setting the primary index of the table here
    Code:
    t = table.open(vTableName,FILE_RO_SHARED)
    vIndex = t.index_primary_put(vIndexName)
    as the value in the type in field changes t.fetch_find(vTypeIn) is used to find the closest matching record using the index. then a list is built using the next number of records you have specified in the function parameter "vNumberOfLines AS N".

    So, if you want different values listed just change the index specified in the function parameter "vIndexName AS C"

    The values in the list are the values in the index not a particular field so construct an index that will display as you want. Like: alltim(Lastname) + ", " + alltrim(Firstname) would give you a list containg values in the form of

    Kiebert, Tim
    Lucas, Alan

    Don't forget that the function returns the record number.
    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.

  9. #9
    Member
    Real Name
    Don Bartlett
    Join Date
    Apr 2006
    Posts
    8

    Default Re: Progressive Lookup Function

    Thank you Kenn, Alan and Tim for your suggestions.
    I understand the concept of changing the arguments passed to the function 'hard coded' within a script so to speak. I was wondering if there was any way of changing them 'on the fly' ie:once the lookup was already open.
    My appologies for not making my question more specific; your replies
    have been very helpful, I'm going to rethink my approach to the problem.

    Don

  10. #10
    Member
    Real Name
    Don Bartlett
    Join Date
    Apr 2006
    Posts
    8

    Default Re: Progressive Lookup Function

    Thank you Kenn, Alan and Tim for your suggestions.
    I understand the concept of changing the arguments passed to the function 'hard coded' within a script so to speak. I was wondering if there was any way of changing them 'on the fly' ie:once the lookup was already open.
    My apologies for not making my question more specific; your replies
    have been very helpful, I'm going to rethink my approach to the problem.

    Don

  11. #11
    "Certified" Alphaholic
    Real Name
    jim chapman
    Join Date
    Apr 2000
    Posts
    1,779

    Default Re: Progressive Lookup Function

    Hello Don,

    I was wondering if there was any way of changing them 'on the fly'
    There are several ways to achieve this. For instance, if you put a row of buttons that corresponded to the different searches you'd like to accomplish, lets say: <Last Name> <Company Name> <State>
    When the user presses a button, you'd change the value held in the vIndex variable to the desired index, blank out the 'search for' control, and re-build the list based on the new index.

    Another way to accomplish this would be to use a modeless Xdialog display. One of the great things about Xdialog is that the construct of the display is simply a text string passed into the function ( ui_dlg_box() and ui_modeless_dlg_box() ). By using the same name for a modeless Xdialog display you can dynamically change the Xdialog on the fly.

    This is a simple example but once you grasp this, then changing the field (index) for the progressive lookup is done by calling the same modeless xDialog box with different parameters.

    Code:
    vdlg = <<%dlg%
    This is Text one;
    The first display;
    {line=1};
    <Switch> <Close>
    %dlg%
    
    vdlg2 = <<%dlg%
    This is Text two;
    The second display;
    {line=1};
    <Switch> <Close>
    %dlg%
    
    vCode = <<%code%
    if a_dlg_button = "Switch"
    	a_dlg_button = ""
    	if WhichVersion = "First"
    		WhichVersion = "Second"
    		ui_modeless_dlg_box("Test",vdlg2,vCode)
    		else
    		WhichVersion = "First"
    		ui_modeless_dlg_box("Test",vdlg,vCode)
    	end if
    end if 
    if a_dlg_button = "Close"
    	ui_modeless_dlg_close("Test")
    end if 
    %code%
    
    dim WhichVersion as c
    WhichVersion = "First"
    
    ui_modeless_dlg_box("Test",vdlg,vCode)
    If you don't want to use the first two approaches you could also achieve the same effect by programically closing one modal Xdialog box and instantly open another 'identical' one with the different parameters. There would probably be a slight screen flicker as the next list was build, but it should be acceptable.

    Good luck,
    Jim

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

    Default Re: Progressive Lookup Function

    Yes, the "v" usually indicates a variable. Open alphaSports and make note of the table and fields that contain the basic info such as the customer name, etc. Study the code and identify the variables; write down the names. compare the names of the fields and the 'Customer' table to the code.

    Next, change the variable names, the table name and field names to reflect your table name, etc. If you don't want, a particular field, delete the code to which it refers. If you want to add a field, simply add the code by copying the part of the code dealing with the field and change the names.

    I believe you will also need to create an index for each search field. I may have missed something but jump in and paly with it. That is the best way to learn.

    kenn

  13. #13
    Member Alan Lucas's Avatar
    Real Name
    Alan Lucas
    Join Date
    Nov 2002
    Posts
    686

    Default Re: Progressive Lookup Function

    Thanks Ken,

    Good sound advice, I will play with it and conquer.

    Alan

  14. #14
    "Certified" Alphaholic
    Real Name
    Richard Coleman
    Join Date
    Oct 2000
    Location
    Franklin, TN
    Posts
    1,206

    Default Re: Progressive Lookup Function

    I have put the 'new' progressive lookup into several apps and am beginning to find some anomolies.....

    Mind you, these may be of my own doing and not a problem with the function.

    Using the old 'Record List - List Box' method, the last name typed would remain at the top of the search box whenever a new record was initiated -- even if I had to leave the data entry form and create a new customer record so that I could enter the sales transaction (known hereafter as tx).

    Maybe a little 'splanation' will help.
    Entering sales txs, the new record button does a search for name using the function --- customer not in table.
    Now the entire or partial name that was typed in for the search is still present in the entry area when I cancel the sales tx (ergo cancel the new record entry). Then press a button on the tx form to go to the 'new customer - modal form'. After completing new cus entry, return to tx form and press 'new record'.... the name is still in the entry area and the lookup has gone to the new customer record.....

    with the new function, the entry area has been cleared... probably rightly so..

    It's not a problem and the lookup works just fine -- just have to retype some data.

    The other 'anomoly' is that after several entries, I seem to be losing the ability to use the up and down arrows to move the cursor.... Cursor movement was a really great improvement in the new function....

    Noticed something in the AlphaSports Explained chm when looking at some of the functions for the 'property sheet'. Apparently Selwyn feels that a closing statement for the main function (when internal functions are present) has got to be specific to the main function..

    At least that's what it looks like to me.... The function definition includes :
    ( 1) and the main function close is : end function ( 1)------------------

    My mind is not at Selwyn's level so I really have no idea what it means, it just looks like a close specific to the main function

    My only concern is the loss of the cursor movement using the keyboard, but the world will continue with or without it.

    D

  15. #15
    Member Alan Lucas's Avatar
    Real Name
    Alan Lucas
    Join Date
    Nov 2002
    Posts
    686

    Default Re: Progressive Lookup Function

    Had a problem with the build list in the progressive look up it was not displaying the last item in the list. Tom Cone finally solved the issue by commenting out part of BuildList section of the code. The full code function is included below.

    Thank you Tom.

    Alan


    [CODE]
    'Date Created: 22-Sep-2005 08:51:46 PM
    'Last Updated: 02-Aug-2007 04:27:49 PM
    'Created By : Jim Chapman
    'Updated By : Tom
    FUNCTION ProgressiveLkUp AS N (vTableName AS C, vIndexName AS C, vNumberOfLines AS N, vWidth AS N )

    dim shared pos as n 'The position of the scroll bar
    dim shared vList as c 'The list variable that is displayed in the control
    dim shared vSelect as c 'The 'record' selected by the user
    dim shared vTypeIn as c 'The variable that contains the 'search for' string the user types in

    pos = 0
    vSelect = ""
    vTypeIn = ""
    t = table.open(vTableName,FILE_RO_SHARED)
    vIndex = t.index_primary_put(vIndexName)
    t.fetch_first()
    vFirstRecordNumber = t.recno()

    vList = ""
    vList = BuildList(t,vIndex,vNumberOfLines)
    dim shared vpgdn as n

    vDlg =vWidth],[varN->vNumberOfLines]vSelect^#vList!vList_*]{vscroll=%0,[varN->vNumberOfLines+1]%3,[varN->vNumberOfLines]pos!pos_Changed};

    %dlg%

    vDlg = replace_parameters(vDlg,local_variables())


    vDlgReturn = ui_dlg_box("Look Up",vDlg, line_count(vList)
    GetCurrentValues("PageDown",line_count(vList))
    else
    GetCurrentValues("PageDown",vNumberOfLines)
    end if
    case else
    vList = BuildList(t,vIndex,vNumberOfLines)
    GetCurrentValues("PageDown",1)
    end select
    end if

    if a_dlg_button = "pgup"
    a_dlg_button = ""
    select
    case pos > 1
    GetCurrentValues("PageUp",1)
    case else
    for i = 1 to (vNumberOfLines + line_count(vList))
    if t.bof(vIndexName)
    exit for
    end if
    t.fetch_prev()
    next
    vList = BuildList(t,vIndex,vNumberOfLines)
    GetCurrentValues("PageUp",1)
    end select
    end if

    if a_dlg_button = "vList_Change"
    a_dlg_button = ""
    vCurrentLine = word_number_get(vList,vSelect,crlf())
    vReturnString = GetCurrentValues("vList_Changed",vCurrentLine)
    end if
    if left(a_dlg_button,6) = "vList_"
    a_dlg_button = ""
    end if
    %code%)
    t.close()
    vRecNumber = val(vSelect)
    select
    case vDlgReturn "OK"
    ProgressiveLkUp = -1
    case vRecNumber > 0
    ProgressiveLkUp = vRecNumber
    case else
    ProgressiveLkUp = -1
    end select

    END FUNCTION


    function GetCurrentValues as C (vWhatFired as c,vPositionNum as n)
    vCurrentString = word(vList,vPositionNum,crlf())
    '*****************************************************************************
    '*** Uncommenting the following lines will cause the type in control to reflect
    '*** highlighted selection when using page and arrow keys, mouse and scroll bar
    '*****************************************************************************
    'if vWhatFired "TypeInChanged"
    ' vTypeIn = alltrim(substr(vCurrentString,at("}",vCurrentString)+1))
    'end if
    vTempLen = at("}",vCurrentString) - 7
    vSelect = substr(vCurrentString,7,vTempLen)
    pos = vPositionNum
    GetCurrentValues = "T"
    end function

    function BuildList as c (vTable as p,vIndex as p,vCount as n)
    BuildList = ""
    ' for i = 1 to vCount
    ' vKey = vIndex.key_get()
    ' BuildList = BuildList + "{data="+padl(alltrim(str(vKey.record)),10,"0")+"}"+ vKey.key + crlf()
    ' vTable.fetch_next()
    ' if vTable.eof(vIndex.name_get())
    ' exit for

  16. #16
    Member Alan Lucas's Avatar
    Real Name
    Alan Lucas
    Join Date
    Nov 2002
    Posts
    686

    Default Re: Progressive Lookup Function

    It appears that some of the code in my previous message was cut off?

    The full code is now in the attached text file.

    Alan

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

    Default Re: Progressive Lookup Function

    Jim

    I have studied your progressive lookup threads, and feel your approach could give me some answers in a very tricky problem i posted yesterday.

    Basically I have to search for the occurrence of hundreds of key words, within a memo field.

    I documented my approach and problem here, and would be in great debt to you for any suggestions and or ideas that could help.

    cheers
    keven

    http://msgboard.alphasoftware.com/al...ad.php?t=69365

  18. #18
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,628

    Default Re: Progressive Lookup Function

    Quote Originally Posted by Alan Lucas View Post

    The full code is now in the attached text file.

    Alan
    Here is a correction to the correction to keep this function as fast as possible...

    Since the variable i wasn't being changed with by the increment_value() function, the entire table was being scanned. Not a problem with a small number of records, but on larger numbers of records it's crippling.

    Code:
         i = 1
        while i <= vCount .and. .not. vTable.fetch_eof()
            vKey = vIndex.key_get()
            BuildList = BuildList + "{data="+padl(alltrim(str(vKey.record)),10,"0")+"}"+ vKey.key + crlf() 
            vTable.fetch_next()       
    '        increment_value(i)  ' old code   
            i=increment_value(i) ' change of old code
        end while
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  19. #19
    "Certified" Alphaholic
    Real Name
    Richard Coleman
    Join Date
    Oct 2000
    Location
    Franklin, TN
    Posts
    1,206

    Default Re: Progressive Lookup Function

    Has anyone noticed that the latest mods to the Prog Lkup now give a display box with two (2) vertical scroll bars and when scrolling with the most right hand one, you can freeze up the display box????

    I have no idea how to fix it or I would and then post it...

    D

  20. #20
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,628

    Default Re: Progressive Lookup Function

    D

    I see that but haven't had the freeze.

    Do you have the mod that I added?

    Also, what I see in those 2 scroll bars is an interesting situation. Given the way this function works, the inside scroll bar scrolls through records already retrieved and the outer scroll bar scrolls through the entire table.

    I can see where not having the mod that I added might cause a 'freeze' - or slow response in a larger table.

    With what mod did you see this double scroll bar appear?

    Thanks for sharing...
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  21. #21
    "Certified" Alphaholic
    Real Name
    Richard Coleman
    Join Date
    Oct 2000
    Location
    Franklin, TN
    Posts
    1,206

    Default Re: Progressive Lookup Function

    Al,
    I am using the latest revision of the prog lkup, with your last mod.
    I cannot duplicate the problem. It appeared ONLY in 4 very small lookup tables containing numeric data only. The problem occurred whenever I would hold the down arrow (for the table, not the visible data in the box) and go past EOF on these small tables. Also, the last record lookup data would sometimes repeat if the table data did not completely fill the dialog box.
    Using larger tables seems to 'overcome' the problem. The dialog box will still go 'blank' when passing EOF using the outside down arrow; but pressing the outside up arrow will bring back correct 'active' data.
    Not sure there is a problem, and not sure I like the second scroll bar (YET).

    thanx for your response and hope to see you again at the next conference.

    D

  22. #22
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,628

    Default Re: Progressive Lookup Function

    Quote Originally Posted by dik_coleman View Post
    The problem occurred whenever I would hold the down arrow (for the table, not the visible data in the box) and go past EOF on these small tables.
    Like my doctor told me - don't do that... :p

    Quote Originally Posted by dik_coleman View Post
    Also, the last record lookup data would sometimes repeat if the table data did not completely fill the dialog box.
    Can you send me your tables and code so I can look at it?
    I can send you a link to upload or you can email it directly.

    Quote Originally Posted by dik_coleman View Post
    thanx for your response and hope to see you again at the next conference.

    D
    God willing and a fair wind, we'll be at the conference again.
    It was good to see you at this year's conference and hopefully I'll see you and many others at the next one...
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  23. #23
    "Certified" Alphaholic
    Real Name
    Richard Coleman
    Join Date
    Oct 2000
    Location
    Franklin, TN
    Posts
    1,206

    Default Re: Progressive Lookup Function

    ooops here I go again ---

    It may only be me, but would someone look at the latest 'hot' version of this lookup and tell me where <Cancel> is handled on the initial lookup screen.
    Not the form that may be used to display the data, the actual dialog box that contains the names, etc.....

    I can't find it anywhere...... I would like to be able to completely cancel the entire operation from that <Cancel> button, close the display form and go back to the 'whatever' that called the lookup.

    Any help is appreciated.
    D

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

    Default Re: Progressive Lookup Function

    D,

    The cancel button is not handled explicitly. The default behavior of the xdialog stores "Cancel" in a_dlg_button and closes the xdialog. The following section of code assigns the character string "-1" to the return value from the function (not the dialog) since vDlgReturn will not equal "OK".

    Code:
    select
       case vDlgReturn <> "OK"
       	  ProgressiveLkUp = -1
       case vRecNumber > 0
       	  ProgressiveLkUp = vRecNumber
       case else
       	  ProgressiveLkUp = -1
    end select
    If you get a "-1" character string return from the function it means that either the user pressed Cancel button or (having first selected a row) the user has pressed the Esc key.

    Read the help file discussion of Xdialog events for details on the default behavior. You will find that pushing any button will assign the name of that button to a_dlg_button. Since a_dlg_button is not empty the dialog will close unless you trap the event in code and assign null to a_dlg_button to keep the xdialog open.

  25. #25
    "Certified" Alphaholic
    Real Name
    Richard Coleman
    Join Date
    Oct 2000
    Location
    Franklin, TN
    Posts
    1,206

    Default Re: Progressive Lookup Function

    Thanx Tom...
    That is exactly what I found out over the past hour.
    by creating a variable to carry out a value after getting the minus one, I was able to stop all further action and return to the form that started the script...

    Sorry to be so dense sometimes..... Me like turtle -- I get there eventually

    Thanx again
    D

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

    Default Re: Progressive Lookup Function

    Recommended changes (for Alpha Five vers 8) to Jim's script are discussed in the following thread:

    http://msgboard.alphasoftware.com/al...ad.php?t=71983

  27. #27
    Member
    Real Name
    Michael Payton
    Join Date
    Dec 2007
    Posts
    294

    Default Re: Progressive Lookup Function

    I'm a little lost here... with all the mods, or not, mods, or not... that have gone on with this function. Anyone have any idea where the "latest/greatest" code for this can be found... if anywhere... out here :-)

    Thanks,
    "all things should be as simple as possible... but no simpler"

    Mike

  28. #28
    "Certified" Alphaholic
    Real Name
    Richard Coleman
    Join Date
    Oct 2000
    Location
    Franklin, TN
    Posts
    1,206

    Default Re: Progressive Lookup Function

    This is my 'latest' version. It works!

    these are the variables passed along to the function.

    the index 'lkup4' is an index on the table that organizes last and first names of husband and wife into the following: Coleman, Richard or Ann.
    It also allows for only one spouse and formats the index accordingly.
    The record chosen is displayed on a form. Data is transferred from the lookup
    to shared and global variables that facilitate data input to a form.


    Code:
    vtblname="c_custbl"
    vindxname="lkup4"
    vnumlines=25
    vWidth=45
    
    vrecnum=PrgLkup(vtblname, vindxname, vnumlines, vWidth)
    
    if vRecNum > 0 then
    	:fc_cusok.Recno_goto(vRecNum)
    	 ' original line in script
    end if
    this is the function PrgLkup:

    Code:
    'Date Created: 22-Sep-2005 08:51:46 PM
    'Last Updated: 14-Dec-2008 08:43:13 AM
    'Created By  : Jim Chapman
    'Updated By  : Dick Coleman
    
    FUNCTION PrgLkup AS N (vtblname AS C, vindxname AS C, vnumlines AS N, vWidth AS N )
    
    dim shared pos as N  'The position of the scroll bar
    dim shared vList as C 'The list variable that is displayed in the control
    dim shared vSelect as C 'The 'record' selected by the user
    dim shared vTypeIn as C  'The variable that contains the 'search for' string the user types in
    dim shared vfstrecnum as N 'The variable that contains the first record number
    dim shared getoutnow as L = .F.
    
    pos = 0
    vSelect = ""
    vTypeIn = ""
    t = table.open(vtblname,FILE_RO_SHARED)
    vIndex = t.index_primary_put(vindxname)
    t.fetch_first()
    vfstrecnum = t.recno()
    
    vList = ""
    vList = BuildList(t,vIndex,vnumlines)
    dim shared vpgdn as n
    
    vDlg =<<%dlg%
    {position=remember=cuslkup1}
    {on_key=PGUP!pgup}
    {on_key=PGDN!pgdn}
    {on_key=Down!Down}
    {on_key=Up!Up}
    {on_key=ENTER!EnterKey}
    Type in Query Description:;
    [.[varN->vWidth]vTypeIn!changed];;
    [%O={@@}%.[varN->vWidth],[varN->vnumlines]vSelect^#vList!vList_*]{vscroll=%0,[varN->vnumlines+1]%3,[varN->vnumlines]pos!pos_Changed};
    <10OK> <10Cancel>
    %dlg%
    
    vDlg = replace_parameters(vDlg,local_variables())
    
    vDlgReturn = ui_dlg_box("Input Search String",vDlg,<<%code%
    
    if a_dlg_button = "Cancel"
    	getoutnow=.T.
    	goto inside_end
    end if
    
    if a_dlg_button = "vList_dblclick"
    	a_dlg_button = "OK"
    end if 
    if a_dlg_button = "EnterKey" .or. a_dlg_button = "OK"
    	select
    	   case vSelect = ""
    	   	a_dlg_button = ""
    	   	ui_msg_box("Oops","A lookup selection must be made or the Cancel button pushed!",UI_STOP_SYMBOL)
    	   case vSelect <> ""
    	   	a_dlg_button = "OK"
    	end select
    end if 
    if a_dlg_button = "changed"
    	a_dlg_button = ""
    	t.fetch_find(vTypeIn)
    	vList = BuildList(t,vIndex,vnumlines)
    	GetCurrentValues("TypeInChanged",1)
    end if 
    if a_dlg_button ="pos_changed" .or. a_dlg_button = "Up" .or. a_dlg_button = "Down"
    	select
    	   case a_dlg_button = "Down"
    	   pos = pos + 1
    	   case a_dlg_button = "Up"
    	   pos = pos - 1
    	end select
    	a_dlg_button = ""
    	select
    	   case pos > vnumlines
    	   	vList = BuildList(t,vIndex,vnumlines)
    	   	GetCurrentValues("Scroll_EOL",1)
    	   case pos = 0 .and. vfstrecnum = val(vSelect)
    	   	GetCurrentValues("At First Record",0)
    	   case pos = 0
    	   for i = 1 to vnumlines * 2
    	   		if t.bof(vindxname)
    				exit for
    			end if 
    	   	t.fetch_prev()
    	   	next 
    	   	vList = BuildList(t,vIndex,vnumlines)
    	   	GetCurrentValues("Scroll_BOL",vnumlines)
    	end select
    	vCurrentString = word(vList,pos,crlf())
    	vReturnString = GetCurrentValues("POS_Changed",pos)
    end if 
    if a_dlg_button = "pgdn"
    	a_dlg_button = ""
    	select
    	   case pos < vnumlines 
    	   if vnumlines > line_count(vList)
    	   	GetCurrentValues("PageDown",line_count(vList))
    	   	else
    	   	GetCurrentValues("PageDown",vnumlines)
    	   end if 
    	   case else
    	   	vList = BuildList(t,vIndex,vnumlines)
    	   	GetCurrentValues("PageDown",1)
    	end select
    end if 
    
    if a_dlg_button = "pgup"
    	a_dlg_button = ""
    	select
    	   case pos > 1
    	   	GetCurrentValues("PageUp",1)
    	   case else
    	   	for i = 1 to (vnumlines + line_count(vList))
    	   		if t.bof(vindxname)
    				exit for
    			end if 
    	   	t.fetch_prev()
    	   	next 
    	   	vList = BuildList(t,vIndex,vnumlines)
    	   	GetCurrentValues("PageUp",1)
    	end select
    end if 
    
    '_____________________________________________________________________
    'Code created by Selwyn Rabins due to bug report submitted.
    'this has fixed the loss of clicking on a name to pick it and have it displayed in pick box.
    	if atc("vlist_",a_dlg_button) = 1 then
    	
    	if a_dlg_button = "vList_Change"
    	dim pos as n
    	pos = atc("{data=" + alltrim(vselect),vlist)
    	vCurrentLine = word_from_pos(vlist,pos,crlf())
    	
    	vReturnString = GetCurrentValues("vList_Changed",vCurrentLine)
    	a_dlg_button = ""
    	else
    	a_dlg_button = ""
    	end if
    	end if 
    '_____________________________________________________________________
    'Original code below and commented out
    
    'if a_dlg_button = "vList_Change"
    '	a_dlg_button = ""
    '	vCurrentLine = word_number_get(vList,vSelect,crlf())
    '	vReturnString = GetCurrentValues("vList_Changed",vCurrentLine)
    'end if 
    'if left(a_dlg_button,6) = "vList_"
    '	a_dlg_button = ""
    'end if 
    '______________________________________________________________________
    
    inside_end:
    
    %code%)
    t.close()
    vrecnum = val(vSelect)
    select
       case vDlgReturn <> "OK"
       	PrgLkup = -1
       case vrecnum > 0
       	PrgLkup = vrecnum
       case else
       	PrgLkup = -1
    end select
    
    END FUNCTION
    
    
    function GetCurrentValues as C (vWhatFired as c,vPositionNum as n)
    	   	vCurrentString = word(vList,vPositionNum,crlf())
    	   	'*****************************************************************************
    	   	'*** Uncommenting the following lines will cause the type in control to reflect
    	   	'*** highlighted selection when using page and arrow keys, mouse and scroll bar
    	   	'*****************************************************************************
    	   	if vWhatFired <> "TypeInChanged"
    	   		vTypeIn = alltrim(substr(vCurrentString,at("}",vCurrentString)+1))
    	   	end if 
    	   	vTempLen = at("}",vCurrentString) - 7
    	   	vSelect = substr(vCurrentString,7,vTempLen)
    	   	pos = vPositionNum
    	GetCurrentValues = "T"
    end function 
    
    function BuildList as c (vTable as p,vIndex as p,vCount as n)
    	BuildList = ""
    	for i = 1 to vCount
    		vKey = vIndex.key_get()
    		BuildList = BuildList + "{data="+padl(alltrim(str(vKey.record)),10,"0")+"}"+ vKey.key + crlf() 		
    		if vTable.eof(vIndex.name_get())
    			exit for
    		end if
    		vTable.fetch_next() 
    	next
    end function
    I don't know if there are any 'other' versions.
    Hope it helps.
    D

  29. #29
    "Certified" Alphaholic
    Real Name
    Jeff Kletrovets
    Join Date
    Oct 2005
    Location
    Columbus, OH
    Posts
    1,135

    Default Re: Progressive Lookup Function

    Hi All,

    Thought I would post this here in case it might help someone else. I made a quick and dirty little function that calls Jim's Progressive Lookup function. But, this function will use the recno() returned by Jim's function and return whatever field you want for that record number. I wanted an easy way to have a one line function call to give me the data from the field I wanted. Instead of modifying Jim's function I decided to use it as is and just call it with this little function.

    You pass the source table, source index, return field, rows and columns.

    Code:
    'Date Created: 12-Mar-2009 05:00:37 PM
    'Last Updated: 12-Mar-2009 08:03:23 PM
    'Created By  : Jeff
    'Updated By  : Jeff
    FUNCTION PrgLkupRetVal AS C (srctable AS C, srcindex AS C, srcretfield AS C,lkuprows as N, lkupcolumns as N)
    'Call Jim's function
    	vrecnum=prglkup(srctable,srcindex,lkuprows,lkupcolumns)
    	
    	if vrecnum>0 ' a valid record was returned
    		dim tblsrc as p
    		tblsrc=table.open(srctable,FILE_RO_SHARED)
    		tblsrc.fetch_goto(vrecnum)
    		PrgLkupRetVal=alltrim(eval("tblsrc."+srcretfield))
    	             tblsrc.close()
    	else
    	PrgLkupRetVal=""
    	ui_msg_box("No Record Chosen","No Record was chosen - Process Terminating!")
    	end
    	end if
    END FUNCTION
    BTW, Thanks to Jim and all that have contributed to this thread. This is a great function to have.

    I have used a fixed width font (QuickType Mono) that I did put into Jim's dialog code to make columns line up perfectly when I use more than one field in my index for the lookup (actually they all have more than one field in them) - makes for a nice looking presentation to the user.

    Regards,

    Jeff

  30. #30
    Member
    Real Name
    John Tary
    Join Date
    Jan 2007
    Posts
    418

    Default Re: Progressive Lookup Function

    Would someone be so kind as to post a small database with this function working within it, thank you.

Similar Threads

  1. Progressive Lookup
    By julian lewis in forum Alpha Five Version 5
    Replies: 2
    Last Post: 06-30-2004, 03:28 PM
  2. Progressive lookup
    By Chris McConnell in forum Alpha Five Version 5
    Replies: 11
    Last Post: 04-04-2004, 07:14 AM
  3. Progressive lookup Value
    By forskare in forum Alpha Five Version 5
    Replies: 5
    Last Post: 02-22-2004, 04:33 AM
  4. Progressive Lookup
    By Bill Parker in forum Alpha Five Version 5
    Replies: 9
    Last Post: 01-13-2004, 09:18 AM
  5. Progressive Lookup
    By Louis Nickerson in forum Alpha Five Version 5
    Replies: 4
    Last Post: 09-29-2003, 08:46 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
  •