Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: How do I use a value from the current form in my SQL Statement?

  1. #1
    Junior Member
    Join Date
    Oct 2018
    Posts
    11

    How do I use a value from the current form in my SQL Statement?

    I am writing an SQL statement to populate the Choices available to a particular form field.

    With SELECT usr_name FROM vt_userlookup
    I get end up with a form field that gives me a dropdown with all of the user names from the usr_name field in the vt_userlookup table.

    There are a lot of names so I need to be able to narrow it down so I don't have to scroll all of the names.

    So I switched and put the same SQL statement
    SELECT usr_name FROM vt_userlookup
    in the Autosuggestions property of the form field.
    I get better results because now it is searching as I type and populating a dropdown based on what I typed.
    But it doesn't work the way I want because it populates the dropdown only on matching the beginning letters of the name (Jo brings up John Smith and Josh Barns, but not Ray Johnson)
    I need it to return values that will allow me to enter any letters in the name (full text) and populate a dropdown for the form field with those.

    I came close to getting what I want with the following select statement entered into the Choices property:
    SELECT usr_name FROM vt_userlookup WHERE usr_name LIKE '%' +'Jo' + '%'
    gives me a list of names where any of the names might have the letters Jo in them. Thus it returns both John Smith and Ray Johnson to my dropdown choices.

    But what I really need is a version of this where I can replace dynamic content into the part of the select statement where 'Jo' is. I want to pull that dynamic content from the current form,
    but do not know how to call a current form value into a form. Can someone show me how to do this? Or a better way?

    I have tried it the following way, but it doesn't seem to work:
    SELECT usr_name FROM vt_userlookup WHERE usr_name LIKE '%' + CURRENTFORM.field23 + '%'

    Thanks in advance.

  2. #2
    Lianja MVP
    Join Date
    Feb 2012
    Location
    Berea, KY, USA
    Posts
    2,068
    Hi,

    It depends on whether the field that's holding "jo" is a field in the present cursor, or is bound to a variable. If the latter, use the variable. If the former use Lianja.getCursor() and the Cursor's .getData method: https://www.lianja.com/doc/index.php/Cursor. And if you are writing the app in JavaScript for the web, you can simply use <controlname>.value -- in JavaScript, control names have to be unique throughout the app even if on different pages. You'll probably have to trim the value of course.

    And there could be other ways (there almost always are).

    Hank

  3. #3
    Junior Member
    Join Date
    Oct 2018
    Posts
    11
    Help please. I'm a Newbie.
    I still don't understand how to write all of this into the Choices attribute of my form field.

    My current SQL statement works: SELECT usr_name FROM vt_userlookup WHERE usr_name LIKE '%' + 'Jo' + '%'

    But I need something that will let me add an element where I can dynamically change Jo to something else. I am using this in an order detail section to select part names from the parts table to populate the order detail info in the form. Whatever replaces the 'Jo' needs to work almost like a search. I was thinking that if I used another field in the form and called the value from it, I could get the dropdown to populate correctly. I know it will work with the SQL I already have using LIKE in the SQL statement, but I don't know how to incorporate a value from the current form into the SQL statement.
    It seems like Hank's suggestion would work in javascript, but how do I use it in the SQL statement. I need an example.

    Here is something that does not work, but what I put together from reading things on the forum and Hank's response (in my test example I am just trying to call up user data. vt_userlookup is a table of system users. Quote_Header is the current form. Oh_contact_name is a field I am using temporarily to store the value Jo in as I try to duplicate the result from hard coding the value Jo into the statement:
    SELECT usr_name FROM vt_userlookup WHERE usr_name LIKE '%' + Lianja.Getcursor("Quote_Header").Getdata("Qh_conta ct_name") + '%'

    As you can see, I am completely lost. Can anyone show me how to use a value from the current form in an SQL statement in the Choices property for a field???

  4. #4
    Junior Member
    Join Date
    Oct 2018
    Posts
    11
    I also tried this way: SELECT usr_name FROM vt_userlookup WHERE usr_name LIKE '%' + {Quote_Print.Quote_Header.field23.value} + '%'

    It does not work either. To understand what I am attempting...

    Quote_Print is the name of the page.
    Quote_Header is the name of the form
    field23 is the name of the form field

  5. #5
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    6,221
    You can set the searchfilter for the section dynamically.

    Code:
    yourexpr = "Jo"
    Lianja.getElementByID("page1.section1").searchfilter = "usr_name LIKE '%&yourexpr.%'"
    Lianja.getElementByID("page1.section1").refresh()
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  6. #6
    Lianja MVP
    Join Date
    Dec 2012
    Location
    Croatia, Zagreb
    Posts
    1,121
    Hi Patonrj,

    Your problem with choices is that it may contain
    a) static list (one,two,tree)
    [not: "one,two,tree"]

    b) dynamic list (@employees,lastaname)
    [not: "@employees,lastaname"]

    c) SQL select (select lastname from employees order by lastaname)
    [not: "select lastname from employees order by lastaname"]

    So, I do not see string building possibility here, i.e.
    "select lastname from employees order by "+m_myorder_variable

    There is no macro usage here:
    &(sqlmystatement)
    where you could build the variable
    sqlmystatement="select lastname from employees order by "+m_myorder_variable

    Your best bet here is
    d) user defined function
    {myfunction()}

    See: https://www.lianja.com/doc/index.php/Choices
    and http://www.lianja.com/community/show...Answers-Choice

    Refreshing the formfield could be another problem.

    Josip
    Last edited by josipradnik; 2018-10-15 at 03:15.

  7. #7
    Junior Member
    Join Date
    Oct 2018
    Posts
    11
    A searchfilter for the whole section does not really help. Is there any way to use values from the current section/form in an SQL statement? I need an example of syntax for how to reference the form value in an SQL statement. Note that I would very much like to use the SQL statement in the choices attribute of a field.

    Thank you for the help.

  8. #8
    Lianja MVP
    Join Date
    Dec 2012
    Location
    Croatia, Zagreb
    Posts
    1,121
    Referencing formfield's value in sql statement is not the primary problem here.

    I suppose that Choices was intended for simple list, or linking to the table columns and (later) for a simple sql statement.
    Dynamic usage is possible only with user defined function, as I understand.
    You can submit ER for extending choices possibility with macro &(m_var) being able to build a sql statement.
    It is not the first occasion when Lianja team would listen to developers' needs and make improvements.

    For now, in your second formfield's Change delegate (where you enter 'Jo' and similar search expression),
    you can call user defined function like getempname() from example,
    which is located in your lib_page1_section1.prg like this:

    Code:
    proc getempname()
        sqlvalues("select lastname from employees order by lastname")
        creturn = astring(_sqlvalues)
        return creturn
    endproc
    Here you would be able to build whole sql statement from current form's formfields with Lianja.get(...)
    instead of simple "select lastname from employees order by lastname"
    It is because sqlvalues accept a string.
    Choices does not accept a string, but list of values without any " or '.
    Note how you need astring() to get a comma separated list of values for Choices purpose.

    In Choices of your first formfield put {getempname()} without any " or '

    Choices: {getempname()}

    I am not quite sure if it could be a problem to refresh your first choices-formfield after you change 'Jo' with something new in your second formfield.
    Last edited by josipradnik; 2018-10-15 at 05:36.

  9. #9
    Junior Member
    Join Date
    Oct 2018
    Posts
    11
    Ok, I think we are getting close. Please be patient with me. I am a total newbie with Lianja. I have 20+ years experience with various forms of sql, and even a few years of experience with Foxpro back in the mid 1990's, but not Lianja until a few days ago. So the thing I most need to understand is the syntax of pulling a value from a form into my SQL statement. So lets pretend that I am using sqlvalues or some other method that is NOT in the choices attribute...How would I write my Select statement and reference a current form value to do something like this:

    SELECT firstname FROM userlookup WHERE userid = useridontheform

    What is my syntax for referencing the value of useridontheform?
    Can someone rewrite my Select statement above with a fictional, but correct syntax so I can understand how form values might interact with the SQL language that I understand??? Please.

  10. #10
    Lianja MVP
    Join Date
    Feb 2012
    Location
    Berea, KY, USA
    Posts
    2,068
    Let's say you are in a section with a table of mytable, and are looking for the value of myfield.

    Assuming you are in VFP:

    Code:
    loCursor = Lianja.getCursor("mytable")
    lcFldVal = loCursor.getData("myfield")
    select * from myothertable where cotherfield = lcFldVal
    Those first two lines will work in JavaScript for a web app if you put semi-colons at the end of the lines.

    There are other, more direct ways to get the value depending on what kind of section, whether you are VFP or JavaScript, etc. I showed one way in a previous post in this thread.

    Hank

Tags for this Thread

Bookmarks

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
  •  
Journey into the Cloud
Join us