Link two combo or List Box

[k2R400]

Hello,

I have 2 Combox, one with Countries, another one with Towns (with a dependance to a country)
I try to use Dynamic Select Box in Genie (look this following post) :
http://www.profoundlogic.com/forum/view ... genie#p134

But it don't works. Towns list depends of te selected country.

It work fine with Auto-Complete TextBox but not with ComboBox or ListBox.

Do you have a solution ?

Ty in advance

[Scott Klement]

Unfortunately that original technique that you link to (that post is from 2008) was deemed "insecure". It allows for the possibilty of SQL injection attacks -- it's very unlikely due to some encryption we had under the covers, but was possible, so we blocked that technique in current versions of Genie. You can no longer make "selection criteria" be a variable that's calculated at run-time, it has to be known at compile-time.

However, the same thing can still be achieved using "parameter markers" however, as described here:
http://www.profoundlogic.com/docs/displ ... urity+Mode

So given the old example you found, you would modify it to make "selection criteria" be COMPANY = ? and "parameter value" be script: get("D_6_3"). This way, only the value for the "company" field can be calculated at run-time, not the selection criteria itself, which makes it much more secure, but has the same functionality.

Hope I explained that well -- let me know if you have any questions.

[vijo]

Hi Scott,
I'm k2R400's colleague and currently working on the same project.
Thank you for the explanations, they are crystal clear.
We succeeded in getting a textbox + autocomplete with this method but we couldn't manage to have it working with list boxes or combo boxes. They don't get populated.

[Scott Klement]

So the next question (as I'm sure you've already asked yourself) is "why don't they get populated?"

To find out, we'll need to do a little bit of troubleshooting. I'm assuming (from earlier in this thread) that you are using database-driven drop-downs and combo boxes. These work by generating an SQL statement and sending it to IBM i to be run. Dropdowns and combo boxes both use the same program on the server side for this, it is PUI0009103.

So the first thing that might help us is to see what's happening when PUI0009103 is called. You can do this using the debugging tools that are built-in to your web browser. (Different browsers tools work differnetly, but all support the same stuff...) If possible, go into the network monitor tool in your browsers tools and look at the requests made to PUI0009103 and see what data it's returning, and post that here.

In the request data, you should also see the parameter values (named p1 for the first marker, etc) being sent.

The other thing that might help (assuming that the response from PUI0009103 is not an error) is to look at the SQL statement that's being generated. The SQL statement is generated by Genie and it's placed into a physical file. PUI0009103 runs in a separate background job, and reads the statement from the physical file, inserts the parmaeter markers, and then runs it. It is passed through that physical file so that the data is never sent to the browser (thus preventing a malicious user from modifying the statemnt in his web browser, which would be a security problem.)

To see that statement, look in the PUISSNVP file (this is in your Profound UI install library, the library is named PROFOUNDUI by default, but you can change that to something else during installation...). This file is keyed by your Profound UI session ID and widget id. This file is kept very clean, and should only contain statements currently running on screens on your server, so looking at this file while the screen is active should show you the exact SQL statement being run.

Once you know the SQL statement and the parameter values being sent, you might see what is wrong. If not, you can take this data and try manually running the SQL statement to see why it fails. Or, you can post it here and I'll see if I can spot something.

Hopefully one of these things will help get you on track.

[k2R400]

Thank you Scott but my problem persist, the SQL statement is OK.

I have a Select Box id=Depart with a Database Driven Selection :


I have another one with a Database Driven Selection too based on the previous Select Box with a Criteria:


It work like a charm with TextBox (look at the bottom of the previous image) but not with a Combo or List Box !!!!!
See below.


It seems that the SQL statement of the ListBox is fighted at the beginning (OnLoad ?), thus with empty criteria.
It would thus be necessary, to refresh then Get () function in the Onfocus event of this ListBox.
A way to make that ?

[Scott Klement]

Oh, I see.

When a property is coded with "script: code-here" that script is run when the screen is loaded. If the issue is that the SQL statement works when the screen is loaded, but does not update when something changes, then you need to add some code to make it re-evaluate the properties when things change.

You mention the 'onfocus' event, but I would actually do it in the 'onchange' event for the 'Depart' widget, since you want things to update when the deparment changes.

So in the Depart widget's on change event I'd code this:

Code: Select all

applyProperty("ListBox", "choices parameter value", get(this));
applyProperty("ListBox", "field type", "select box");

notes:
1) get(this) gets the value of the current widget -- in this case, the department widget.
2) applyProperty() changes a property in a widget, in this case, I am changing the 'choices parameter value' to the value of the department widget.
3) When you apply the 'field type' property of most widgets, it forces them to re-render. In this case, changing the field type of the ListBox (even though it's changing to the same field type that was already used) will cause it to re-run the SQL statement and re-draw the list box.

Good luck

[k2R400]

Scott,

Thanks a lot, it work perfectly.