Page 1 of 1

[Solved] Pattern Field for variable text length

PostPosted: Mon Apr 16, 2018 11:09 am
by Maax555
Hi, is there a way to use a pattern field for text entry that is variable length. For example in a name field where the entry could be "John", Joe" or Jonathan" for example?
I simply want to ensure all text entered into a field is upper case only. The actual text entered could be a combination of letter and numbers, for example "1234567/A", "ABCDEF/1" or "ABCD123/A1"

thanks

Re: Pattern Field for variable text length

PostPosted: Mon Apr 16, 2018 3:54 pm
by Villeroy
Do you have a set with all possible strings?

Re: Pattern Field for variable text length

PostPosted: Mon Apr 16, 2018 4:04 pm
by Maax555
Hi, not sure i follow "Do you have a set with all possible strings?" The data entered could be anything from 5 characters to 30 in any combination of letters or numbers.

Re: Pattern Field for variable text length

PostPosted: Mon Apr 16, 2018 4:39 pm
by Villeroy
If you had a few thousands of these IDs in a table, you could fill a list box with them and choose one by typing into the list box.
With HSQL you can use a field of type VARCHAR_IGNORECASE(length) where upper/lower case makes no difference.
In a query, UPPER("field name") represents the upper case version of the string.

Re: Pattern Field for variable text length

PostPosted: Tue Apr 17, 2018 8:41 am
by Maax555
Ah ok, no then. The user will enter the data which will then be saved. The main reason for doing this to be honest is to avoid entering duplicates. I had already used index design to specify unique field but it was allowing the same text if any of the characters were in a different case. So I already had PartNumber Part123/A in the data base it was also allowing an upper/lowercase variation like part123/A or Part123/a.

Re: Pattern Field for variable text length

PostPosted: Tue Apr 17, 2018 4:34 pm
by Villeroy
Simply change the column type from VARCHAR to VARCHAR_IGNORECASE and add an unique index on that column. Now you can not enter any duplicates, no matter if they are upper, lower or mixed case.

Re: Pattern Field for variable text length

PostPosted: Thu Apr 19, 2018 4:23 pm
by Maax555
Villeroy wrote:Simply change the column type from VARCHAR to VARCHAR_IGNORECASE and add an unique index on that column. Now you can not enter any duplicates, no matter if they are upper, lower or mixed case.


OMG. I did not even see that field type. I looked through the field types several times over trying to find this and missed it several time.

many thanks as this has been a major headache for me.