SQL in Profound

[oumouna]

Hello,
Is it possible to use a sql query in Profound to do a linked drop-down list? If so what property do I change in the second select list and how do I use the id of the first select list in my query?
Thank you for your help

[David]

You'll want to reference the value from the first drop down box in the 'choices selection criteria' property of the 2nd drop down box. This property becomes the WHERE clause of the SQL statement that is used to fetch the records.

Take for example, a drop down over file 'COMPANY' that displays a list of companies. When you select one, a 2nd drop down which displays the divisions within the company file 'DIVISION' is loaded with the divisions for the company selected in the first box.

The first box could be setup like this:

Box1_3.png (9.84 KiB) Viewed 1702 times

Box1.png (15.76 KiB) Viewed 1702 times

Box1_2.png (10.63 KiB) Viewed 1702 times

This will fetch the list of companies. The 'CONUM' field which is bound to the value matches the type and length of 'CONUM' field in the 'COMPANY' file. The code that is run from the 'onchange' event will submit the screen back to RPG (so the 2nd drop down box can be loaded) when the user makes a selection in the box.

The 2nd box is then setup like this:

Box2.png (16.12 KiB) Viewed 1703 times

The 'selection criteria' contains a ? which is an SQL parameter marker. The value is given by the field which is bound to 'choices parameter value'. In this case, it's the same field that is receiving the value from the first box. So, when the screen is redisplayed, the records in the 2nd box are filtered by the value of 'CONUM'.

[oumouna]

It works!!!!! Thank you a lot David!!!! You are very kind!

[SeanTyree]

Hi David,

I have a similar need to drive the selection criteria of one Select Box with the value from a previous Select Box, but I was wondering if there is any way to refresh the contents of the second box without using the pui.click() to cause a round-trip to the RPG program?

In short is there now (or plans for) an object refresh method for SQL driven widgets. Could allowing an object reference to be passed to the pui.refresh() work?

Thanks,
Sean

[David]

You can do this today, but it requires a bit of JavaScript coding to 'refresh' the widget with different parameter values.

First of all, if you want to update the 'selection criteria' in a drop down without going back to the server, you cannot bind the 'choices parameter value' property. All SQL properties are evaluated entirely on the server for security purposes.

The ONLY exception to this is 'choices parameter value' and 'parameter value' (same property, but named differently on some widgets). These are evaluated on the server if they are bound, but if not bound the values can be accepted from the browser. The reasoning for this is to allow for what you are wanting to do here.

With the above in mind, and extending the example above to give the company box an id of 'company' and the division box an id of 'division', you can...

Set the 'choices parameter value' on the division box to this:

Code: Select all

script: get("company");

This sets the parameter value when the screen loads to be the value from the company box, so that it's set properly when the screen is first displayed.

Then, whenever the 'company' drop down box is changed, you have to update the parameter value in the division drop down box and trigger a 'refresh' of the widget, by using code like this in the 'onchange' property of the company drop down box:

Code: Select all

applyProperty("division", "choices parameter value", get("company"));
applyProperty("division", "field type", "select box"));

See here for details on the APIs used:

http://www.profoundlogic.com/docs/displ ... y+Used+API

It's the last 'applyProperty' call which refreshes the widget. Apply the 'field type' value EXACTLY as you see it in the designer properties for the widget. This will basically cause PUI to re-render the widget with currently set property values.

Keep in mind that this technique could be a security concern, depending on your usage. Basically you are allowing the user's web browser to supply the 'choices parameter value' in this case. A malicious user could update this value to whatever they want quite easily. Although ONLY this value, the rest of the SQL statement cannot be altered in any way.

An easy way to think of it this way -- you should only use this technique (unbound parameter values) when there is no possible value of the parameter that will result in the user seeing data they shouldn't.

The way PUI secures these components, this is the only part of the SQL statements that you have to consider. If you do not use parameter markers, or if you do use them but bind all the values, then ZERO parts of the SQL statement can be altered and there is nothing to worry about.

[SeanTyree]

Thanks David!

The second applyProperty to "refresh" the combo box was what I was looking for.

Sean

[EDIT] David, while implementing this I noticed that you had an extraneous parenthesis. I am reposting the code for future clarification:

Code: Select all

applyProperty("division", "choices parameter value", get("company"));
applyProperty("division", "field type", "select box");

[paparazia]

Dropdown lists related,



  I would do the same thing without having to return to the server only with javascript.
I managed to do it with pui.click (). But with the javscript code below to...

[paparazia]

ApplyProperty.("division", "field type", .....................................

I have tried it but it's not work

[Scott Klement]

Can you provide details of what does not work? Do you get an error message? Or, what happens when you try it?

[Scott Klement]

The get() and applyProperty() APIs use the 'id' field to look things up (not the bound variable names.) And they are case-senstive.

So, looking over David's example, it seems to me that the code should actually be like this:

Code: Select all

applyProperty("selDivision", "choices parameter value", get("selCompany"));
applyProperty("selDivision", "field type", "select box");

Or if you want to use the property directly (instead of the applyProperty API) it should be:

Code: Select all

script: get("selCompany");