Weird problem with dddw not applying filter when the user clicks on a different updateble column

In my app, if the user clicked on a new row for an updateable column, the dddw filter for pos_cd was not being applied. It would work if the user clicked on row outside any column, or on the pos_cd or rsrc_grp columns. Developed this workaround, added the following to the Clicked Event:

if IsValid(dwo) then
   if lower(dwo.type) <> "datawindow" then
       this.SetColumn('pos_cd' )
       this.SetColumn('dpt_nbr' ) // first column
   end if
   if Lower(dwo.type) = "column" and row > 0 then
      ls_name = dwo.name
      this.SetColumn(ls_name)
   end if
end if

RowFocusChanged

integer li_col
long    ll_row

ll_row = this.GetRow()

if ll_row > 0 and this.RowCount() > 0 then
   wf_populate_resrc_grp_dropdown(ll_row)
   wf_FilterPositions()
   wf_ScrollToRow()
   li_col = this.GetColumn()
   if NOT IsNull(li_col) and li_col > 0 then
      this.SetColumn('pos_cd' )
      this.SetColumn(li_col)
   end if
end if 

How come my dddw pops up a response window asking for retrieval arguments when I try to insert a row?

If the datawindow tries to insert a new row, and the dddw is not yet populated, the response window asking for Retrieval Arguments will be displayed.  Here are 3 ways to solve this problem:

1. Get the handle of the DDDW using GetChild, SetTransObject() and Retrieve() before the parent is filled. Then filter the invalid rows based on the values of the columns entered. This works fine if there are only a modest number of rows in the DDDW universe and this data is static.
2. Pre-store data in the DDDW in the DataWindow painter. Then filter the invalid rows based on the values of the columns entered. This requires the data to be static. If it is not, the datawindow has to be updated, and a new build will need to be deployed.
3. Get the handle of the DDDW using Get Child and InsertRow() to give it a result set. The DDDW will have a blank row, but at least the annoying response window will not be in the user’s face. The row with the DDDW is protected until all the columns from which retrieval arguments are derived are entered. Then the DDDW is populated with a retrieve (or copied from a cache).

DDDW Contents Based on the Value of Another Column

The Problem

The contents of a particular drop down datawindow (dddw) depends on the value of another column. In the example, Version No is a drop down data window whose contents depend on the value of Tool Name. Version No will be a dddw child. Many books use retrieval arguments, but Andy Kempen showed me a way of using filter to avoid the additional database traffic. Note, this example assumes that the Code Value and the Display Value of the DDDW are the same. If they are different you will have another problem which will be addressed in another post

dddw-pic11

• When the datawindow is retrieved, the entire contents of the dddw child must also be retrieved. In the ue_retrieve event:

// override ancestor

datawindowchild dwc
string          ls_tool_name

this.GetChild("version_no", dwc)
dwc.SetTransObject(SQLCA)
dwc.Retrieve( )

this.SetTransObject(SQLCA)
IF this.Retrieve(il_uo_seq_no) < 0 THEN
   IF SQLCA.SQLCode < 0 THEN
      MessageBox("[1]uo_dw@ue_retrieve",  SQLCA.SQLErrText, stopsign!)
   END IF
END IF

IF This.GetRow( ) = 0 THEN
   this.Event ue_insert( )
END IF

• If the user selects a different row, the column which drives the DDDW child may change, so the contents of the DDDW child will have to be updated. In the RowFocusChanged event:

datawindowchild  dwc
string           ls_tool_name

this.GetChild("version_no", dwc)
ls_tool_name = this.GetItemString ( currentrow, "tool_name" )
IF IsNull(ls_tool_name) THEN
   ls_tool_name = ""
END IF
dwc.SetFilter("tool_name = '" + ls_tool_name + "'" )
dwc.Filter( )

If the column which drives the DDDW child changes, the contents of the DDDW child will also have to change. So in the ItemChanged event:

datawindowchild dwc
string          ls_tool_name,ls_null

IsNull(ls_null)

IF Upper(getcolumnname( )) = "TOOL_NAME" THEN
   this.GetChild("version_no", dwc)
   ls_tool_name = data
   dwc.SetFilter("tool_name = '" + ls_tool_name +"'" )
   dwc.Filter( )
   SetItem(row, "version_no", ls_null)
end if

• If a new row is inserted, the dwc should not have any rows as the tool_name column is empty. Script in the ue_insert event:

datawindowchild   dwc
decimal           ld_seq_num
integer           li_rc
string            ls_tool_name, ls_null

ld_seq_num = il_uo_seq_no
li_rc = this.SetItem ( this.GetRow( ), "SEQ_NO" , ld_seq_num )

IsNull(ls_null)
IF Upper(getcolumnname( )) = "TOOL_NAME" THEN
   this.GetChild("version_no", dwc)
   dwc.SetFilter("tool_name = '" + ls_null + "'" )
   dwc.Filter( )
END IF

Rich Text

A rich text DataWindow allows you to

• Define a result set for the DataWindow
• Retrieve info into the DataWindow
• Update data
• Add a limited number of objects to the DataWindow
• Set DataWindow column properties

A rich text DW is best used for display-only reports, especially mail-merge documents. However, if you want, you can specify validation rules and display formats for the input fields

Crosstab

A crosstab report presents data in a row and column format. Because cells in a crosstab report are calculated, they cannot be updated by users.

Static Crosstab

Columns and rows are established based on data in the database when the crosstab is defined. Subsequent executions do nto change the number of rows and columns, only the values change

Dynamic Crosstab

All column and rows are built during execution based on current data in the database. Both the number of rows and columns and the values contained in the report can change.