finding empty or null values

Classic Servoy
1

Hi

i am trying to find records in a table with 2 text fields. The first text field i want to search either empty or null values. The other text field search is not empty or not null.

ie.
forms.purchases_detail.controller.find();

text_field1 = “^”;
text_field2 = “!^”;

//start search and sort
forms.purchases_detail.controller.search();

this will find null and not null values. how do i search for empty and not empty values? i assume i then extend the foundset or is there an easier way. with SQL?

2

what does the # symbol do in a search?

3

It makes a search NOT case sensitive, i. e. if you enter #hello it finds Hello and hello.

Regards, Robert

rodneysieb:
what does the # symbol do in a search?

4

Hi Robert

it seems to find empty values in text fields.

MySQL 5.027

5

rodneysieb:
how do i search for empty and not empty values? i assume i then extend the foundset or is there an easier way. with SQL?

You can use ‘=’ and ‘!=’ (or ‘>0’) for finding empty fields and filled fields.

rodneysieb:
it seems to find empty values in text fields.

When using ‘#’ you get a SQL like UPPER(columnName)=UPPER(‘value’).
But since you didn’t provide a value at all you will end up with all records where that column is empty.

Hope this helps.

6

Hi Robert

In JavaScript, ‘=’ and ‘!=’ don’t work with PostgreSQL, I have to use ‘^’ and ‘!^’ to search for null values.

Interactively in a field, I using as well ‘=’ and ‘!=’ leads to the same results as using ‘^’ and ‘!^’.

Don’t know where this inconsistency comes from, though.

Regards, Robert

ROCLASI:
You can use ‘=’ and ‘!=’ (or ‘>0’) for finding empty fields and filled fields.

Hope this helps.