DataBase Driven Grid w/Position To

[ppbedz]

I am trying to load a grid using the data-base driven option with an sql statement containing a parm (which is a position to field). The parm will initially be blank, so the grid should load with all employees in my file. If the user enters part of the name in the "position to" field, I want the grid to automatically be refreshed starting with whatever name is greater than or equal to the characters typed in the position to field. The grid loads fine from the employee file. However, it does not refresh if I modify the position to field. I have enclosed relevant screen shots. I am hoping someone can help correct my "code" to make this work properly. Thank you....

[Scott Klement]

Patti,

First of all -- this isn't exactly related to the question you've asked, but... your [empname] fields in the output widgets in your subfile will always be blank (as far as I can tell.)

This is a little tricky to explain, so please forgive me if I over/under explain it, just trying to put my thoughts together, here... When you use the sql ("database-driven") properties to load a grid, the data goes into the grid itself. I know that sounds weird... but the "bound fields" (like 'empname') are for data written from your RPG program only. When data is automatically loaded from the database files there's no need for these bound properties and they are not used. Data is just put directly into the cells themselves without any other widgets.

So with the way you have it now, you'll have these [empname] output fields in the grid, but they will be blank. Since they have no backgrounds assigned to them, they will be "invisible" (you'll be able to see through them.) And you'll be able to see the SQL loaded data which will be "behind" them in the grid.

Hope that makes sense... the [empname] fields won't stop things from working. They're just superfluous and not used.

Second point...

When you bind a field to a property, the data is loaded into that property when your screen is rendered. (i.e. before the screens 'onload' event.) So the 'parameter value' property of your grid is set from the 'position' field when the screen is rendered. This will not change when the user types into the textbox! So when you call the grid's refresh() method, it will re-run the exact same SQL statement that it ran when the display was loaded.

For performance purposes, Profound UI keeps track of the SQL statement, it's parameters, etc. If nothing has changed, it will keep using the data it has already loaded instead of re-running the statement. So really the refresh() call will redraw the grid from the data that PUI has already received from the server when the screen first loaded. This is why your "position" is not working.

We can fix this by using JavaScript to change the "parameter value" when the 'onchange' event fires. To do this, change the following:

• Make sure the textbox has an id of "POSITION" (this is case-sensitive. you can call it whatever you want, but the upper/lowercase must match). The "id" is what's used from JavaScript, not the bound field name.
• set the 'parameter value' property to: script: get("POSITION") -- this will also run only when the display is rendered... so it'll have the exact same effect as binding to the position field, except that now that it's not a bound field, it'll be something we can change with JavaScript.
• In the 'onchange' event (if that's when you want it to run) for the position textbox to applyProperty("PendingGrid", "parameter value", get("POSITION")); -- so this will change the parameter value when the user changes the field.
• On the next line of the 'onchange' (after the applyProperty) do the getObj("PendingGrid").grid.refresh(); (you already have this part -- just make sure it's after the applyProperty)
• Also, you should verify that the id of the grid widget is "PendingGrid" (again, match upper/lowercase exactly) if you haven't already.

You could also do the "applyProperty" and "refresh()" in the onclick of the magnifying glass if you wanted it to update when they click on that image.

Third point...

Instead of doing all of the stuff in the 2nd point, another alternative would be to submit the screen back to the RPG program, and have RPG do EXFMT again. In that case, you could keep the bound fields as-is, and just have onchange do "pui.click()" instead of refresh. Since control is sent back to the RPG program, the position bound variable will be updated, and the whole load/render process of the screen will be repeated, which will refresh the grid.

So the second point is only needed if you want to avoid sending control back to the RPG. (Which some do)

Fourth point....

You probably already know this, but just in case you didn't... the SQL statement doesn't really "position" the subfile Rather it will only load records into the subfile that are greater than your criteria. So you're saying "I only want records WHERE emname >= [position] and emsuspend='S'"

I'm assuming you know this already, but thought I should mention it just in case.

Anyway, hope that helps. Let me know if there's anything I can explain better.

[ppbedz]

Good Morning Scott,

Thank you for the explanation. Point 3 definitely seems like the better approach and I am going to try it. I did not know the fields in the grid would not be populated. I was originally loading the grid from the rpg, but decided it would be fun to try the data-base driven feature. I am a little confused because my grid DOES contain visible (and correct) data. I believe you are saying that if I referenced the field "employee name" in the rpg it would be blank? If so, is there any way the data-base driven approach would load values into fields? The purpose of this application is for the user to select an employee from the grid in order to perform additional processing for the selected employee. The intent was to display the employee name and include the employee number as a hidden field for processing purposes. If not, I will go back to using the rpg and save the data-base driven technique for a more appropriate application.

Patti

[ppbedz]

position_id.PNG (34.74 KiB) Viewed 582 times

empex.PNG (13.63 KiB) Viewed 582 times

Scott,

I removed the "on change" property and replaced w/ pui.click() as you suggested. For some reason, control is not being returned to the rpg. I have successfully used pui.click in other applications (usually with an auto-fill or drop-down) and it has worked fine. Any idea why it might not be working?

Also, interesting that you mentioned upper vs lower for the "position field". The id IS lower case. However it appears as uppercase when displayed in the widget? I even deleted the bound field name and recreated. Profound still inserts the name in uppercase.

[Scott Klement]

The bound field and the id are two separate things. Deleting the bound field won't change the id at all.

I don't know why pui.click() wouldn't work.

[ppbedz]

ok, thx

Scott,

Per my 8:27 post ..... is there any way the data-base driven approach would load values into fields in the grid, or is that approach simply for displaying information w/no selection or evaluation of the grid contents? Thank you, Patti

[Scott Klement]

No, there's no way for the database-driven method to load data into fields. You need to use RPG to load the grid if you want data in fields.