[Solved] Pattern Field for variable text length

Creating and using forms
Post Reply
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

[Solved] Pattern Field for variable text length

Post 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
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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Pattern Field for variable text length

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Pattern Field for variable text length

Post 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.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Pattern Field for variable text length

Post 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Pattern Field for variable text length

Post 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.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Pattern Field for variable text length

Post 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Pattern Field for variable text length

Post 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.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
Post Reply