[Solved] Pattern Field for variable text length

Creating and using forms

[Solved] Pattern Field for variable text length

Postby Maax555 » Mon Apr 16, 2018 11:09 am

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
Last edited by robleyd on Fri Apr 20, 2018 2:07 am, edited 1 time in total.
Reason: Tagged [Solved] [robleyd, Moderator]
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
Maax555
 
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Pattern Field for variable text length

Postby Villeroy » Mon Apr 16, 2018 3:54 pm

Do you have a set with all possible strings?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26751
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Pattern Field for variable text length

Postby Maax555 » Mon Apr 16, 2018 4:04 pm

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.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
Maax555
 
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Pattern Field for variable text length

Postby Villeroy » Mon Apr 16, 2018 4:39 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26751
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Pattern Field for variable text length

Postby Maax555 » Tue Apr 17, 2018 8:41 am

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.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
Maax555
 
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Pattern Field for variable text length

Postby Villeroy » Tue Apr 17, 2018 4:34 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26751
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Pattern Field for variable text length

Postby Maax555 » Thu Apr 19, 2018 4:23 pm

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.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
Maax555
 
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am


Return to Forms

Who is online

Users browsing this forum: No registered users and 1 guest