February 27, 2009

Filtering data through multiple rows

Lets say we have created a form on the table EMP and want to filter the data, so that only the Employees from Department 20 and 30 are displayed. The best UI for the user is a filter-block, where you see all departments and can mark them.

In this filter we click on the checkboxes for department 20 and 30:



The button Query data (Abfrage starten) starts a query on the emp-block.



What do we need for this technique?

-) A CONTROL-block with an item TI_Filter_String (char, 2000)
-) A checkbox CB_Filter in the DEPT-block (numeric, 0-unchecked, 1-checked)

The button, which starts the query, needs a WHEN-BUTTON-PRESSED:


:Control.TI_Filter_String := ';';
go_block ('DEPT');
first_record;
LOOP
IF :DEPT.CB_Filter = 1 THEN
:Control.TI_Filter_String := :Control.TI_Filter_String || :DEPT.DEPTNO || ';';
END IF;
IF :SYSTEM.LAST_RECORD = 'TRUE' THEN
EXIT;
ELSE
Next_Record;
END IF;
END LOOP;
go_block ('EMP');
execute_query;


The EMP-block needs this default-where:


:Control.TI_Filter_String LIKE '%;' || DEPTNO || ';%'

The trick is, that each deptno from the filter-string can be found using the LIKE %;...;% - technique:



Have fun with this little trick!
Gerd

2 comments:

Anonymous said...

You shouldn't use a control block but updating the DEFAULT_WHERE before executing the query. You also have the whole code in one place then.

Gerd Volberg said...

good point. Using a local variable and setting the where-clause is the next better way to do it!