OpenOffice Base Field Template help?

Creating tables and queries

OpenOffice Base Field Template help?

Postby specialvat » Tue Feb 02, 2016 6:04 pm

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
Open Office 4.1.2
specialvat
 
Posts: 5
Joined: Mon Nov 09, 2015 11:29 am

Re: Openoffice Base Field Template help?

Postby F3K Total » Tue Feb 02, 2016 7:29 pm

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:
SN.PNG
SN.PNG (2.55 KiB) Viewed 2595 times
E.g. with this query:
Code: Select all   Expand viewCollapse view
SELECT "ID", '12000' || REPEAT( '0', 3 - LENGTH( "ID" ) ) || "ID" || 'C10' AS "SN","TEXT" FROM "tbl_serialnumbers"

find attached an example.
R
Attachments
serialnumbers.odb
(3.57 KiB) Downloaded 77 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
F3K Total
Volunteer
 
Posts: 950
Joined: Fri Dec 16, 2011 8:20 pm

Re: OpenOffice Base Field Template help?

Postby specialvat » Wed Feb 03, 2016 10:06 am

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
Open Office 4.1.2
specialvat
 
Posts: 5
Joined: Mon Nov 09, 2015 11:29 am

Re: OpenOffice Base Field Template help?

Postby UnklDonald418 » Wed Feb 03, 2016 8:25 pm

Perhaps something like
Code: Select all   Expand viewCollapse view
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.7 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1286
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: OpenOffice Base Field Template help?

Postby specialvat » Thu Feb 04, 2016 10:10 am

Hi
Were do I put the code?
I am new to this sorry.
Open Office 4.1.2
specialvat
 
Posts: 5
Joined: Mon Nov 09, 2015 11:29 am

Re: OpenOffice Base Field Template help?

Postby UnklDonald418 » Thu Feb 04, 2016 8:56 pm

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.
Code: Select all   Expand viewCollapse view
SELECT "SN" FROM "YourTable" WHERE "SN" NOT LIKE '12000___C10'

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.
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.7 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1286
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: OpenOffice Base Field Template help?

Postby UnklDonald418 » Fri Feb 05, 2016 11:05 pm

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.
Attachments
ValSerNum.odb
(20.08 KiB) Downloaded 76 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.7 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1286
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: OpenOffice Base Field Template help?

Postby Nocton » Fri Feb 19, 2016 4:40 pm

Can't you use a pattern or formatted or pattern field control on your form?

Regards

Nocton
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 508
Joined: Fri Nov 05, 2010 10:27 am
Location: UK


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 3 guests