find null or empty values

Classic Servoy
1

Hello everybody.

I`ve got an issue with finding data which can contain null or empty values.

How can I get both values in one singleline “AND” search request (Servoy find request).

To give you an idea what I`m doing:

I offer the clients to store there find requests as they have entered it and run it anytime they like to, using a single find form on which all the data can be reached.

Is it possible using an OR statement within a AND search for these finds eg. ^||=
On which the Servoy search engine translates it into

fieldname IS null OR fieldname = ''
2

I think you mean:
controller.find()
fieldname = “^”
controller.newRecord()
fieldname = “=”
controller.search()

3

Hello Jan,

Let me explain it a bit.

Search request entered by an User:

Field1 = Value1
Field2 = null or empty
Field3 = Value2
Field4 = Value3
Field5 = null or empty

Because SQL differs between null and empty values you always have to duplicate your request (the user is not aware of this so his found set is not, what he wants to ask).

The method I`ve created runs through all the fields on a find form and alters the value according to fieldtype and stores the value if desired.

if (forms.Find_Calls.calldescription)
	{
		if (utils.stringPatternCount(forms.Find_Calls.calldescription, '!'))
			{
				var GT_Value = '!' + utils.stringReplace('%' + forms.Find_Calls.calldescription.replace('!', '') + '%', '%=%', '=')
			}
		else
			{
				var GT_Value = utils.stringReplace('%' + forms.Find_Calls.calldescription + '%', '%=%', '=')
			}
		var GT_FieldName = forms.Find_Calls.elements.calldescription.getName()
		forms.Find_Calls.calldescription = GT_Value
		if (globals.GI_Find_Request_Stored == 1)
			{
				findid_to_find_settings.newRecord()
				findid_to_find_settings.field_name = GT_FieldName
				findid_to_find_settings.field_value = GT_Value
				findid_to_find_settings.table_name = GT_TableName
				findid_to_find_settings.form_name = GT_FormName
			}
	}

With an OR requests to split up null and empty request. I`ve got to run through all the fields and then after all fields are processed. when one or more fields has been entered as null or empty, duplicate that search record and replace the values, which were entered as null or empty, from eg. null to empty. This complicates it quit a bit.

I believe that most find requests initialized by users wanting a to find an empty value dont care if its a virgin field (null) or a cleared field (empty).

Thats why Im asking to implement such a find in the standard servoy search method (as option or by entering ‘^||=’ as search request) if this is not possible yet.

Field1 = Value1 (Field2 IS null OR Field2 = '') Field3 = Value2 Field4 = Value3 (Field5 IS null OR Field5 = '')
4

So you actually want an operator which automaticly translates in:
(x not null or x = ‘’)
something like “^=” ?

5

Hello Jan,

Yes That`s exactly what I want.

btw I think you mean

(x IS null or x = ‘’)

instead of

(x NOT null or x = ‘’)

Operators ‘^=’ and ‘!^=’ will do fine.

A long story for such a small question. :lol: :lol: :lol:

Rene

6

Will be implemented in Servoy 2.2 beta
^= in a textfield/column will result in the SQL (x IS NULL or x = ‘’)

7

Thanks a lot Jan.

8

should the following also work? (or is there another way to achive the same…)

var test = plugins.dialogs.showSelectDialog("Test","Select a value",new String[]{"value 1","value 2"});
if (test ^= null)
{
   return;
}
else
{
   action 2;
}

I tried this and if “plugins.dialogs.showSelectDialog” returned null or “”, the test value was set to 0 by “test ^= null”

Paul

9

no the “^=” will not work in a JavaScript expression, use:
if (a == null || a == “”)

If doing a find from within a script the “^=” will work as field data, like:
controller.find()
customername = “^=” //search for null or empty
controller.search()

10

ok, too bad… I thought this might cut back even a little bit more on the coding involved :D

Paul

11

This should work..

var test = plugins.dialogs.showSelectDialog("Test","Select a value",new String[]{"value 1","value 2"}); 
if (test) // has a value?
{...
12

:D cool, even less to type…

Paul

13

What a great new feature!
To be able to do a null/empty search now just using ‘^=’ is fantastic!

Thanks guys!

14

This does’nt seem to work with integers attached to check fields!

What I would like to find in just one statement is for 0 and NULL

because if you use an integer, it starts as NULL when you select it, it becomes 1, and if you deselect it, it becomes 0

So if I want to search all NOT selected fields, I need to search for 0 and NULL
Is that possible?

15

It works only for string fields currently, not sure why we did not do this for number fields…want this?

16

yes, but is this also possible?

What I would like to find in just one statement is for 0 and NULL

17

yes,sure, will be in next release