[Solved] Date calculaton in Base

[FJK]

Is there an easy way to have a date calculated from in initial date in base (i.e. a due date dated 30 days after a bill date on an invoice)?

I've got my invoicing system working with the exception of this function, my current workaround to use it is a separate field in my order table in which I enter the date manually, a rather awkward but workable solution so far.

The invoice is printed as a designed report and either a calculated field in the report or a calculated field in the initial entry table would be equally acceptable. open office 4.1.2, Windows 7, Oracle report builder extension installed and used for the invoicing report drawing data from a query based on two tables, one for customer data and one for order details.

As should be obvious, I'm a total novice at both open office and a user rather than a professional designer of databases so answers need to be explicit and in simple terms.

[MTP]

Will the due date ever be manually adjusted to be something other than 30 days after the bill date? If not, it would be more efficient to write a view to show the due dates instead of jumping through hoops to save them in the table.

Either way will be easier if you have a split database with an upgrade to a newer version of HSQLDB. (Base itself is just a "front-end" program that handles queries, forms and reports; the tables are handled by a different "back-end" program that by default is an old version of HSQLDB zipped inside the Base file; in addition to the zipped HSQLDB version being old, the zipping itself can cause crashing/data loss issues with larger or more complicated databases so "splitting" the two programs apart is recommended.) There's a wizard with instructions for doing this in the thread [Tutorial] Splitting an "embedded HSQL database"

The newer version of HSQLDB has a function called DATEADD that lets you easily add, say, 30 days to a date. The Base parser doesn't recognize DATEADD so to use it in a view (in the Tables view of Base, click on "Create View...") you will need to go to SQL mode (click on the toolbar icon that is a rectangle with a triangle) and then turn off the parser by Edit→Run SQL Directly. The code for your view will be

Code: Select all

SELECT *, DATEADD('dd',30,"BillDate") AS "DueDate" FROM "YourTableName"

Make sure to change "BillDate" to your actual column name and "YourTableName" to your actual table name. Once the view is created, you treat it just like a table.

If you do need to make the "DueDate" editable to sometimes not be 30 days, then you would need to write a macro or a trigger to record the value in the original table.

[FJK]

Yeah, not problem with that, already done and everything works and prints fine (I'm using it now for my billing, having switched from an no longer supported version of Alpha Five).

What I'm wanting to do is have the due date calculated automatically without having to do make a manual entry after calculating it every time I enter a new invoice record as I'm doing now (which involves calculating the Julian date from a separate program and adding 30 to it then calculating it back to the Gregorian and entering it into the record each time I add one).

It's the OO calculation formula, command or code that would accomplish this in base that I don't understand and don't even know where to actually look for it.

A few other minor problems I haven't solved yet and may just ignore but this is the irritating one.

[Villeroy]

Code: Select all

ALTER TABLE "INVOICES" ALTER COLUMN "INVOICE_DATE" SET DEFAULT CURRENT_DATE

sets the invoice date automatically when you append a new invoice record.

[FJK]

I'm not being clear or not understanding the answers (I'm a complete novice at open office base and a user very much more than a programmer as well).

I'm trying to set a new date automatically that is 30 days from the billing date to indicate the date a bill is due with a "Net 30" terms condition without making a separate manual entry for each invoice record.

i.e. - Bill Date "date billed" | Due Date "date billed + 30 days" - printed out on the invoice report so I don't have to go through the process of separately figuring out what date 30 days from the date I am sending the invoice on is and entering it somewhere every time I enter a new record and print it. Maybe this can't be done in base. I got used to doing it that way in prior databases I've used I so sort of thought it would be be doable in OO base.

Maybe someone could show how to write a simple single table database example that contains only two fields, "entry date" and "entry date + 30 days", in which the field "entry date" is the only entry required or allowed and the second field is calculated automatically so I can see the code and structure of how it is done. Alternatively a single field table where the second field is not calculated in the database table itself when the initial entry is made but in the report -or in a form letter- as it is created and printed as a printed invoice to send the customer (which is how I've done it in prior databases I've used).

(FWIW, I don't really want a default date entered for the bill date field since I don't always send the invoices out on the date I enter them and want the option of entering the date I am intending to send the bill out on without an automatic error built in if I miss entering the date when I create the record. No date entered is easier to catch than a wrong date since it won't print using the bill date parameter.)

[Villeroy]

If the due date is always entry date + 30 then it would be a mistake to store the due date.

[FJK]

Agreed about storing that data, but that is my temporary fix to use the system.

Generated in the Query or Report -or in a form letter- as it is printed is what I would desire and what I've done with other database programs, but I have no idea how to do either on an automatic basis with open office.

Not knowing how to do this, I put the field in the n the orders data table so it could be put into use for billing till I find out how and modify the report or query to do it for me. I have no idea how to do this yet.

[Villeroy]

Create a query

Code: Select all

SELECT "Table Name".*, DATEADD(...) AS "Due Date" FROM "Table Name"

selects all fields plus a calculated field "Due Date" from "Table". Create your report from that query.

[FJK]

I'll give that a try this evening, not at the right computer now.

[FJK]

Create a query

Code: Select all

SELECT "Table Name".*, DATEADD(...) AS "Due Date" FROM "Table Name"

selects all fields plus a calculated field "Due Date" from "Table". Create your report from that query.

I can't get DATEADD (...) to work no matter how I try using it, it just shows green and produces a syntax error. Cutting and pasting your example with appropriate table and field names does not work, I have no idea what I'm doing wrong since my SQL experience years out of date and no longer seems to apply.


Here's a sample of a query I would use as a basis for an invoice report using an already entered due date field:

SELECT "NUMBER" AS "NUMBER", "NAME" AS "NAME", "ADDRESS" AS "ADDRESS", "CITY" AS "CITY", "STATE" AS "STATE", "ZIP" AS "ZIP", "TERMS" AS "TERMS", "PONUM" AS "PONUM", "BILLDATE" AS "BILLDATE", "JOB NAME" AS "JOB NAME", "JOB ADDRESS" AS "JOB ADDRESS", "WDATE1" AS "WDATE1", "DESC1" AS "DESC1", "PRICE1" AS "PRICE1", "WDATE 2" AS "WDATE 2", "DESC 2" AS "DESC 2", "PRICE2" AS "PRICE2", "WDATE 3" AS "WDATE 3", "DESC3" AS "DESC3", "PRICE3" AS "PRICE3", "TTL" AS "TTL", "Due Date" FROM " View-Invoice combined tables" WHERE "NUMBER" = '97455'

The query is based on a relation of two tables with some drawn from one table and the rest from another in a combined table view. This represents about the extent of my knowledge at this point. It is functional but awkward without calculated fields.

[Sliderule]

I can't get DATEADD (...) to work no matter how I try using it, it just shows green and produces a syntax error. Cutting and pasting your example with appropriate table and field names does not work, I have no idea what I'm doing wrong since my SQL experience years out of date and no longer seems to apply.

Please understand, the SQL function DATEADD will only work, assuming your are using HSQL as your database back-end, IF, you are using a version of HSQL 2.X or greater, BUT not with the embedded database delivered with OpenOffice / LibreOffice ( that is HSQL Version 1.8.0.10 ).

So, Questions:

1. What is the HSQL Version you are using?
   
   To determine if your are still using an Embedded HSQL Database ( so DATEADD is NOT supported ), perform these steps:
   
   You can write a query by following ALL of the steps below, to confirm the explicit HSQL version:
   1. Start / Open your Base file ( *.odb )
   2. On the left, click on the Queries Icon
   3. Under Tasks, click on: Create Query in SQL View...
   4. Copy and paste the following code:
      

      Code: Select all

      call "org.hsqldb.Library.getDatabaseFullProductVersion"()

   5. IMPORTANT STEP: From the Menu, click on: Edit -> Run SQL Command directly
   6. Either:
      1. From the Toolbar, click on the Run Query (F5) Icon
      2. Press F5
      3. From the Menu: Edit -> Run Query
   IF, you have upgraded to a SPLIT database, AND, you are running a HSQL 2.X Version, you can determine the HSQL Version with the following Query:

   Code: Select all

   SELECT 
      DATABASE_VERSION( ) AS "HSQL Version" 
   FROM "INFORMATION_SCHEMA"."TABLES" 
   WHERE "TABLE_NAME" = 'TABLES'

2. If your are not using HSQL, what database are you using?

If you are using HSQL Version 2.3.3, for example, the following should work:

Code: Select all

SELECT 
   "NUMBER" AS "NUMBER",
   "NAME" AS "NAME",
   "ADDRESS" AS "ADDRESS",
   "CITY" AS "CITY",
   "STATE" AS "STATE",
   "ZIP" AS "ZIP",
   "TERMS" AS "TERMS",
   "PONUM" AS "PONUM",
   "BILLDATE" AS "BILLDATE",
   DATEADD('day', 30, "BILLDATE") as "DUE_DATE_30",
   TO_CHAR(DATEADD('day', 30, "BILLDATE"),'YYYY-MM-DD') as "DUE_DATE_30_YYYYMMDD",
   "JOB NAME" AS "JOB NAME",
   "JOB ADDRESS" AS "JOB ADDRESS",
   "WDATE1" AS "WDATE1",
   "DESC1" AS "DESC1",
   "PRICE1" AS "PRICE1",
   "WDATE 2" AS "WDATE 2",
   "DESC 2" AS "DESC 2",
   "PRICE2" AS "PRICE2",
   "WDATE 3" AS "WDATE 3",
   "DESC3" AS "DESC3",
   "PRICE3" AS "PRICE3",
   "TTL" AS "TTL",
   "Due Date"
FROM "View-Invoice combined tables"
WHERE "NUMBER" = '97455'

Explanation: I added the DATEADD function, to calculate the value from the column ( "BILLDATE . . . and, did it a second time to show the date as a text string as well ). Furthermore, compared to what you wrote, I removed a space from the beginning of the VIEW name as you entered it above, since, a table or view cannot start with a space.
Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[FJK]

call "org.hsqldb.Library.getDatabaseFullProductVersion"()

Returns 1.8.0.10

The version that installs with the download.

I have no idea how to change it to a different version and it seems it might be beyond the scope of this current help request to pursue it since is seems more complicated than my knowledge of the subject easily allows.

[Sliderule]

So, since you elected, as of this point in time, to NOT upgrade to HSQL Version 2.X ( as the date I am writing this, the newest version of HSQL 2.3.3 ) . . . the DATEADD function is NOT present.

In the post given by user MTP above, it referenced the link . . .

There's a wizard with instructions for doing this in the thread [Tutorial] Splitting an "embedded HSQL database"
viewtopic.php?f=83&t=65980

please read user DACM's comment in his Number 1 about moving to HSQL Version 2.x .

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[FJK]

I'll pursue that option, but I imagine it will take a while to understand what I'm doing and how to do it so I'm going to "solve" this inquiry for now.

I'll open a new thread if I have trouble after I follow your advice here, thanks for the patience and the help you've given me.