Page 1 of 1

[Solved] Data format

Posted: Mon Oct 23, 2017 10:49 pm
by jack&Jill
Not sure how to get this correct?

Thank you.

Re: data format

Posted: Tue Oct 24, 2017 12:39 am
by Villeroy
menu:Tools>SQL...

Code: Select all

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.

Re: data format

Posted: Wed Oct 25, 2017 5:14 pm
by jack&Jill
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.

Re: data format

Posted: Wed Oct 25, 2017 7:43 pm
by Villeroy
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

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'.

Solved Re: data format

Posted: Wed Oct 25, 2017 9:23 pm
by jack&Jill
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

Re: data format

Posted: Wed Oct 25, 2017 10:52 pm
by Villeroy
With HSQL2:

Code: Select all

SELECT TO_CHAR("Date",'MMDDYY')|| LPAD(CAST(RAND()*30 AS TINYINT)+1,2,'0') || 'wo' AS "Concatenate"
FROM "TableX"

Re: data format

Posted: Thu Oct 26, 2017 12:14 am
by jack&Jill
I've got to go back and dbl. check, I'm getting an error.
Thank you for the reply.

Re: data format

Posted: Thu Oct 26, 2017 11:26 am
by Villeroy
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: viewtopic.php?f=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

Re: data format

Posted: Sat Oct 28, 2017 7:18 pm
by jack&Jill
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.