OpenOffice Base Field Template help?
-
- Posts: 5
- Joined: Mon Nov 09, 2015 11:29 am
OpenOffice Base Field Template help?
Hi I'm new to open office base.
I have created a database which serial numbers get entered into it.
I have limited the field in edit to 11 characters but you can type in longer numbers and nothing happens.
For example.
12000657C10
I need to check the data is enter in this form.
I have tried setting the format to:
"12000"###"C10"
But this just add the same number when i tab onto the next field.
There must be an easy way to do this?
The data only change the middle 3 digits and the rest should be identical.
Where I am going wrong?
Is there a way to highlight it if it is enter incorrectly?
Please help.
Regards
Specialvat
I have created a database which serial numbers get entered into it.
I have limited the field in edit to 11 characters but you can type in longer numbers and nothing happens.
For example.
12000657C10
I need to check the data is enter in this form.
I have tried setting the format to:
"12000"###"C10"
But this just add the same number when i tab onto the next field.
There must be an easy way to do this?
The data only change the middle 3 digits and the rest should be identical.
Where I am going wrong?
Is there a way to highlight it if it is enter incorrectly?
Please help.
Regards
Specialvat
Open Office 4.1.2
Re: Openoffice Base Field Template help?
Hello,
it's not possible to generate serialnumbers as you like, using Base as it's shipped. The embedded database HSQL version 1.8.10, does not support this.
But you can use a workaround. It's possible to set an ID integer as autovalue, means each new row will increase the ID by one. Then you can use concat || to put your number together: E.g. with this query:
find attached an example.
R
it's not possible to generate serialnumbers as you like, using Base as it's shipped. The embedded database HSQL version 1.8.10, does not support this.
But you can use a workaround. It's possible to set an ID integer as autovalue, means each new row will increase the ID by one. Then you can use concat || to put your number together: E.g. with this query:
Code: Select all
SELECT "ID", '12000' || REPEAT( '0', 3 - LENGTH( "ID" ) ) || "ID" || 'C10' AS "SN","TEXT" FROM "tbl_serialnumbers"
R
- Attachments
-
- serialnumbers.odb
- (3.57 KiB) Downloaded 221 times
- MMove 1.0.6
- Extension for easy, exact positioning of shapes, pictures, controls, frames ...
- my current system
- Windows 10 AOO, LOLinux Mint AOO, LO
-
- Posts: 5
- Joined: Mon Nov 09, 2015 11:29 am
Re: OpenOffice Base Field Template help?
Hi sorry
Seems we are slightly off piste.
The serial number don't need to be generated.
They are scanned in and i need some sort of data validation.
They need to 11 characters in length.
The format will always be "12000"###"C10"
So I also need to validate the format.
Thanks for your speedy help much appreciated.
Specialvat
Seems we are slightly off piste.
The serial number don't need to be generated.
They are scanned in and i need some sort of data validation.
They need to 11 characters in length.
The format will always be "12000"###"C10"
So I also need to validate the format.
Thanks for your speedy help much appreciated.
Specialvat
Open Office 4.1.2
-
- Volunteer
- Posts: 1547
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: OpenOffice Base Field Template help?
Perhaps something like
Code: Select all
SELECT "SN" FROM "YourTable" WHERE "SN" NOT LIKE '12000___C10'
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
-
- Posts: 5
- Joined: Mon Nov 09, 2015 11:29 am
Re: OpenOffice Base Field Template help?
Hi
Were do I put the code?
I am new to this sorry.
Were do I put the code?
I am new to this sorry.
Open Office 4.1.2
-
- Volunteer
- Posts: 1547
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: OpenOffice Base Field Template help?
As you are discovering, Base doesn't offer much in the way of run time data validation, in fact a Primary Key violation is about it. And even that is done silently, it just won't let you move the cursor from the offending line.
would be run as a Query to display violations already in your table. One caveat is that the '_' wildcard matches any character, not just numbers.
Apparently there are some flavors of SQL that would accept '12000[0-9][0-9][0-9]C10' but the 'HSQL database engine' that comes with oOBase doesn't support that.
Since you mentioned you are new to Base I would add a warning. Sometimes Base crashes without warning and upon recovery you may discover that your data tables have vanished, so backup you obd files frequently. When copying to a different directory on the same drive Windows apparently just saves a link to the original file, so it too would be corrupt. Either copy them to a different logical drive or use SaveAs to store the backup with a different name. OO does have an automatic backup feature, but I have found it to be unreliable.
I will continue to look at your problem as time allows. but so far I don't see any easy solution.
Code: Select all
SELECT "SN" FROM "YourTable" WHERE "SN" NOT LIKE '12000___C10'
Apparently there are some flavors of SQL that would accept '12000[0-9][0-9][0-9]C10' but the 'HSQL database engine' that comes with oOBase doesn't support that.
Since you mentioned you are new to Base I would add a warning. Sometimes Base crashes without warning and upon recovery you may discover that your data tables have vanished, so backup you obd files frequently. When copying to a different directory on the same drive Windows apparently just saves a link to the original file, so it too would be corrupt. Either copy them to a different logical drive or use SaveAs to store the backup with a different name. OO does have an automatic backup feature, but I have found it to be unreliable.
I will continue to look at your problem as time allows. but so far I don't see any easy solution.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
-
- Volunteer
- Posts: 1547
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: OpenOffice Base Field Template help?
I fooled around with SQL a little more with no success so I hacked together a macro to do the data validation.
I uploaded an obd file that you can test to see if it works as you want. If you open the Form “ValidateSN001” it will display a list of serial numbers. You can select one from the list or you can select an empty line to enter a new SN and press the Validate button. A input box window will pop up and allow you to edit/enter the SN. When you press the OK button it will either tell you what it saw wrong or it will write the valid number back on the form. The way the table is set up on the form, it won't allow you to enter duplicate SN's. Unfortunately, it is that silent Primary Key Violation I mentioned in my last post.
This odb file also includes the Query I had posted earlier so you can see how it works.
If this is what you are looking for then we can discuss how you might implement it in your database.
I uploaded an obd file that you can test to see if it works as you want. If you open the Form “ValidateSN001” it will display a list of serial numbers. You can select one from the list or you can select an empty line to enter a new SN and press the Validate button. A input box window will pop up and allow you to edit/enter the SN. When you press the OK button it will either tell you what it saw wrong or it will write the valid number back on the form. The way the table is set up on the form, it won't allow you to enter duplicate SN's. Unfortunately, it is that silent Primary Key Violation I mentioned in my last post.
This odb file also includes the Query I had posted earlier so you can see how it works.
If this is what you are looking for then we can discuss how you might implement it in your database.
- Attachments
-
- ValSerNum.odb
- (20.08 KiB) Downloaded 213 times
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: OpenOffice Base Field Template help?
Can't you use a pattern or formatted or pattern field control on your form?
Regards
Nocton
Regards
Nocton
OpenOffice 4.1.12 on Windows 10