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

[patonrj]

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.

[HankFay]

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

[patonrj]

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???

[patonrj]

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

[barrymavin]

You can set the searchfilter for the section dynamically.

yourexpr = "Jo"
Lianja.getElementByID("page1.section1").searchfilter = "usr_name LIKE '%&yourexpr.%'"
Lianja.getElementByID("page1.section1").refresh()

[josipradnik]

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

[patonrj]

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.

[josipradnik]

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:

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.

[patonrj]

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.

[HankFay]

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:

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