Create Aggregate Filter Only if it Exists

I have a list of records on a page with a filter at the top of the page.  The user fills out the filter information and then selects the "Search" button and the records matching the filter show in the list. The filter has 4 different fields in it all of which, of course, correspond to columns in the list being displayed.  


I'm trying to use an aggregate to build the data that gets displayed.  We can call the search fields: S1, S2, S3 and S4.  These search fields may or may not have a value and they are also Identifiers that are held in the main table.  I can't yet figure out how to specify the 4 filters in my aggregate to accommodate only using the value in the search field if there is something other than 0 (Null Identifier).  If there are no values in any of the search fields, then all the records should display.


Please help me formulate the filters on the aggregate for these optional search fields.

Sincerely,

Paul

Solution

Hi Paul,

Use the filter in a way like this: paramB = NullIdentifier() or colB = paramB

Maybe this post will also help: 

https://www.outsystems.com/forums/discussion/23117/strange-aggregate-filter/

Regards,

Jasper

Solution

you have to apply in your query the WHERE clause like this (considering that S1,S2,S3 and S4 are of Identifier/long integer/integer type):

Select * 

from table

WHERE (S1=NullIdentifier() OR Table.ColumnS1=S1)

AND

(S2=NullIdentifier() OR Table.ColumnS2=S2)

AND

(S3=NullIdentifier() OR Table.ColumnS3=S3)

AND

(S4=NullIdentifier() OR Table.ColumnS4=S4)


If you are using Aggregates, just apply this expressions to the filters:

(S1=NullIdentifier() OR Table.ColumnS1=S1)

AND

(S2=NullIdentifier() OR Table.ColumnS2=S2)

AND

(S3=NullIdentifier() OR Table.ColumnS3=S3)

AND

(S4=NullIdentifier() OR Table.ColumnS4=S4)


If your search filters are of text data type use:

NullTextIdentifier() or "", instead of NullIdentifier().


Nuno Gonçalo Pereira wrote:

you have to apply in your query the WHERE clause like this (considering that S1,S2,S3 and S4 are of Identifier/long integer/integer type):

Select * 

from table

WHERE (S1=NullIdentifier() OR Table.ColumnS1=S1)

AND

(S2=NullIdentifier() OR Table.ColumnS2=S2)

AND

(S3=NullIdentifier() OR Table.ColumnS3=S3)

AND

(S4=NullIdentifier() OR Table.ColumnS4=S4)


If you are using Aggregates, just apply this expressions to the filters:

(S1=NullIdentifier() OR Table.ColumnS1=S1)

AND

(S2=NullIdentifier() OR Table.ColumnS2=S2)

AND

(S3=NullIdentifier() OR Table.ColumnS3=S3)

AND

(S4=NullIdentifier() OR Table.ColumnS4=S4)


If your search filters are of text data type use:

NullTextIdentifier() or "", instead of NullIdentifier().



Thank you Nuno. I was so close but not quite there.

Jasper Oudenaarden wrote:

Hi Paul,

Use the filter in a way like this: paramB = NullIdentifier() or colB = paramB

Maybe this post will also help: 

https://www.outsystems.com/forums/discussion/23117/strange-aggregate-filter/

Regards,

Jasper


Thanks Jasper, this is very good information and gave me what I needed to make it work.