Range restrictions in Dexterity

Did battle today with range settings in Dex.  I was trying to use a table index for six column filters, two of which allowed ranges.  Yes it's a somewhat hairy screen but I want to give the client good flexibility in filtering what is a voluminous scrolling region.

 

I completely failed on the table index side.  I know I was providing everything correctly but Dex would not honor my ranges.  It would work for the integer columns but not for the text columns.

 

I bailed on that strategy and went with a "range where" clause.  This is an alternative to index ranges that involves building a Transact-SQL where clause - without the actual "WHERE" reserved word.

 

Here is how I did it.

 

I have my filter fields on the top of the window, with the scrolling region below.  I also have a "Redisplay" button in the control area of the form.  The user enters whatever filters they want and click Redisplay and the scrolling region should be restricted based on the criteria.

 

In the script for the Redisplay button I do the following:

 

1. Check if the filter fields are all empty.  If so display the full table in the scroller.
2. Declare a string variable called strwhere to contain the filter conditions.
3. If at least one of the filter fields is provided start to build the conditions.
4. The first condition checks for "if not empty()" on the first field.
5. On subsequent fields I check first to see if strwhere has data and if so add "AND" at the end.
6. For each field I use the physicalname() function to substitute the real column name then add the operator and the value.
7. String fields must be enclosed in single quotes.  This would start with "= '" and end with "'".
8. Where I've allowed a range of values I start with "BETWEEN" and if the "To" value is empty I substitute the "From" field.
9. Integer values can be on the right of the "=" without quotes converted with str().
10. Date values must be converted with str() and embedded with single quotes.

 

At the end of all that I do a "range table <xx> where strwhere" and then fill the scroller from that.


It worked beautifully and took a fraction of the time I wrestled with the index method.  So I'm hooked on range where and moving on.