Remember the feature of dddw where all the lookup values (in the child datawindow) are retrieved at the time the parent record is retrieved? Problem: why should the program retrieve a bunch of stand-by records (for dropdown purposes) while some users just want to view the "real, native" record itself, and never intended to click some of the dropdown arrows?

To suppress dddw.retrieve() at runtime is easy: just put a blank record in the datawindow painter at design time. The trick is to get the dddw to do the appropriate retrieve() at the moment when the user actually drops down the dddw (i.e., when he clicks the little dropdown arrow).

Someone once proposed a solution of doing the dddw.retrieve() in the `clicked!' event of the parent datawindow. That's a correct idea, but the problem is that `clicked!' event is fired whether the user clicks exactly on the dropdown arrow or the text edit box portion of that column. If the clicked spot is the latter (i.e., not the little arrow beside it), then the program should not do dddw.retrieve(). But it turns out that there's no way (to the best of my knowledge) to distinguish the clicked spot between those two. That is, GetObjectAtPointer() and GetClickedColumn() don't tell you whether the arrow itself was clicked. 

After some hacking, I gathered that the correct event is pbm_dwndropdown.

So the solution:

Create a user event ue_dddw_dropdown (mapped to pbm_dwndropdown) in the datawindow with the following script:

1) DataWindowChild dwc

        dwGetChild(GetColumnName(), dwc)
        dwc.SetTransObject(SQLCA)
        dwc.retrieve()

That's the basic implementation. Several improvements are:

2) dwGetChild(GetColumnName(), dwc) 

        // Now, if dwc.rowcount()>1, then the
        // dddw has once been dropped-down 
        // before. So don't bother to retrieve() again.
        // Remember that the dddw
        // initially has a single (blank) row.

        if dwc.rowcount() > 1 then return

        dwc.SetTransObject(SQLCA) 
        dwc.retrieve() 

     3) ...     // same as that in (1).
        dwc.retrieve(GetText())

        where the dddw's SQL is:
         select country_name from contry_lookup
         where country_name like :retrieve_argument + "%"

That is, if the user types in "ca" and click the dropdown arrow, the the list of country names dropped down will look like:

                cambodia
                cameroon
                canada
                ...   /* all countries starting with ca */

But countries like "france" and "russia" are not retrieved because they don't satisfy the LIKE clause in the SQL. Needless to say, that saves lots of runtime resources.

4) Suppose the dddw is "employee_name", and a previous field is "department". There are lots of employees in the company. But after you typed in "Information Services" for the department, tab to employee_name field and click the dropdown arrow, you only want to see a list of I.S. employees. So:

        ...   // same as that in (1)
        dwc.retrieve(GetItemString(GetRow(), "department"))

        where the dddw's SQL is:
          select employee_name from employee_table
          where department = :retrieve_argument 

Alright! Have fun and remember that pbm_dwndropdown is an undocumented event (and probably unsupported by Sybase.)