PowerBuilder Retrieve() via controls by the user

Question

I am using SetTransObject() and Retrieve() on PowerBuilder 12.5 Classic. However it doesn't give the user much power in retrieving data; I have a single line edit(sle_employeeID), which the user needs to insert in the employee ID and the DataWindow displays the employee data (IDNO, name, age, designation) of a selected ID NO.

dw_1.settransobject(sqlca)
string employeeID
employeeID=sle_employee.text
dw_1.retrieve(employeeID)

The code retrieves the whole data as per my specifications from the DataWindow object, I used a tabular and quick select statement. Please help with a code that will give me a freer way to select data via controls.

Answer 1

You may want to use the DataWindow's Query Mode as Terry mentioned. You will have to provide your user with instructions, but the basic use is to simply enter the values you want to match directly in the DataWindow. For more information see the topic Providing query ability to users in the DataWindow Programmer's Guide. Here is code I have in one of my utility windows. It uses a menu with a toolbar button to put the DataWindow in Query Mode and to turn Query Mode off and retrieve. I'm using PFC message routing between the menu and the DataWindow event but you could simply call the event from a button clicked event, in which case you'd remove the lines that modify the menu.

// this code is in an event in the DataWindow
// toggles query mode on and off

if "no" = object.dataWindow.queryMode then
    // you may want to check for unsaved changes here and let the user go back
    object.dataWindow.queryMode = "yes"
    m_myMenu.m_rows.m_query.checked = TRUE  // this has a button on the toolbar

else
    m_myMenu.m_rows.m_query.checked = FALSE
    acceptText()
    object.dataWindow.queryMode = "no"
    retrieve()

end if

To get maximum flexibility set criteria.override_edit='yes' on some or all of the columns. The code below sets criteria.override_edit='yes' on all the columns. This may not be appropriate for your situation. Without override_edit the user is restricted to entering query values that pass the column validation.

// this code is in an event in the DataWindow
// it is called after the DataObject is set
// it sets criteria.override_edit='yes' for all the columns

string ls_describe, ls_modify, ls_col, ls_result
integer li_colCount, li_col

setTransObject(SQLCA)
ls_describe = "datawindow.column.count"
ls_result = dw_1.describe(ls_describe)
if not isnumber(ls_result) then
        // logging code elided
        ls_result = '0'
end if
li_colCount = integer(ls_result)
for li_col = li_colCount to 1 step -1
    ls_col = "#" + string(li_col)
    ls_modify = ls_col + ".criteria.override_edit='yes'"
    ls_result = dw_1.modify(ls_modify)
    if "" <> ls_result then 
   // every column may not have an edit, and some edits might not          // have the property. it's just as fast to try to modify it as it
   // is to check it
       // logging code elided
    end if
next 

Answer 2

It's not clear to me what your expectations are, but it strikes me that SetTransObject() and Retrieve() are doing exactly what you've asked them to do; the problem is not there. If you create 20 SLEs and a DataWindow with 20 arguments (and an accompanying SELECT statement sophisticated enough to handle skipping arguments as criteria if the argument is empty), then SetTransObject() and Retrieve() will work just fine.

There is a feature of the DataWindow called Query By Example (QBE) that you may want to look at. However, while this gives significant querying capabilities to the user, you might want to consider who your user is. If the user has a PhD in data analytics or computer science, then QBE is just fine; if the user is a hung-over casual employment telemarketer, you may be tossing them in over their head.

Good luck,

Terry.