I have read this thread about hierarchical filtering and have implemented a level-based system.
I have a parent / child dataset where the parent row (level 0) is an employee and the child rows (level 1) are expenses:
Bruce (parent key 15, level 0)
Paper $4 (parent key 15, level 1)
Pens $8 (parent key 15, level 1)
Phil (parent key 22)
Lunch $9 (parent key 2, level 1)
Stephen (parent key 28)
Gas $15 (parent key 28, level 1)
Parking $7 (parent key 28, level 1)
In our case we want to filter on just the parent rows and show all the children of the parent, not just the children that match the filter. When I filter by Employee contains ‘ph’ I want Phil and his lunch rows and Stephen and his gas & parking rows.
Since this data structure is flat, we first apply a Level 0 filter on employee name containing ‘ph’. I take the resulting data set and apply an ‘or’ condition to catch all the children with the remaining parent keys. The composite filter winds up looking like this:
(( Employee Contains ‘ph’ ) AND ( Level IsEqualTo 0 ))
OR
(((ParentKey IsEqualTo 22) OR (ParentKey IsEqualTo 28)) AND ( Level IsEqualTo 1 ))
The problem is when I have thousands of rows that match the filter the child level OR clause gets very large and stack overflows occur.
((ParentKey IsEqualTo 1) OR (ParentKey IsEqualTo 2) OR (ParentKey IsEqualTo 3) OR … (ParentKey IsEqualTo {n-1}) OR (ParentKey IsEqualTo {n}))
Is there a better way to do this sort of filtering?
Thanks
-mark