Aggregate Filters

Aggregate Filters


I have a web screen with a table providing results based on a series of filters, text, combo box and dates.  I'm using the if() function with a like statement so if one of the text boxes is not populated then use the wild card %.  Example: 

SyntaxEditor Code Snippet

Personnel.Name like If(Session.Estimate_Search_Personnel = "", "%", "%" + Session.Estimate_Search_Personnel + "%")

This method works exceptionally well until I get to using a drop down for States (Colorado, Nebraska, etc).  Some data has a State while other data doesn't require a State.  Note: the State.Id is stored in the entity data.  I've added a zero and text 'Select a State' in the combo box allowing the end user to decide whether or not to filter the data using a State.  In the OnChange action, I'm reverse looking up the State selected from an ID and assigning the result to a session variable 'Session.Estimate_Search_State' and using the session variable in aggregate like the following...

SyntaxEditor Code Snippet

State.PostalCode =  NullTextIdentifier() or
State.PostalCode like If(Session.Estimate_Search_State =  "", "%", Session.Estimate_Search_State)

I've tried several different variations of the above filter to no success.  Using the first line will yield only rows where there is no State.  The 2nd line yields rows having a State was supplied; this works well with the State combo box, selecting a State or 'Select a State'.  

I need to return all rows, with or without a State in the data, when 'Select a State' is used and return only the rows where a State equals the State selected in the combo box.  The example provided will return all rows having no State in the data and rows where a State was specified.

Any suggestions on how I can get this working correctly?

the following advanced query works perfectly, however, i need to be able to do sorting in the table and to my knowledge I cannot do column sorting when using an advanced query.

SyntaxEditor Code Snippet

SELECT      *
from        {Estimate} left outer join
            {State} on {Estimate}.[StateId] = {State}.[Id] 
where       isnull({Estimate}.[StateId], '') = 
                case when isnull(@StateId, '') = ''
                    then ''
                    else @StateId
            {Estimate}.[StateId] >
                case when isnull(@StateId, '') = ''
                    then 0

after some pain staking translation time from advance query to the aggregate, i got the query to work...

SyntaxEditor Code Snippet

LongIntegerToText(IdentifierToLongInteger(Estimate.StateId)) = If(LongIntegerToText(IdentifierToLongInteger(Session.Estimate_Search_StateId)) = "", "", LongIntegerToText(IdentifierToLongInteger(Session.Estimate_Search_StateId))) or
IdentifierToLongInteger(Estimate.StateId) > If(LongIntegerToText(IdentifierToLongInteger(Session.Estimate_Search_StateId)) = "" , 0 , 9999)

and the where clause of SQL produced by the aggregate...

WHERE (((convert(varchar(21), (isnull(ENEstimate.[STATEID], 0)))) = (CASE WHEN ((convert(varchar(21), (isnull(@Estimate_Search_StateId, 0)))) = N'') THEN N'' ELSE (convert(varchar(21), (isnull(@Estimate_Search_StateId, 0)))) END)) OR ((isnull(ENEstimate.[STATEID], 0)) > convert(bigint, (CASE WHEN ((convert(varchar(21), (isnull(@Estimate_Search_StateId, 0)))) = N'') THEN 0 ELSE 9999 END))))

the "9999" is necessary since the advanced query requires the 'else' versus the advanced query doesn't.

PROBLEM SOLVED.  i thought i would share in case anyone else sees an improvement can be made or can use it.