How can i add a filter in Data window

Question

I am very new in power builder. I am trying to add the filter in data window. I have created a data window dw_trans which return the trans number & amount. I want to add the filter on the basis of trans number. user select the trans number only that specific row will show.

Answer 1

Here is a simple example

integer li_rc
long ll_rows, ll_filteredrows, ll_trans_id
string ls_filter, ls_original_filter

// in case you need original filter value
ls_original_filter = dw_trans.describe("Datawindow.Table.Filter") 

// create the filter as a string using the dw column name
// ll_trans_id set somewhere else in code (user input or whatever)
ls_filter = 'trans_no = ' + string(ll_trans_id)

//li_rc will be -1 if an error occurs
li_rc = dw_trans.setfilter(ls_filter)

//ll_rows will be number of rows left after filtering
ll_rows = dw_trans.filter()

// # of rows filtered out by current filter
ll_filteredrows = dw_trans.filteredcount()

// remove a filter
dw_trans.setfilter('')
dw_trans.filter()

//set back original filter
IF POS(ls_original_filter,'~"') > 0 THEN
// get rid of tilde doublequote since it is an invalid expression when we try to re-apply the filter
    ls_original_filter = replace(ls_original_filter,Pos(ls_original_filter,'~"') - 1, 2, "'")
// do twice since if there is one there will be two
    ls_original_filter = replace(ls_original_filter,Pos(ls_original_filter,'~"') - 1, 2, "'")
END IF
li_rc = dw_trans.setfilter(ls_original_filter)
ll_rows = dw_trans.filter()

Answer 2

You can have filter in two ways. 1st way. Considering the the column values as a string and getting the input as a string, and finding the relative number or exact number.

2nd way. you can have the numeric type filter. so that you can search the exact values. but cant search the relative values.

i will write the sample code for the both.

am considering your column name as cust_num. and having the SingleLineEdit control for Entering the input value. That name is sle_input. Write the following code on the button clicked event.code follows

1st way.

string ls_input
ls_input=sle_1.text
If len(trim(ls_imput)) =0 Then
Messagebox('Alert','Please Enter The value to filter')
Return
End if
dw_trans.setfilter("string(cust_num) like '"+string(ls_input)+"%'")
dw_trans.filter()

2nd Way.

string ls_input
ls_input=sle_1.text
If len(trim(ls_imput)) =0 Then
Messagebox('Alert','Please Enter The value to filter')
Return
End if
dw_trans.setfilter("cust_num = "+string(ls_input))
dw_trans.filter()

There are many awesome features in powerbuilder. its also possible to filter at the time of typing the numbers. Reply me if you want to know about that code.

Thanks
Raj~

Vote up if satisfy with this answer,else add comment for your specific requirements. So that others having the same question can find the answer quickly.

Answer 3

You can do this very easy.Try this on the clicked() event of your datawindow - it will filter all data into the datawindow from the selection.

integer li_colnbr, li_row
string ls_colname, ls_value, ls_filter, ls_type

if row = 0 then return //don't do nothing
ls_colname = dwo.name
li_row = row
li_colnbr = integer(this.describe(ls_colname + ".id")) //find the name of the column
    if li_colnbr = 0 then
        this.setredraw(true)
        return
    end if

ls_value = string(this.object.data[li_row, li_colnbr]) //get the value of the cell / item
    if isnull(ls_value) then
        ls_filter = "isnull(" + ls_colname + ")"
    else
        ls_type = lower(left(this.describe(ls_colname + ".coltype"), 5)) //get the type of the column
        choose case ls_type
            //creating the final filter
            case "decimal", "number", "long", "ulong", "real" //search into the help for all coltypes
                ls_filter = ls_colname + " = " + ls_value
            case else
                ls_filter = "string(" + ls_colname + ") = ~"" + ls_value + "~""
        end choose
    end if

this.setfilter(ls_filter) //setting the filter
this.filter()
this.sort() //done !

If you want to see again the original data retrieved just add this for example on rbuttondown() event:

this.setfilter('') //back to original filter
this.filter( )
this.sort( )