I have a parallel table of users and a form where the user enters a proposed new user name. It’s designed to test first whether the name proposed already exists in the Users table.
Servoy’s security area is case insensitive. Add a user called “name” and then try to create another user called “Name”, and you’ll be rebuffed.
I’ve discovered the following code is case sensitive. How can it be made case insensitive – that is to test for both “name” and “Name”?
If ( gname$users_to_users.username )
you can do something like ```
if (new_name.toLowerCase == security_name.toLowerCase)
IT2BE:
you can do something like ```
if (new_name.toLowerCase == security_name.toLowerCase)
I get errors.
I want to test whether either global.gname = “name” or global.gname = “Name” has a match in a table. I suspect there’s a function to ignore case in seeking a match but haven’t been able to find it.
Did you copy it one-to-one? Because I forgot the ‘()’ at the end… of .toLowerCase()
IT2BE:
Did you copy it one-to-one? Because I forgot the ‘()’ at the end… of .toLowerCase()
Yes, I included the brackets.
Here’s my code. “gname” is a global into which the user has entered a proposed name to be created as a user. The table of users is called “security”. The relationship gname$security_to_security has global.gname as primary key and “user_name” as the foreign in a self relationship.
If ( global.gname.toLowerCase() == gname$security_to_security.user_name.toLowerCase() )
{
plugins.dialog.showInfoDialog('User exists!')
}
else
{
plugins.dialog.showInfoDialog('User does NOT exist.')
}
The desired result is that EITHER “name” or “Name” will be detected as already existing.
If I substitute the following in the IF statement:
If ( gname$security_to_security.user_name )
then only an EXACT case match will be detected.
I suspect there must be a function to ignore case when evaluating matches but haven’t found it yet.
Hi Morley,
If ( global.gname.toLowerCase() == gname$security_to_security.user_name.toLowerCase() )
{
plugins.dialog.showInfoDialog(‘User exists!’)
}
else
{
plugins.dialog.showInfoDialog(‘User does NOT exist.’)
}
The above code is forcing the global name to lowercase and then testing the related name data which is based upon that global which is only then trying to impose lowercase formatting on the related data.
It is resolving the relationship first before it tries to format it as lowercase - thus you will never get a true result from it.
And as you rightly say, the simple boolean test via the relationship will only return a value when there is an exact match.
Could you either :
Hmm, problems.
The above code is forcing the global name to lowercase and then testing the related name data which is based upon that global which is only then trying to impose lowercase formatting on the related data.
It is resolving the relationship first before it tries to format it as lowercase - thus you will never get a true result from it.
Makes emminent sense.
Could you either :
- Run a query based upon the global field (which is then case insensitive by default) and if there is a found set then you have a match ?
- Have a calculated field in the security table which returns the lowerCase() version of the name field and then have the relationship point to that field?
Didn’t understand the first suggestion so I tried the second.
Created a field called “user_name_lc”, created a calc based on it with code “return user_name.toLowerCase() ;”. Tested, it does indeed display lower case equivalents of the “user_name” field.
Then revised the existing “gname$security_to_security” relationship with a match between gname and user_name_lc.
Then tried the following:
If ( gname$security_to_security )
If ( gname.toLowerCase() == gname$security_to_security.user_name )
If ( gname.toLowerCase() == gname$security_to_security.user_name_lc )
ALL of them see “Test9” as NOT a match for “test9”.
I was sure your logic was/is impecable. Puzzled. Perhaps your first idea might work, but I need clarification on it.
My first idea is to take the global name entered and perform a find in the security table using the global name as a search criteria.
If the find returns a found set > 0 then you have a match !
I will do some testing on the other idea !!
Harry
Doing a Find does work.
I’m puzzled why your relationship method doesn’t. I prefer them for their more compact code. Let me know if you work out that route.
I was originally hoping there was some simple shortcut symbol to force SQL to ignore case.
Hi Morley,
I did some checking and the relational tests that I did were not showing as being case sensitive and did therefore work !
I have a single table with a name field and a global name field and a relationship between them - there is no lowercase derivative of the original name field !!
I created a method as follows :
ptpCount = databaseManager.getFoundSetCount(projects_to_people_lc)
if ( ptpCount != 0 )
{
//show dialog
var thePressedButton = plugins.dialogs.showInfoDialog(‘STOP’, ‘User Exists’,‘OK’);}
else
{
//show dialog
var thePressedButton = plugins.dialogs.showInfoDialog(‘Carry On’, ‘User does NOT exist’,‘Whew’);}
This method is fired on losing focus on the global field and it worked !
I must admit now to being confused about why the relationship is still valid for text values which are seemingly not equal !!
Anybody shed some light on this ?
I have attached a sample solution which has been created in 2.1 beta 2 build 300…
