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

Thread: Search Grid On A Desktop Form

  1. #1
    Member
    Real Name
    Melvin
    Join Date
    Nov 2011
    Posts
    133

    Default Search Grid On A Desktop Form

    Hi All

    I have created a Search Grid web component and embedded it on a desktop form. This Search Grid is designed to allow the user to search for a specific customer in the database. (Please see pic) I'm looking to create a link between the search grid and the customer desktop form. The idea being that when the user comes across the customer they are looking for, they can simply click either on the row or a link that would then open the related customer form.

    Is this possible? And if so, how do I build it?

    Search Form.JPG

  2. #2
    Member
    Real Name
    Melvin
    Join Date
    Nov 2011
    Posts
    133

    Default Re: Search Grid On A Desktop Form

    Follow Up...

    If it is not possible to link an embedded grid to a desktop form. How can I program a desktop button to run a search of a browse based on values entered into a number of variables?

    Please see pic as a simple example.

    Search.JPG

  3. #3
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,366

    Default Re: Search Grid On A Desktop Form

    Why not use a Browse with a click through to the record?
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  4. #4
    Member
    Real Name
    Melvin
    Join Date
    Nov 2011
    Posts
    133

    Default Re: Search Grid On A Desktop Form

    Hi Ted

    Using the browse with a on row click is my back up plan. The issue i'm having with this is building the variables and search button. The database will have over 110,000 customers, so I need a solid search tool.

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

    Default Re: Search Grid On A Desktop Form

    Unzip the attached form and import to the customer table in AlphaSports.

    search form.a5pkg

  6. #6
    Member
    Real Name
    Melvin
    Join Date
    Nov 2011
    Posts
    133

    Default Re: Search Grid On A Desktop Form

    Hi Stan

    Thanks for the Form!

    Is there anyway to modify the search to allow a partial search?

    For example: If the user searches for "15 Park St", but the field value is "15 Park Street"

    It's just that when I enter part of the field value it returns nothing.

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

    Default Re: Search Grid On A Desktop Form

    Add another form variable named addr.

    Add this to the search button code.

    if var->addr > ""
    filt = filt + quote(var->addr) +" $ Bill_Address_1"
    end if

    That will allow someone to enter 15 Park and get either.

  8. #8
    Member
    Real Name
    Melvin
    Join Date
    Nov 2011
    Posts
    133

    Default Re: Search Grid On A Desktop Form

    Thanks Stan! That works perfectly.

    Can you recommend how to code a Refresh button to clear the filter?

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

    Default Re: Search Grid On A Desktop Form

    To clear the filter issue a show all. To clear the variables reset them to blank (negative number for the creditrating).

  10. #10
    "Certified" Alphaholic
    Real Name
    Jetson Lilibeth
    Join Date
    Dec 2011
    Posts
    1,093

    Default Re: Search Grid On A Desktop Form

    Nice search technique for editing, I will be using it in my forms, BTW, How do we search dates that has a from and to criteria?

  11. #11
    Member
    Real Name
    Melvin
    Join Date
    Nov 2011
    Posts
    133

    Default Re: Search Grid On A Desktop Form

    Hi Stan

    I've come across an issue using this code while searching numeric and date fields.

    Any suggestions on how to modify the code?

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

    Default Re: Search Grid On A Desktop Form

    Would depend on what your issue is. The searching works for me with numerics as is. Try searching for creditrating 3 in AlphaSports.

  13. #13
    Member
    Real Name
    Melvin
    Join Date
    Nov 2011
    Posts
    133

    Default Re: Search Grid On A Desktop Form

    Here is a copy of my code for a Customer Search form.

    The v_custid is where I am having problems.

    The error I get is "argument is incorrect data type"

    filt = ""
    if var->v_cname > ""
    filt = filt + quote(var->v_cname) +" $ cust_fnme"
    end if
    if var->v_cname2 > ""
    filt = filt + quote(var->v_cname2) +" $ cust_lnme"
    end if
    if var->v_caddr1 > ""
    filt = filt + quote(var->v_caddr1) +" $ cust_add1"
    end if
    if var->v_caddr2 > ""
    filt = filt + quote(var->v_caddr2) +" $ cust_add2"
    end if
    if var->v_cunit > ""
    filt = filt + quote(var->v_cunit) +" $ cust_unit"
    end if
    if var->v_ccity > ""
    filt = filt + quote(var->v_ccity) +" $ cust_city"
    end if
    if var->v_cstate > ""
    filt = filt + quote(var->v_cstate) +" $ cust_state"
    end if
    if var->v_czip > ""
    filt = filt + quote(var->v_czip) +" $ cust_zip"
    end if
    if var->v_ccnty > ""
    filt = filt + quote(var->v_ccnty) +" $ cust_cntry"
    end if
    if var->v_ctel > ""
    filt = filt + quote(var->v_ctel) +" $ tel_1"
    end if
    if var->v_ctel2 > ""
    filt = filt + quote(var->v_ctel2) +" $ tel_2"
    end if
    if var->v_ctel3 > ""
    filt = filt + quote(var->v_ctel3) +" $ tel_3"
    end if
    if var->v_cemail > ""
    filt = filt + quote(var->v_cemail ) +" $ email"
    end if
    if var->v_caccount > ""
    filt = filt + quote(var->v_caccount ) +" $ accnt_num"
    end if
    if var->v_custid >= 0
    filt = filt + "cust_id = "+alltrim(str(var->custid))
    end if
    filt = ltrim(filt," .and. ")
    parentform.QueryRun(filt)

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

    Default Re: Search Grid On A Desktop Form

    What data type field is cust_id and what datatype is the variable custid?

  15. #15
    Member
    Real Name
    Melvin
    Join Date
    Nov 2011
    Posts
    133

    Default Re: Search Grid On A Desktop Form

    Cust_id is a auto incremented numeric field with a starting value of 120000.

    the custid variable is v_custid = "".

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

    Default Re: Search Grid On A Desktop Form

    If v_custid is a character type then you can't directly test for >=0

    if var->v_custid >= 0
    filt = filt + "cust_id = "+alltrim(str(var->custid))
    end if

    you need to test the value of the variable
    if val(var->v_custid) >= 0

    Actually you probably want to test for

    if val(var->v_custid) >= 120000

  17. #17
    Member
    Real Name
    Melvin
    Join Date
    Nov 2011
    Posts
    133

    Default Re: Search Grid On A Desktop Form

    Just implemented your code and it works great!

    What modifications will I need to make to apply this to a date field?

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

    Default Re: Search Grid On A Desktop Form

    You're probably going to use two dates and filter for records in the range between them?
    dt1 is the first date variable, dt2 is the second datevariable, and date_field is your date field name to filter on.

    if isdate(dtoc(var->dt1)) .and. isdate(dtoc(var->dt2)) .and. (var->dt2>var->dt1)
    filt = filt + " .and. between(date_field,"+s_quote(var->dt1)+","+s_quote(var->dt2)+")"
    end if

  19. #19
    Member
    Real Name
    Melvin
    Join Date
    Nov 2011
    Posts
    133

    Default Re: Search Grid On A Desktop Form

    I actually only need to search for one date.

    my variable is v_rpurch and my date field is purch_date.

    Would I be right in say my code should look like this?

    if isdate(dtoc(var->v_rpurch))
    filt = filt + "purch_date= "+ s_quote(var->v_rpurch)
    end if

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

    Default Re: Search Grid On A Desktop Form

    Close. Assuming you declare the variable v_rpurch = {}

    if isdate(dtoc(var->v_rpurch)) .and. var->v_rpurch > {}
    filt = filt + ".and. purch_date= "+ s_quote(var->v_rpurch)
    end if

  21. #21
    Member
    Real Name
    Melvin
    Join Date
    Nov 2011
    Posts
    133

    Default Re: Search Grid On A Desktop Form

    Thanks for your help again!

  22. #22
    Member
    Real Name
    Melvin
    Join Date
    Nov 2011
    Posts
    133

    Default Re: Search Grid On A Desktop Form

    Hi Stan

    Sorry to bother you again, but I came across several issues that I can't resolve.

    In the code listed below the first search criteria "svc_id" is a numeric value and the rest are characters. With this code I can search for the svc_id, but all of the other fields return no results. If I delete the svc_id search criteria then the other search fields work normally. Is there something wrong with my code that prevents searching multiple field types?

    Also, I can not search using more than one field at a time. Is there away to allow the user to enter multiple search fields?

    Code:
    filt = ""
    if val(var->v_sservid) >= 0
    filt = filt + "svc_id = "+alltrim(str(var->v_sservid))
    end if
    if var->v_sinstall > ""
    filt = filt + quote(var->v_sinstall) +" $ Svc_instal"
    end if
    if var->v_srate > ""
    filt = filt + quote(var->v_srate ) +" $ svc_ratng"
    end if
    if var->v_scomp > ""
    filt = filt + quote(var->v_scomp) +" $ svc_nme"
    end if
    if var->v_saddr1 > ""
    filt = filt + quote(var->v_saddr1) +" $ svc_add1"
    end if
    if var->v_saddr2 > ""
    filt = filt + quote(var->v_saddr2) +" $ svc_add2"
    end if
    if var->v_sunit > ""
    filt = filt + quote(var->v_sunit) +" $ svc_unit"
    end if
    if var->v_scity > ""
    filt = filt + quote(var->v_scity) +" $ svc_city"
    end if
    if var->v_sstate > ""
    filt = filt + quote(var->v_sstate) +" $ svc_state"
    end if
    if var->v_szip > ""
    filt = filt + quote(var->v_szip) +" $ svc_zip"
    end if
    if var->v_scountry > ""
    filt = filt + quote(var->v_scountry) +" $ svc_cntry"
    end if
    if var->v_stel1 > ""
    filt = filt + quote(var->v_stel1) +" $ s_off_tel"
    end if
    if var->v_semail > ""
    filt = filt + quote(var->v_semail ) +" $ s_email"
    end if
    if var->v_stype > ""
    filt = filt + quote(var->v_stype ) +" $ svc_typ"
    end if
    filt = ltrim(filt," .and. ")
    parentform.QueryRun(filt)

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

    Default Re: Search Grid On A Desktop Form

    Haven't quite grasped the need for .and., have we?

    Code:
    filt = ""
    if val(var->v_sservid) >= 0
    filt = filt + "svc_id = "+alltrim(str(var->v_sservid))
    end if
    if var->v_sinstall > ""
    filt = filt + " .and. "+ quote(var->v_sinstall) +" $ Svc_instal"
    end if
    if var->v_srate > ""
    filt = filt + " .and. "+quote(var->v_srate ) +" $ svc_ratng"
    end if
    if var->v_scomp > ""
    filt = filt + " .and. "+quote(var->v_scomp) +" $ svc_nme"
    end if
    if var->v_saddr1 > ""
    filt = filt + " .and. "+quote(var->v_saddr1) +" $ svc_add1"
    end if
    if var->v_saddr2 > ""
    filt = filt + " .and. "+quote(var->v_saddr2) +" $ svc_add2"
    end if
    if var->v_sunit > ""
    filt = filt + " .and. "+quote(var->v_sunit) +" $ svc_unit"
    end if
    if var->v_scity > ""
    filt = filt + " .and. "+quote(var->v_scity) +" $ svc_city"
    end if
    if var->v_sstate > ""
    filt = filt + " .and. "+quote(var->v_sstate) +" $ svc_state"
    end if
    if var->v_szip > ""
    filt = filt + " .and. "+quote(var->v_szip) +" $ svc_zip"
    end if
    if var->v_scountry > ""
    filt = filt + " .and. "+quote(var->v_scountry) +" $ svc_cntry"
    end if
    if var->v_stel1 > ""
    filt = filt + " .and. "+quote(var->v_stel1) +" $ s_off_tel"
    end if
    if var->v_semail > ""
    filt = filt + " .and. "+quote(var->v_semail ) +" $ s_email"
    end if
    if var->v_stype > ""
    filt = filt + " .and. "+quote(var->v_stype ) +" $ svc_typ"
    end if
    filt = ltrim(filt," .and. ")
    parentform.QueryRun(filt

  24. #24
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,366

    Default Re: Search Grid On A Desktop Form

    Melvin, the easiest way is to use the Find by Form when multiple fields need to be searched and satisfied.
    You don't have to use the form you have open though.
    If there are specific fields you need to search on, suggest you create a Search Button which opens a form with only the searchable fields on it. I suspect you can do it in a Modal manner but not tried that yet.
    Then, if the data is found, open the primary form again and scroll through the records in the query, or open the primary using a specific ID or Record number.
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  25. #25
    Member
    Real Name
    Melvin
    Join Date
    Nov 2011
    Posts
    133

    Default Re: Search Grid On A Desktop Form

    Thanks Stan...That fixed the multiple search issue.

    Unfortunately I still can't search for numeric and character values.

    Any Ideas?

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

    Default Re: Search Grid On A Desktop Form

    Unfortunately I still can't search for numeric and character values.
    Not understood. Probably time for a sample database to see where the issue lies.

  27. #27
    Member
    Real Name
    Melvin
    Join Date
    Nov 2011
    Posts
    133

    Default Re: Search Grid On A Desktop Form

    Hi Stan

    Attached is a sample database.

    At present it will search the Servicer ID, which is a numeric field, but all other character fields will not work.

    If i delete the below line of code, then the character fields work.

    Code:
    if val(var->v_sservid) >= 0
    filt = filt + "svc_id = "+alltrim(str(var->v_sservid))
    end if
    Attached Files Attached Files

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

    Default Re: Search Grid On A Desktop Form

    What is the purpose of the equals sign in

    if val(var->v_sservid) >= 0

    ?

    If the user enters only one of the character fields to be searched, this line still tries to build a filter using the service id since the value of a blank is zero.

    Note: You were probably following my earlier pattern but you neglected the part about.....

    To clear the variables reset them to blank (negative number for the creditrating). IOW your refresh button should reset any numeric variables to some negative number. Then the test for >= won't be met and the service id won't be part of the filter.
    Last edited by Stan Mathews; 07-02-2012 at 03:30 PM.

  29. #29
    Member
    Real Name
    Melvin
    Join Date
    Nov 2011
    Posts
    133

    Default Re: Search Grid On A Desktop Form

    Hmmm...I guess there is no real point to it.

    It just allowed the search function to work for me.

    What should the appropriate code be?

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

    Default Re: Search Grid On A Desktop Form

    See my note in post 28. If your refresh button sets the service id to zero then you need to test for > 0 (no equals, if val(var->v_sservid) > 0).
    If it sets service id to a negative value then you can test for >= 0.

Similar Threads

  1. opening a desktop form from a grid
    By barry685 in forum Alpha Five Version 11 - Desktop Applications
    Replies: 9
    Last Post: 03-11-2012, 03:33 PM
  2. Opening a desktop form from a grid
    By rafaelecuador in forum Application Server Version 10 - Web/Browser Applications
    Replies: 0
    Last Post: 11-12-2011, 07:24 PM
  3. Use Grid search form to filter child grid
    By aburningflame in forum Application Server Version 10 - Web/Browser Applications
    Replies: 0
    Last Post: 10-26-2010, 10:09 AM
  4. Grid / Search Criteria - active by loading form
    By PhillipS in forum Application Server Version 10 - Web/Browser Applications
    Replies: 0
    Last Post: 09-23-2009, 10:01 PM
  5. Initialize grid search field in tabbed form
    By Brett in forum Web Application Server v7
    Replies: 1
    Last Post: 04-07-2006, 10:35 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
  •