Selection Criteria - script using screen field value

[jholsombach]

I went searching on the forum for how to set my selection criteria for a database driven dropdown box and found the information on setting it using a script. So I populated my selection criteria with the following:

script: "ELEMP# = " + get("box_scemp")

box_scemp is the ID of the textbox where i bound a field named SCEMP#. SCEMP# is populated with my employer # in my RPG program.

However, i am not getting any choices in my dropdown box so it must be failing on the selection criteria statement.

To test my script, i set up an output box on my page and place that exact same script in its value field and ran my program. The textbox showed exactly what i was expecting, which was: ELEMP# = 547


Do we know any any possible reason why the selection criteria might be failing?

[jholsombach]

I seem to have some other difficulties with the drop down box as well that i can't seem to find some documentation to cover.

To go with prior example, i set up my dropdown box to read from the WDELP file for choices database file. I set my choice options field to ELFYR so i can see a list of years for my particular employer. I set my choices value field bound to KKFYR so my selection will be bound to my program field.

I set my onchange event to process PUI.CLICK(); so i can return control to my program after a selection is made in the drop down box.

However, my field KKFYR is never populated with my selection. I tried to replace that with just returning ELFYR rather than binding to another variable and that field does not seem to change its value either when i debug my program.

Also, while my list on my dropdown should show all of the years available for the employer, i want the currently selected year to be shown in the box, so i set SCFYR to the value field. However i only see the 1st choice from my selection list in the dropdown, not SCFYR.

So if my employer has years 107 - 112, and i am currently viewing year 111, then i want to see 111 in the drop down box and then when i press the arrow to view the choices, i want to see 107 - 112 available to pick from.

UPDATE: We were able to solve the problem with returning the value, the choices value field ties back to the value field of the widget. That was the missing key. Correcting that also fixed the problem of having the currently selected value displaying in the box before selecting the dropdown.

However this question is still pending:

Lastly, is it possible to order to selection in descending order?

thanks,

John Holsombach

[jholsombach]

Another question is can multiple fields be selected to show as choices in a dropdown box? The help text mentions multiple fields can be selected for a text box, but does that mean they can't be used for the other widgets?

For instance, i want to show division number and division name for a division box, rather than just showing the number.

[David]

In regard to the selection criteria not working...

I would not recommend using script to do this, although I don't think that's the cause of hte problem, either. I would recommend binding the selection criteria to an RPG field (just because it's simpler). Then before showing the screen, move text like this into the RPG field:

Code: Select all

ELEMP# = 547  (if ELEMP# is numeric field)

ELEMP# = '547' (if ELEMP# is character field)

This turns into an SQL query, so the selection criteria must be formatted EXACTLY as it would be if you were to put it into an SQL WHERE clause.

To trouble shoot if it doesn't work, there is an API that can show you the SQL errors. Load the screen in Internet Explorer. After the screen loads (and fails to populate the drop down), type this into the browser's address bar and press enter:

Code: Select all

javascript: pui.downloadJSON();

This will alert any SQL errors and should give a clue.

Regarding not getting a value into the program...

The fields that you setup for "choice options" and "choice values" are just those used to build the drop down list. To return a value, bind a field (same type/length as KKFYR) to the drop down's "value" property. When the screen is submitted, the field bound to that will receive the value of KKFYR for the selected record.

Regarding sorting the records...

Put ELFYR DESC into the drop down's "order by" property. Again, this is directly turned into an SQL ORDER BY clause.

Regarding multiple fields...

This can be done for drop down by making the "choice option field" an SQL expression, rather than a field name. For example, you can do this:

Code: Select all

TRIM(ELFYR) || '-' || OTHERFIELD

This will concatenate ELFYR with OTHERFIELD and a dash in between. Again, SQL syntax rules apply. Any expression that can be used as an SQL column in a SELECT from your file can be used.

[jholsombach]

Thank you for information. I did end up changing my selection criteria to a bound field which i then populated in my RPG and it worked.

I also fixed my dropdowns to bind KKFYR into my VALUE field which started to properly return the selected choice.

However, my dropdown boxes appear to have stopped returning my selection. My value field is still set to KKFYR. My Choices Value field is set to ELFYR (which is the year shown on dropdown), however KKFYR is no longer getting set to my selection.

It was working for awhile but no longer is now.

[David]

It will work as long as the "value" property of the drop down box is bound to a field which is of the right type and long enough to hold the value.

When the screen is submitted to the server, the field bound to "value" will be populated with the appropriate value.

Are you sure the property is correctly bound to a field vs. set to the text 'KEYFR'?

[jholsombach]

I discovered what is causing the problem with the dropdown box not returning the proper value however i don't know why it occurs.

To help me review the data on the screen i added two textboxes to display the value of my bound variables (KKFYR, KKDIV).

When i place these two textboxes above the dropdown boxes on the screen and sort my elements, the dropdown boxes do not return the selected values. It seems like they are retaining the value from the text boxes instead.

But if i place my textboxes below the dropdown boxes on the screen (and again sort my elements) then my dropdown boxes do return the selected values.

Is there a limitation to binding the same variable in multiple widgets?

[Brian]

You should never bind two input capable widgets to the same field. When the screen is submitted they have different values. Which one do you want? there is no way for PUI to know so it just passed back the first (or possibly last) one.