Alpha Video Training
Results 1 to 9 of 9

Thread: Using Autosuggest across multiple tables

  1. #1
    Member
    Real Name
    tnt
    Join Date
    Oct 2005
    Posts
    18

    Default Using Autosuggest across multiple tables

    Hello All,

    I've taken the autosuggest example from the web applications demo and got it working nicely on one table in my database.

    What I'm trying to accomplish now is modifying the .ajax page to work across multiple tables.

    The xbasic code (taken from the .ajax page)

    ***********************************************
    <%a5

    dim action as c = default ""



    dim dbf_folder as c
    dbf_folder = _A5_Aliases.get("PathAlias.ADB_Path")
    DIM tableFilename as c
    tableFilename = a5_removetrailingbackslash(dbf_folder) + chr(92) + "compser.dbf"

    if action = "getserviceList" then
    dim service as c = default ""

    if service <> "" then
    dim tbl as p
    tbl = table.open(tableFilename)
    dim itbl as p
    itbl = tbl.order("serv","left(serv," + len(service)+")=" + s_quote(service))

    result = tbl.record_content_get("alltrim(serv)")
    tbl.close()
    'just take the first 15 entries in the list
    result = word(result,1,crlf(),15)
    result = alltrim(remove_blank_lines(result))

    'convert the |||| delimited string into a Javascript array of the form ['item1','item2']
    result = *for_each(x,"['"+strtran(strtran(x,"'","\'"),"||||","','")+"']",result)
    'convert the crlfs to commas so that we have a Javascript array of arrays.
    result = "["+rtrim(strtran(result,crlf(),","),",")+"]"
    ?"serviceSuggest.populateList("+result+");"
    end if

    else if action = "getFilteredserviceList" then

    dim service as c = default ""
    dim servicename as c = default ""

    if service <> "" .and. servicename <> "" then
    dim tbl as p
    tbl = table.open(tableFilename)
    dim itbl as p
    itbl = tbl.order("servid","left(servid," + len(service)+")=" + s_quote(service) + " .and. serv = " + quote(servicename))

    result = tbl.record_content_get("alltrim(servid) + '||||' + alltrim(serv)")
    tbl.close()
    'just take the first 15 entries in the list
    result = word(result,1,crlf(),15)
    result = alltrim(remove_blank_lines(result))

    'convert the |||| delimited string into a Javascript array of the form ['item1','item2']
    result = *for_each(x,"['"+strtran(strtran(x,"'","\'"),"||||","','")+"']",result)
    'convert the crlfs to commas so that we have a Javascript array of arrays.
    result = "["+rtrim(strtran(result,crlf(),","),",")+"]"
    ?"serviceFilteredSuggest.populateList("+result+");"
    end if
    tbl.close()

    end if

    function jsEscape as c (txt as c)
    jsEscape = stritran(txt,"'","\'")
    jsEscape = stritran(jsEscape,crlf(),"\n")

    end function


    %>

    *******************************************

    I've been looking at the if..then..else function and tried modifying but just can get it to work. What i need to do is the following

    1) Take the input
    2) search fields across multiple tables.

    Please can someone give me some pointers what would be the best way to approach this?

    I've been pulling my hair out tryin all kinds of combinations.

    Many Thanks
    Last edited by timnoakes; 02-09-2009 at 10:34 AM.

  2. #2
    VAR
    Real Name
    Bill Parker
    Join Date
    Apr 2000
    Location
    Dallas, TX
    Posts
    1,699

    Default Re: Using Autosuggest across multiple tables

    I am just leaving and did not have time to look at the logic, but
    if service <> "" .and. servivename <> "" then
    should be
    if service <> "" .and. serviCename <> "" then

    Bill.

  3. #3
    Member
    Real Name
    tnt
    Join Date
    Oct 2005
    Posts
    18

    Default Re: Using Autosuggest across multiple tables

    Hi Bill, just wondering whether you've had a chance to look at the logic. I've corrected the typo.

    Many Thanks

    Tim

  4. #4
    Member StephenP's Avatar
    Real Name
    Stephen Pilon
    Join Date
    Apr 2000
    Location
    Front Royal, Virginia
    Posts
    491

    Default Re: Using Autosuggest across multiple tables

    Tim,

    I'm not sure exactly what you mean by "search fields across multiple tables". I see you have two IF statements and they each search a different table. Is that all you mean? If so, please give the specifics of the problem.

    I did notice that you are opening the table just to search for records and return a record list. I never open a table like this if I can avoid it. It seems messier, and xbasic provides a simpler method (which I believe provides the same result).

    Thus, this :

    Code:
    dim tbl as p 
    tbl = table.open(tableFilename)
    dim itbl as p 
    itbl = tbl.order("serv","left(serv," + len(service)+")=" + s_quote(service))
            
    result = tbl.record_content_get("alltrim(serv)")
    tbl.close()
    'just take the first 15 entries in the list
    result = word(result,1,crlf(),15)
    result = alltrim(remove_blank_lines(result))
    Can be replaced with this:

    Code:
    dim resultString as c
    
    resultString = table.external_record_content_get(tableFilename,"alltrim(serv)","serv","left(serv," + len(service)+")=" + s_quote(service))
    resultString = word(resultString,1,crlf(),15)
    resultString = alltrim(remove_blank_lines(resultString))
    Stephen Pilon
    Associate Librarian
    Christendom College

  5. #5
    Member
    Real Name
    tnt
    Join Date
    Oct 2005
    Posts
    18

    Default Re: Using Autosuggest across multiple tables

    Hello Stephen,

    The current script searches one table called 'compser.dbf'

    I took the script from the 'web applications demo' and modified to search one of my tables.

    The demo is at **http://afas.alphasoftware.com/Sample...List_DBF.a5w**

    What I need to be able to do is search across more than one table. I'm new to alpha and need some guidence on how to approach this.

    Many Thanks

    Tim

  6. #6
    Member StephenP's Avatar
    Real Name
    Stephen Pilon
    Join Date
    Apr 2000
    Location
    Front Royal, Virginia
    Posts
    491

    Default Re: Using Autosuggest across multiple tables

    You seem to have an extra "tbl.close()" between the last two END IF statements. I don't know how that would affect things.

    I only see you searching one table. Are you asking how to add another table to the search? If so, in what manner do you want to sequence it? Search tbl1 then tbl2 then tbl3 -- or search tbl1 then if nothing found, tbl2, and if nothing found then tbl3 -- or search tbl and tbl2 and tbl3 and combine results?

    I'm not sure exactly what you need.
    Stephen Pilon
    Associate Librarian
    Christendom College

  7. #7
    Member
    Real Name
    tnt
    Join Date
    Oct 2005
    Posts
    18

    Default Re: Using Autosuggest across multiple tables

    Hello Stephen,

    Many thanks for you prompt repsonses and questioning it's enabling me to begin to pick up how to approach the logic.

    Ideally would like to search tbl1 then if nothing found, tbl2, and if nothing found then tbl3.

    I tried your code last night but didn't get any results posted back from the ajax page.

    Am i correct in the way it works is;

    1) type a word in the suggestion box
    2) the ajax page receives a response from the a5w page
    3) begins the search
    4) posts the result back to the a5w page


    2) and 3) work in the background. And the most noticable difference 'No page refresh'

    My skills are in design and i'm now venturing into developing, fascinating! If i can help you in return with web design stuff then please don't hesitate to ask.

    Many Thanks

    Tim

  8. #8
    Member StephenP's Avatar
    Real Name
    Stephen Pilon
    Join Date
    Apr 2000
    Location
    Front Royal, Virginia
    Posts
    491

    Default Re: Using Autosuggest across multiple tables

    So it looks like the structure you want is:

    [get initial info from incoming variables]

    run search against TBL1

    if TBL1 results are blank, then run search against TBL2

    if TBL2 results are blank, then run search against TBL3

    I think this example would work for what you want:

    Code:
    <%a5
    dim webSearchVar as c = default ""
    dim return_1 as c
    dim filter_1 as c
    dim order_1 as c
    dim result_1 as c
    
    if webSearchVar <> "" then
        return_1 = "[set you return expression here]"
        filter_1 = "[set you filter expression here]"
        order_1 = "[set your order expression here]"
        
        result_1 = table.external_record_content_get("[PathAlias.ADB_Path]\tbl_1",return_1,order_1,filter_1)
    
        if result_1 = "" then
            dim return_2 as c
            dim filter_2 as c
            dim order_2 as c
            dim result_2 as c
    
            return_2 = "[set you return expression here]"
            filter_2 = "[set you filter expression here]"
            order_2 = "[set your order expression here]"
    
            result_2 = table.external_record_content_get("[PathAlias.ADB_Path]\tbl_1",return_2,order_2,filter_2)
            'add formatting steps here
    
            if result_2 = "" then
                dim return_3 as c
                dim filter_3 as c
                dim order_3 as c
                dim result_3 as c
        
                return_3 = "[set you return expression here]"
                filter_3 = "[set you filter expression here]"
                order_3 = "[set your order expression here]"
        
                result_3 = table.external_record_content_get("[PathAlias.ADB_Path]\tbl_1",return_3,order_3,filter_3)
                'add formatting steps here
    
                if result_3 <> "" then
                    'add formatting steps for result_3 here
                end if
            else
                'add formatting steps for result_2 here
            end if
        else
            'add formatting steps for result_1 here
        end if
    end if
    %>
    Obviously you would be fleshing it out with your own needs.
    Stephen Pilon
    Associate Librarian
    Christendom College

  9. #9
    Member
    Real Name
    tnt
    Join Date
    Oct 2005
    Posts
    18

    Default Re: Using Autosuggest across multiple tables

    Hi Stephen,

    Sorry have taken a while to respond.

    Firstly this is what i came up with. Which doesn't work :(.

    **********************************************************

    <%a5

    dim action as c = default ""

    dim service as c = default ""


    dim dbf_folder as c
    dbf_folder = _A5_Aliases.get("PathAlias.ADB_Path")



    if action = "getserviceList" then
    if service <> "" then

    dim tbl as p

    tbl = table.open(a5_removetrailingbackslash(dbf_folder) + chr(92) + "compindser.dbf")

    dim itbl as p

    itbl = tbl.order("Indserv_Na","left(Indserv_Na," + len(service)+")=" + s_quote(service))

    result = tbl.record_content_get("alltrim(Indserv_Na) + alltrim(Descriptio)")
    tbl.close()

    'just take the first 15 entries in the list
    result = word(result,1,crlf(),15)
    result = alltrim(remove_blank_lines(result))

    'convert the |||| delimited string into a Javascript array of the form ['item1','item2']
    result = *for_each(x,"['"+strtran(strtran(x,"'","\'"),"||||","','")+"']",result)
    'convert the crlfs to commas so that we have a Javascript array of arrays.
    result = "["+rtrim(strtran(result,crlf(),","),",")+"]"
    ?"serviceSuggest.populateList("+result+");"


    else if service = "" then

    dim tbl1 as p

    tbl1 = table.open(a5_removetrailingbackslash(dbf_folder) + chr(92) + "compsec.dbf")

    dim itbl1 as p

    itbl1 = tbl.order("Sector_id","left(Sector_id," + len(service)+")=" + s_quote(service))

    result = tbl.record_content_get("alltrim(Sector_id) + alltrim(Sector_nam)")

    tbl.close()


    'just take the first 15 entries in the list
    result = word(result,1,crlf(),15)
    result = alltrim(remove_blank_lines(result))

    'convert the |||| delimited string into a Javascript array of the form ['item1','item2']
    result = *for_each(x,"['"+strtran(strtran(x,"'","\'"),"||||","','")+"']",result)
    'convert the crlfs to commas so that we have a Javascript array of arrays.
    result = "["+rtrim(strtran(result,crlf(),","),",")+"]"
    ?"serviceSuggest.populateList("+result+");"


    end if
    end if
    end if

    function jsEscape as c (txt as c)
    jsEscape = stritran(txt,"'","\'")
    jsEscape = stritran(jsEscape,crlf(),"\n")

    end function


    %>

    **********************************************************

    I then had a play around with your script and to be perfectly honest havn't got anywhere due to lack of understanding.

    I've wrote in red my understanding if you could kindly confirm.

    <%a5
    dim webSearchVar as c = default "" (will this be the name of the input tag on the non ajax page)
    dim return_1 as c
    dim filter_1 as c
    dim order_1 as c
    dim result_1 as c

    if webSearchVar <> "" then
    return_1 = "(code goes here to send data back to none ajax page)"
    filter_1 = "(why is a filter needed?)"
    order_1 = "(code that orders table)"

    result_1 = table.external_record_content_get("[PathAlias.ADB_Path]\tbl_1",return_1,order_1,filter_1)

    if result_1 = "" then
    dim return_2 as c
    dim filter_2 as c
    dim order_2 as c
    dim result_2 as c

    return_2 = "[set you return expression here]"
    filter_2 = "[set you filter expression here]"
    order_2 = "[set your order expression here]"

    result_2 = table.external_record_content_get("[PathAlias.ADB_Path]\tbl_1",return_2,order_2,filter_2)
    'add formatting steps here

    if result_2 = "" then
    dim return_3 as c
    dim filter_3 as c
    dim order_3 as c
    dim result_3 as c

    return_3 = "[set you return expression here]"
    filter_3 = "[set you filter expression here]"
    order_3 = "[set your order expression here]"

    result_3 = table.external_record_content_get("[PathAlias.ADB_Path]\tbl_1",return_3,order_3,filter_3)
    'add formatting steps here

    if result_3 <> "" then
    'add formatting steps for result_3 here
    end if
    else
    'add formatting steps for result_2 here
    end if
    else
    'add formatting steps for result_1 here
    end if
    end if
    %>


    Just wondering how long it took you to learn xbasic? and your learning approach?

    Many Thanks

    Tim

Similar Threads

  1. storing value from AJAX autosuggest
    By Ezslim in forum Application Server Version 9 - Web/Browser Applications
    Replies: 1
    Last Post: 05-18-2008, 04:21 AM
  2. Joining Multiple Tables
    By MValentine in forum Alpha Five Version 8
    Replies: 4
    Last Post: 03-06-2008, 07:23 AM
  3. Query multiple tables, but not all linked tables
    By TCorn in forum Alpha Five Version 8
    Replies: 4
    Last Post: 02-12-2008, 12:18 PM
  4. Replies: 8
    Last Post: 02-12-2007, 02:43 PM
  5. multiple tables
    By BWilliams in forum Alpha Five Version 5
    Replies: 6
    Last Post: 10-16-2002, 06:03 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
  •