|This tip was submitted by Qing Vincent
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
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
3) ... // same as that in (1).
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:
... /* 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
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)
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.)