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

Using Autosuggest across multiple tables

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

  • 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, 11:34 AM.

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

    Comment


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

      Comment


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

        Comment


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

          Comment


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

            Comment


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

              Comment


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

                Comment


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

                  Comment

                  Working...
                  X