[Solved] Data format

Creating tables and queries

[Solved] Data format

Postby jack&Jill » Mon Oct 23, 2017 10:49 pm

Not sure how to get this correct?

Thank you.
Attachments
date issue.jpg
Last edited by Hagar Delest on Sun Nov 05, 2017 11:07 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 4.01 on Windows 7
jack&Jill
 
Posts: 15
Joined: Fri Mar 28, 2014 8:42 pm

Re: data format

Postby Villeroy » Tue Oct 24, 2017 12:39 am

menu:Tools>SQL...
Code: Select all   Expand viewCollapse view
ALTER TABLE "X" ALTER COLUMN "Date" SET DEFAULT CURRENT_DATE

[Execute]
menu:View>Refresh Tables
Now you get the current date in every new record unless you enter some other date.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25750
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: data format

Postby jack&Jill » Wed Oct 25, 2017 5:14 pm

Thank you,

Yes, that works.
Is it possible to have it also read as such?

Today date and example: 102517??wo , followed by a random # of 0 to 30 plus the two letters of wo: results = 10251729wo

Thank you.
OpenOffice 4.01 on Windows 7
jack&Jill
 
Posts: 15
Joined: Fri Mar 28, 2014 8:42 pm

Re: data format

Postby Villeroy » Wed Oct 25, 2017 7:43 pm

The following query assumes that you use the emedded HSQLDB which is shipped with the office suite. The status bar of the document window indicates the actual database in use.
That database is documented here: http://www.hsqldb.org/doc/1.8/guide/ch09.html
Code: Select all   Expand viewCollapse view
SELECT "Date",
right('0'|| Month("Date"),2) || right('0' || Day("Date"),2) || right('0' || Year("Date"),2)|| right('0' || cast(rand()*30 AS TINYINT) +1, 2)  || 'wo' AS "Concatenation"
FROM "TBL"

It would be easier with a database better than HSQL 1.8.
The functions are documented in the above linked document. || is the text concatenation operator analog to & in a spreadsheet.

We generate a 2-digit month string by concatenating a '0' with the month of the date in column "Date" which gives 01...012. from that string we get cut off the rightmost 2 characters and get 01...12.
We do the same with the day number and the year number of the date in column "Date".
Then we use the Rand() function which returns a floating point number between 0 and 1, not including the 1. We multiply this random decimal with 30 and convert (cast) the result to a number of type TINYINT. The conversion cuts off the decimals after the point. It does not do any rounding. So we get a number between 0 and 29 to which we have to add 1 in order to get a random integer between 1 ... 30. With this integer of one or two digits we do the same trick as we did with the month, day and year in order to get 2 digits 01...30. Finally we concatenate the literal 'wo'.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25750
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Solved Re: data format

Postby jack&Jill » Wed Oct 25, 2017 9:23 pm

Thank you for the coding and link.

After reading, that you should keep your data separate from the db front end,
I will see if there might be a better way for me to do this.

Thank you
OpenOffice 4.01 on Windows 7
jack&Jill
 
Posts: 15
Joined: Fri Mar 28, 2014 8:42 pm

Re: data format

Postby Villeroy » Wed Oct 25, 2017 10:52 pm

With HSQL2:
Code: Select all   Expand viewCollapse view
SELECT TO_CHAR("Date",'MMDDYY')|| LPAD(CAST(RAND()*30 AS TINYINT)+1,2,'0') || 'wo' AS "Concatenate"
FROM "TableX"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25750
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: data format

Postby jack&Jill » Thu Oct 26, 2017 12:14 am

I've got to go back and dbl. check, I'm getting an error.
Thank you for the reply.
OpenOffice 4.01 on Windows 7
jack&Jill
 
Posts: 15
Joined: Fri Mar 28, 2014 8:42 pm

Re: data format

Postby Villeroy » Thu Oct 26, 2017 11:26 am

The simplified formula works with HSQL2 as documented here: http://hsqldb.org/doc/guide/builtinfunc ... _functions
TO_CHAR creates a formatted string from a date.
LPAD fills up a short string to the left.

You can easily convert your embedded HSQL1.8 to HSQL2:
1) Put a copy of your odb into a separate directory and add a subdirectory "driver".
2) Download HSQL version 2.3.4 (there is a little problem between the latest 2.4 and Base) and extract lib/hsqldb.jar to the driver directory.
3) Download and extract this script: https://forum.openoffice.org/en/forum/v ... 21&t=86071
4) Open the odb and run the macro. It will extract the embedded database to subdirectory "database" and connect the Base document to that database using the provided driver/hsqldb.jar
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25750
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: data format

Postby jack&Jill » Sat Oct 28, 2017 7:18 pm

Thank you.

Worked as described. I am going to go back and reread and review some other things for Tables:
for: clients, client contacts, order, order discounts, phone#/s, email/s, ship to addresses, product, assembled product, invoice.
inventory, and I'm sure this will grow as I discover other things I've missed.

(1 customer has multiple people able to order at multiple state locations,
{sometimes I need to send directly to a individual's address, rather than the state location, but normally just once}
I though I might handle this on the fly as needed rather that slow my progress down at this time).

specific to this client, order discounts, example 1 to 5 $, 6-10 $, etc;

My thinking is to list the tables in calc, then drag sheets into base (tables) in case i need to alter or do a qry on my base table to extract.
OpenOffice 4.01 on Windows 7
jack&Jill
 
Posts: 15
Joined: Fri Mar 28, 2014 8:42 pm


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 5 guests