How can I create unique keys

Classic Servoy
1

I want to have a field with unique content. I can’t set a ‘unique’ property on the database field.

Is the only (best) possible way to check for the existence of that content throught a SQL query or are there any other suggestions?

Thanks…

2

IT2BE:
I want to have a field with unique content. I can’t set a ‘unique’ property on the database field.

Is the only (best) possible way to check for the existence of that content throught a SQL query or are there any other suggestions?

I’m just thinking on the fly here, but it seems like this should be something you could do as part of your onDataChanged or onFocusLost method for the field.

Yes, you would likely need to use the getDataSetByQuery using the value in the field and if a zero, empty, null or -1 is returned then the value is ok. (Can you tell I haven’t done this? I don’t know what the return value is)

But this seems like the way to do it.

3

Hai Matt,

this is the complete method now (including check on an empty field and a loop that keeps on checking:

organizationalcode = organizationalcode.toUpperCase();

var check = 0;

while (check == 0)
{
if (organizationalcode == “”)
{
var message = “The code is empty.\nYou need to enter a code…”;

var answer = plugins.dialogs.showInputDialog(‘Let op…’, message);

organizationalcode = answer.toUpperCase();
}

var query = “”;

query = query.concat(“SELECT organizationalcode,organizationid FROM organization WHERE organizationalcode = '”, organizationalcode, “'”);

var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, 1);

if (dataset.getMaxRowIndex() > 0 && dataset.getValue(1,2) != organizationid)
{
var message = “The code '”;

message = message.concat(dataset.getValue(1, 1), “’ existsl. \nYou need to enter a new code…”);

var answer = plugins.dialogs.showInputDialog(‘Let op…’, message);

organizationalcode = answer.toUpperCase();
}
else if (organizationalcode != “”)
{
check = 1;
}
}

4

BTW I do this onDataChange…

5

I am using this method you gave here to check for unique values and for not null constraints. The method gets called onDataChange of a field and checks the other fields or combinations (for unicity or not null).

It is a good method to use when the user updates data. But at creation of a record, it is not nice that the user gets the dialogs telling him that the fields are empty, because he did not get to fill them.

Do you have any suggestion on how I can do the checks in a better way?

Sabina

6

Use a (global) var newrecord and set it to 1 when you create a new record. But… When you create a new record this script should not be called. You don’t edit your field so nothing should happen.

My solution at least doesn’t fire the method until the data is really changed by the user…

Something else happens here I guess.