[Solved] Data format

[Hagar Delest]

Not sure how to get this correct?

Thank you.

[Villeroy]

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.

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

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

[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

[Villeroy]

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"

[jack&Jill]

I've got to go back and dbl. check, I'm getting an error.
Thank you for the reply.

[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: 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

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