[Solved] BASE Field Type def's - No auto capitalize?

Discuss the database features
Post Reply
MapCollector
Posts: 7
Joined: Sat Jun 19, 2021 9:17 am

[Solved] BASE Field Type def's - No auto capitalize?

Post by MapCollector »

I'm building a database Entry Form and find there is no "auto-capitalise" function.
One of my inputs is part numbers composed of numerals and capital letters.
To speed up data entry I would like auto-capitilise to auto-stop lowercase letters creeping into the database part numbers.
[ I realise there is a definition that says "Ignore upper/lowercase" for output searches. ]
But I would like the database printout to look tidy/correct, and not be sprinkled with unintentional lowercase.

Is it possible there is a reason for this function being absent inside BASE ? It seems to be lurking everywhere else.
The present solution 'on the table' (not really popular) is to set the keyboard on CapsLock and have all input capitalised.
We can live with all-capitals if that-is-the-way-it-is. :? :?
OpenOffice 4.1.10 32bit
Win 8.1
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: BASE Field Type def's - No auto capitalise (capitalize)

Post by Villeroy »

There is no such thing as a "Base database". All Base documents are connected to some type of database. The type of database connection is indicated in the status bar of the database document window.

Most Base documents are connected to some spreadsheet for mail merge. The built-in functions of these pseudo-databases (sheet, text, dBase) are specified in SQL Functions for file based database drivers

Since you are editing your database, you are most likely connected to a HSQLDB with built-in functions documented here: http://www.hsqldb.org/doc/1.8/guide/ch09.html#N1251E
This is a very old version of HSQL and the only type of database which can be embedded in the Base document. Actually, the database is "installed" (extracted) when you access it for editing and the embedded database is updated when you close an editing session with a table, query or form.

Your Base document may be connected to PostgreSQL, MySQL, MS SQL, Access or some other database providing its own set of functions.

UPPER("PartNum") should work in most cases.
There is also a pattern control. This form control can format user input.
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
MapCollector
Posts: 7
Joined: Sat Jun 19, 2021 9:17 am

Re: BASE Field Type def's - No auto capitalise (capitalize)

Post by MapCollector »

I'm utilising "OpenOffice Database", which is called "Base" on the work screen, a subset of OpenOffice.

This 'accessory' is being used to create a customised data input and output package that can be passed around to libraries for a specific cataloging purpose. It is being kept deliberately simple so that users can modify it without bumping into embedded clip-on subroutines. That is - keep it standard with no clever stuff. :geek:

My posting is about an input problem of no great importance, but I'm wondering if a "Base" user out there may have a simple solution to auto-correcting erroneous input: i.e. a lower case entry that should be upper-case auto-corrected.

If there is a simple subroutine that can be linked to an input in "Base" then I could surround it with an explanation so that students could follow it and maybe tweak it with confidence. On the other hand "CapsLockON" appears to be the sensible solution. 8-)
OpenOffice 4.1.10 32bit
Win 8.1
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: BASE Field Type def's - No auto capitalise (capitalize)

Post by Villeroy »

OpenOffice Base is not a database. It is a tool to work with databases. UPPER is a function which converts all alphanueric letters to upper case. You may also use a pattern field on a form and finally (if HSQL is your actual database) you can add a constraint that raises an error whenever you are going to store a record with lower case letters in the respective field. The actual database you are using is HSQL 1.8 as documented in http://www.hsqldb.org/doc/1.8/guide/ch09.html if the status bar of your document looks like
Image

1) The attached Base document with an embedded HSQLDB has a table with a column TXT of type VARCHAR_IgnoreCase(10). IEven if you would store lower and upper case letters, it would not make any difference.
2) For demonstration purposes I added a constraint to that column:

Code: Select all

ALTER TABLE "TBL" ADD CONSTRAINT "Check_Upper" CHECK ("TXT" = UPPER("TXT"))
If you try to save lower case letters in that column, you get a constraint violation error.
3) On form level I set up a pattern field. It accepts 3 upper case letters followed by a literal dash followed by 3 digits followed by a literal slash followed by 2 upper case letters.
4) Without the constraint, you would be able to enter lower case letters directly into the table which would not make any difference due to the data type. However, a most simple query could convert everything to upper case: SELECT UPPER("TXT") AS "Upper", "ID" FROM "TBL"
Attachments
UPPER.odb
(11.81 KiB) Downloaded 230 times
Last edited by Villeroy on Sun Jun 20, 2021 9:38 am, edited 1 time in total.
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
MapCollector
Posts: 7
Joined: Sat Jun 19, 2021 9:17 am

Re: BASE Field Type def's - No auto capitalise (capitalize)

Post by MapCollector »

Hi Villeroy - I think you have answered my conundrum quite well, in that an invalid input raises an error.
The present draft form input box for the code-number is larger than the other entry boxes to cope with a blue bold 28 point Arial rounded font. This was made large so that the input 'operator' should notice quite quickly that lower case lettering was present.

So what I will do now is return to the two libraries for a discussion on what they would prefer: Either an error prompt or a CapsLockOn input.
Once I hand over the 'package', they can monitor the system themselves as they have in-house IT managers to refer to, and they can also tweak the input settings if they want. This little project is a free contribution to the libraries to solve one of their record-keeping problems.

So thanks Villeroy, I'll list this post as SOLVED in a day or two as I've noticed others viewing this query and your very prompt replies.
:super: :super: :super: :super:
OpenOffice 4.1.10 32bit
Win 8.1
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: BASE Field Type def's - No auto capitalise (capitalize)

Post by Villeroy »

MapCollector wrote:Hi Villeroy - I think you have answered my conundrum quite well, in that an invalid input raises an error.
Execute the constraint statement in the Tools->SQL... dialog and always call Edit->Refresh Tables whenever you did something in that dialog. This will sync the status of the HSQLDB with the user interface of your office suite.

The constraint is not absolutely necessary because upper or lower makes no difference when comparing or sorting the case insensitive field, the pattern field on the form converts all lower to upper case and even if you would manage to get some lower case letters into that field, a query makes them appear as upper case when it comes to reporting.
------------
When you have your database up and running, so it does not need significant changes in table structures, relations, indices and constraints, you may convert the embedded HSQLDB into a stand-alone HSQLDB which provides more data safety and more features.
As long as you use embedded HSQLDB:
-- Close the entire office suite together with the database document when finishing work.
-- Wait a few seconds before you shut down or hibernate the computer.
-- Do your daily backup maintainance.
Any embedded database is a bad choice in respect to data safety. Wrapping the whole database into a Base document takes more time as the database grows. The data will be lost when you power down too quickly. Sooner or later you will be lucky with yesterday's backup.
-----------
Apropos maintainance:

Code: Select all

UPDATE "TBL" SET "TXT" = UPPER("TXT")
could be used to convert any lower case letters to upper case, so we have 5 features to handle this. Column type, constraint, pattern field, fix by query with UPPER, fix by UPDATE statement with UPPER.
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
MapCollector
Posts: 7
Joined: Sat Jun 19, 2021 9:17 am

Re: BASE Field Type def's - No auto capitalise (capitalize)

Post by MapCollector »

RE: "Any embedded database is a bad choice in respect to data safety. Wrapping the whole database into a Base document takes more time as the database grows. The data will be lost when you power down too quickly. Sooner or later you will be lucky with yesterday's backup."

OK - I'll be seeing the library people in three or four days and I'll chat to them about the limitations you mention. We expect the database to get large if utilised. I've not looked at form-entry before as I've just worked with raw databases. Can you name any stable "Form Entry" packages that may come up in conversation with their IT Managers? I'm just giving them some free assistance.
OpenOffice 4.1.10 32bit
Win 8.1
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: BASE Field Type def's - No auto capitalise (capitalize)

Post by Villeroy »

Any of the popular external database engines will do if it meets your requirements and if you can connect a Base document to it. Base is NOT the problem. Sooner or later OpenOffice will be a problem because OpenOffice is zombie software. Since many years, there is a successor to OpenOffice called LibreOffice with hundreds of voluteers publishing new releases on a regular basis.

https://www.libreoffice.org/discover/li ... penoffice/

The biggest advantage of LO Base compared to OO Base is that LO for Windows is 64-bit software so you don't need an extra 32-bit runtime as for OO/Windows which still is 32-bit. In addition, a professional report builder is integrated in LO. The same report builder is availlable as an extension to OO.
The rest of the office suite has hundreds of little features that are not in OO, some of them questionable though. The resulting documents are the same (almost).

HSQL is a database product on its own right. If you are satisfied with a small solution made with Libre/OpenOffice Base and its embedded HSQL, then it is rather easy to convert this embedded HSQLDB into an external HSQLDB or even a HSQLDB server which is accessible from multiple clients. I use Base with stand-alone HSQL databases on Windows and Linux since 11 years with no problems at all. I even used to use an embedded HSQL with hundreds of thousands of records in dozends of tables for some time following the above mentioned precautions.
If you switch to some other database engine later, you can still use Libre/OpenOffice Base with that one. If the structure of tables and columns is the same, you can reconnect the same Base document with its queries, forms and reports from HSQL to MySQL or whatever you prefer. Just like MS Access with its JET database engine, Base is not a database. It is a tool set to work with databases (much smaller and much more primitive than Access, though). Database professionals prefer using MS Access with database engines other than embedded JET. They connect Access documents with PostgreSQL, MySQL or whatever.

[Database Backend storing data in tables and columns] <--> [Database Frontend]
[HSQL, MySQL, PostgreSQL, JET] <--> [Open/LibreOffice Base]
[HSQL, MySQL, PostgreSQL, JET] <--> [MS Access]
[HSQL, MySQL, PostgreSQL, JET] <--> [Web server composing web pages from database content] this forum is a MySQL database.
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
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: BASE Field Type def's - No auto capitalise (capitalize)

Post by dreamquartz »

We have been using a TRIGGER to make sure information is CAPITALIZED as such that it does not matter if the entry is or is not.

For data safety and stability we are also using a split database.
viewtopic.php?f=83&t=65980
There is ample information made available by Master Villeroy.
One of the tools related to a split database is called FreeHSQLDB.
viewtopic.php?f=21&t=77543

Code: Select all

CREATE TRIGGER PUBLIC.POSTALCODE_BU BEFORE UPDATE ON PUBLIC."tPostalCode"
REFERENCING NEW ROW AS NEWROW FOR EACH ROW
BEGIN ATOMIC
SET NEWROW."PostalCode" = UPPER(NEWROW."PostalCode");
END
When the database is 'split, you can enter the code via Direct SQL.
It 'looks' @ the table tPostalCode and before anything is added to the table, the info will be CAPITALIZED.

Hopes this helps a little,

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
MapCollector
Posts: 7
Joined: Sat Jun 19, 2021 9:17 am

Re: BASE Field Type def's - No auto capitalise (capitalize)

Post by MapCollector »

OK - Thanks folks for expertise.
I can now retreat and sort out the project.
BYE
OpenOffice 4.1.10 32bit
Win 8.1
Post Reply