Reloading a query after changing the choice selection criteria.

[swalter]

I am using an external javascript file to dynamically build my WHERE statement in an auto-complete text box (to be placed into the choices selection criteria) based on form fields that allow for "All Values". If the user decides to search on a value instead, the javascript adds a new segment to the statement. I can load the tables i need and it loads a list of unrefined objects into the field im trying to autocomplete, but as changes are made the autocomplete isnt refined. Im using an applyProperty() on an onFocus(). my property statement looks like:

applyProperty("partnum","choices selection criteria",buildWhereStatement());

Any help would be greatly appreciated.

[Scott Klement]

If that's working, it shouldn't be. That'd mean anyone can go into the browser's JavaScript console and change your statement to query anything they want, or even query other tables on the system. A huge security hole!

The only thing you should be able to change in an SQL statement at run-time are parameter markers.

[swalter]

I guess that makes sense. Is there a way to delay the initial search then? I have the proper validation in my function to prevent any untoward code in the text fields, so im not concerned about building a where statement that could be dangerous. And the page im working on is inward-facing.

[Glenn]

If you want to add additional restrictions to the autocomplete based on other values on the screen, you can accomplish it with a combination of the 'choices selection criteria' and parameter markers.

For example: We include a 'Category' file (CATEGP) in our PUISAMPLES library. Assume you want the user to be able to choose a category via an autocomplete textbox (TextBox1). Now assume we have a LOT of categories and the user may want to limit the ones available in the autocomplete search by the 'Specials' flag in the data (CSPEC) by entering a Y/N value in another textbox (TextBox2). If you load the properties of the autocomplete textbox as shown below it will restrict the data in the search ONLY if the user types something in TextBox2.

parmmarker.png (7.47 KiB) Viewed 512 times

[Scott Klement]

Glenn,

Keep in mind that those "script:" properties are executed when the display is loading. Therefore:

1) it's important for the "auto complete" to come after TextBox2 in the "elements" tab. When the autocomplete is rendered, it needs to run the get(), and the get() will fail if TextBox2 hasn't already been rendered... by putting the TextBox2 earlier than the autocomplete in the elements tab, you ensure that it is rendered first.

2) If you want the user to be able to change TextBox2 and have the results update, then you need to use JavaScript in the TextBox2 that does an applyProperty() against the choices parameter value. The existing "script:" properties are only checked when the display is loaded, but if you code applyProperty() in the onchange or onkeyup, or whatever properties of the textbox, then you can update the properties as the changes happen. You'll probably have to re-apply the field type of the autocomplete box to make the changes go into effect.

Also, consider writing the selection criteria as follows: ? in (CSPEC, '')
(that way you only need one parameter value)