Scripting in Base??

[AlgonquinPeak]

Many years ago I was an MS Access developer, and my databases usually relied pretty heavily on the scripting language VBA.

Using VBA allowed me to turn MS Access into a usable application for end-users with no knowledge of databases.

Is there any practical way to do scripting in Base?

Here is an example of where scripting is nice...

I am trying to build an expense database for myself at home. When I enter in a new receipt, I would like LibreOffice to take value in the "Order Date' field, count how many receipts may have been entered with that date, add one to that number, and then concatenate that value to the date to create an "Order Number".

Let's say I had already entered in the following...

OrderNumber OrderDate Description Amount
20190501_01 2019-05-01 Gas $40.27
20190501_02 2019-05-01 Groceries $121.94
20190501_03 2019-05-01 Lunch $7.20

When I go to enter in a new receipt for the day, my application would calculate the next OrderNumber as: "20190501_04"

How hard is it to do things like that in LibreOffice Base??

[RoryOF]

OpenOffice BASIC will do quite a lot. A manual is at https://wiki.openoffice.org/wiki/Docume ... ASIC_Guide

Greater detail is in the works of Andrew Pitonyak, which can be downloaded from http://www.pitonyak.org/oo.php.

[AlgonquinPeak]

OpenOffice BASIC will do quite a lot. A manual is at https://wiki.openoffice.org/wiki/Docume ... ASIC_Guide

Greater detail is in the works of Andrew Pitonyak, which can be downloaded from http://www.pitonyak.org/oo.php.

Are you fluent in OO Basic?

How hard would it be to do what I described?

Or am I wasting my time with such things in LibreOffice?

[RoryOF]

No; I live a very simple life and try to avoid programming.

One can program almost anything, given enough time.

[Villeroy]

Keep it simple. Let your backend database create a date stamp and an auto-ID. I know, this is not the same but it will work. You can query the creation date and you have a primary key. Programming Base with StarBasic is a nightmare.

[AlgonquinPeak]

Keep it simple. Let your backend database create a date stamp and an auto-ID. I know, this is not the same but it will work. You can query the creation date and you have a primary key. Programming Base with StarBasic is a nightmare.

The whole reason I am looking into LibreOffice Base is because there is no easy way for me to create a web-based subform without doing some fancy Javascript coding. (I can eventually learn how to do that, but don't have the time now.)

In the example I gave above, I was hoping to do that so that when I scan in a receipt I would have a *meaningful* ID for the receipt.

If I just use "OrderID", having an integer on a paper receipt and the scanned version is useless. But having an "OrderDate" plus a sequence # appended to it gives meaning in the real world.

Not the end of the world, but nice-to-have.

So it sounds like Base doesn't offer much, huh?

Should I just use it for it's built in subform control, and then use that to get all of my receipts entered in, and then build something more robust using web languages?

[Villeroy]

A unique number can not be useless. You enter it into a search field and get the correct record.

[AlgonquinPeak]

A unique number can not be useless. You enter it into a search field and get the correct record.

You didn't read what I said...

I wasn't talking about a unique ID for the records...

I said I wanted to create a unique value - that is also self-identifying - which I can use to write on paper receipts, and then use as the filename for the scanned in receipt. (I suppose I could also prepend things with "rcpt".)

So if I keyed in a new entry today (i.e. "2019-06-17"), maybe the record ID would 27, but I would like my expense database to display a number like ("20190617_001") which I can then write onto my paper receipt - in case I keep them filed away - and which I can use to re-name the scanned in image to "rcpt_20190617_001.jpeg" so that if my database ever blows up or whatever, I have a reasonable way of figuring out what "rcpt_20190617_001.jpeg" refers to! (Hint: It's the first thing that I bought on June 17, 2019!)

Get it now?!

[Villeroy]

There is nothing wrong with "rcpt_9628463_20190617" concatenated from the literal string 'rcpt_", a unique number and the date. It will work. It will serve all thinkable purposes.

[AlgonquinPeak]

There is nothing wrong with "rcpt_9628463_20190617" concatenated from the literal string 'rcpt_", a unique number and the date. It will work. It will serve all thinkable purposes.

Good, you agree!

Now back to what I asked above...

How difficult would it be to code something like that in LibreOffice Base using any scripting features?

If this was a web page that I wrote in PHP, I would know how to accomplish the above functionality. Likewise, it could be done using VBA in MS Access.

It sounds like doing any scripting in LO Base is painful?!

[Villeroy]

How "difficult"? Depends on your qualification? If you are a professional programmer not shying away from a very complex API then you may take some 3 or 4 weeks of intense learning until you get to the point where you get things straight on the frontend. If you are proficient with SQL you know about triggers on the backend. This is all beyond my skills. I keep things simple.

[robleyd]

It may be that HSQL supports a NOW() function which gives you a timestamp to the second, or CURRENT_DATE() which gives you a date - the latter could be concatenated with a unique ID; all can be done using SQL alone, thus avoiding any scripting.

[Villeroy]

The embedded HSQL is documented here: http://www.hsqldb.org/doc/1.8/guide/ch09.html
This is all you need to know. Simple, standard conformant SQL.
Right at the end of that page:

When specifying default values for date / time columns in CREATE TABLE statements, or in SELECT,INSERT, and UPDATE statements, special SQL functions: NOW, SYSDATE, TODAY, CURRENT_TIMESTAMP, CURRENT_TIME and CURRENT_DATE (case independent) can be used. NOW is used for TIME and TIMESTAMP columns, TODAY is used for DATE columns. The data and time variants CURRENT_* are SQL standard versions and should be used in preference to others. Example:

CREATE TABLE T(D DATE DEFAULT CURRENT_DATE);
CREATE TABLE T1(TS TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

For an existing table:
ALTER TABLE T ALTER COLUMN D SET DEFAULT CURRENT_DATE);
ALTER TABLE T ALTER COLUMN D SET DEFAULT CURRENT_TIMESTAMP);

[Nocton]

You can write some relatively simple Basic code to do useful things. It is not as complicated as has been suggested and there is lots of help on this forum. However, you should carefully study the first link that RoryOF provided. To make nice, menu driven, applications for anyone to use you do need some coding I think.
However, as suggested, I should use SQL and the Query wizard as far as possible to do what you require. I should certainly use an Auto Integer as the Primary Key/ID for each table, and simply generate your receipt code as you wish as a field in the table. That way you are certain to have no problems with uniqueness and data entry. Let the software do the hard work!

Finally, referring to the example you have given, that would be relatively easy to programme using OO/LO Basic. You can grab easily the data from fields on a form, do the arithmetic required and then update table/form.