[Solved] Setting a non-constant default value

Discuss the database features
Post Reply
appliednano
Posts: 10
Joined: Mon Apr 29, 2013 5:52 pm

[Solved] Setting a non-constant default value

Post by appliednano »

Hello,

I'm new to OOBase and SQL in general, so please bear with me!

I've got three tables: Items, Parts, and Vendors. Each entry in the Parts table corresponds with a unique part, and a vendor ID ("VID", linked to the Vendors table) for a current vendor from whom we should make future purchases for that part. Each entry in the Items table corresponds with each individual item, so if we have five "doohickey"s in stock, and the "doohickey" entry in the Parts table has a part ID ("PID") of 1, then we have five separate entries in Items, each with part ID 1.

Entries in the Items database ALSO have a vendor ID field, just like entries in the Parts database. This vendor ID corresponds with the vendor from whom we purchased that particular item, not necessarily where we should make future purchases.

Here's what I want to do: set the default value of the Items table's vendor ID (and other purchasing information) to the part's "future purchase" vendor ID from the Parts table. That is, if I don't enter where we bought this new item, just assume we bought it from our current vendor. I might need something like this:

Code: Select all

alter table "Items" alter column "VID" set default (something like: "Parts"."VID" where "Items"."PID" = "Parts"."PID")
This obviously doesn't work, and I think SET DEFAULT only works with constant values. How should I go about doing this? I'm also open to setting up the default value via a form instead of using an ALTER TABLE statement.

Thank you!
Last edited by appliednano on Fri May 17, 2013 12:44 am, edited 1 time in total.
OpenOffice 3.4.1 on Windows 7
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Setting a non-constant default value

Post by DACM »

Unless I'm missing something here, it appears at first glance that you should eliminate the VID from the Items table, since it's maintained in the related Parts table.

And I'm not sure how you're managing the inventory/stock through the Items table, but you might be interested in: Villeroy's In-Out database version 2
...
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
appliednano
Posts: 10
Joined: Mon Apr 29, 2013 5:52 pm

Re: Setting a non-constant default value

Post by appliednano »

Like I said, VID serves different purposes in the Items table vs. the Parts table.

In the Parts table, VID points to the current vendor of that part. Meaning, when it comes time to restock, that's the vendor to contact.

In the Items table, VID points to the vendor we actually used for that particular item. The Items table is a history of every single item, and sometimes the vendor for a given item will change.

Parts: Future purchases, unique entries for each type of item.
Items: Past purchases, unique entries for each individual item we've ever purchased.

Usually, these values will be the same, since vendors don't change often. Therefore, when creating a new entry in Items, I'd like the default VID to be the corresponding VID for that item's part number in the Parts table.

I need to do this with other purchasing information as well, such as price. The price field in the Parts table indicates the current price of that part, whereas the price field in the Items table indicates the amount we paid for that item. By default, I'd like the Items entries to use the price from the Parts table. I apologize if my usage of the words "Items" and "Parts" are confusing; in fact, if anyone has a suggestion for more clear names for these tables, please let me know.
OpenOffice 3.4.1 on Windows 7
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Setting a non-constant default value

Post by Sliderule »

Can you please help me, help you?

Let me quickly explain a few concepts . . ..
  1. OpenOffice / LibreOffice Base is NOT a database. That is, it is FRONT-END, to a BACK-END database.
  2. Therefore, I / we need to know, which database BACK-END you are using. For example, is it, HSQL ( either, embedded, or, external ), MySQL, H2, Microsoft Access, PostGRE, SQLite, Firebird, other ?
  3. Please tell me / us, by reporting what the STATUS BAR says, at the BOTTOM, after you start your Base ( database FRONT-END ) file . . . *.odb.
  4. In the event you are using HSQL, either Version 2.2.8, or, Version 2.3.0 . . . I think you might be able to accomplish your task, using, a TRIGGER . However, HSQL Version 1.8.0.10 which, as of the date I am writing this, comes as the default for OpenOffice / LibreOffice, does NOT support the use of a TRIGGER . However, if you 'upgrade' to the external database, ( for example HSQL 2.2.8 or HSQL 2.3.0 . . . or . . . some other database BACK-END ) . . . these capabilities might be available.
  5. Please, tell me / us EACH table you have defined, AND, please use DOUBLE QUOTES around the table name . . . for example: "Items" . And, please do the same for each column in your table, AND, the type of data it is . . . I mean, for example, INTEGER, VARCHAR, DECIMAL, etc . . . for example: "VID" Integer . I am asking this to be sure others can correctly create the necessary statement . . . assuming you are using a database BACK-END that supports it.
Sliderule
appliednano
Posts: 10
Joined: Mon Apr 29, 2013 5:52 pm

Re: Setting a non-constant default value

Post by appliednano »

Sorry about that. I'm using HSQL 1.8.0.10. I know that OOBase is a front-end. However, like I said, if my problem can't easily be solved with a simple HSQL statement, I'd like to know if it can be done using Forms in OOBase.

Thank you!
OpenOffice 3.4.1 on Windows 7
appliednano
Posts: 10
Joined: Mon Apr 29, 2013 5:52 pm

Re: Setting a non-constant default value

Post by appliednano »

In response to 5), I will repeat the relevant information here. All columns I list here are integers. Obviously I have other columns, but they are not relevant to my question.

Table 1: "Items"
"IID" (Item ID, key)
"PID" (Part ID)
"VID" (Vendor ID)

Table 2: "Parts"
"PID" (Part ID, key)
"VID" (Vendor ID)

Table 3: "Vendors"
"VID" (Vendor ID, key)


*Edited to mention that the "Vendors" table is also not relevant to the question. I just want to set the default VID column in "Items" to the corresponding VID value in "Parts".
Last edited by appliednano on Tue Apr 30, 2013 12:10 am, edited 1 time in total.
OpenOffice 3.4.1 on Windows 7
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Setting a non-constant default value

Post by Sliderule »

Since you are using HSQL 1.8.0.10, and, not HSQL 2.2.8 ( or HSQL 2.3.0 ) . . . use of a TRIGGER is not appropriate.

Therefore, I will leave it for others to help you. Continued success.

Sliderule
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Setting a non-constant default value

Post by DACM »

So you simply need to pass the current PID and VID from the PARTS table to the ITEMS table when creating new item records?

That can be done through SubForm links: Or with a Form and Macro: Perhaps see also: Or if you want to solicit help from Sliderule using Triggers, or otherwise adopt a reliable database configuration with Base, then try this Base template as described here which employs HSQLDB 2.2.8.
...
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
appliednano
Posts: 10
Joined: Mon Apr 29, 2013 5:52 pm

Re: Setting a non-constant default value

Post by appliednano »

I just need to pass VID from Parts to Items. Here's how I want this to work from a data entry standpoint:

Create a new Items entry.
Choose a PID (Parts ID, linking this new entry to an entry in the Parts table) for this Items entry.
Have the form automatically set the default VID for this new Items entry to the VID of the linked Parts table entry.

I'm a little familiar with SubForms, and I poked around a bit with the Survey/Invoice examples, but I don't see how to implement them to set default values. Like I said, I'm new to this, and I'm not connecting the dots here.

I attempted to upgrade my HSQL version to 2.2.8 so that I can try Triggers, but I've run into a confusing issue. I've converted my embedded database to a split database using these instructions, which seemed to be successful. Then I followed these instructions and used hsqldb2_OOo.exe to upgrade to HSQL 2.2.8. But when I then use this query to check my HSQL version:

Code: Select all

call "org.hsqldb.Library.getDatabaseFullProductVersion"()
I get this error:

The data content could not be loaded.

user lacks privelege or object not found
java.lang.ClassNotFoundException: org.hsqldb.Library
org.hsqldb.Library

This query formerly returned "1.8.0.10" before upgrading. I also get this error when running the HSQL version query on the Base template you linked here, DACM. Did I miss a step somewhere?
OpenOffice 3.4.1 on Windows 7
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Setting a non-constant default value

Post by Sliderule »

No, if you are using HSQL 2.2.8 rather than HSQL 1.8.0.1 . . . to confirm / return the VERSION, use the following Query:

Code: Select all

Select 
   DATABASE_VERSION() as HSQL_VERSION, 
   CURRENT_TIMESTAMP as "Current Time" 
From INFORMATION_SCHEMA.SYSTEM_TABLES 
Where TABLE_NAME = 'TABLES'
The reason for using the specific code above ( from and where clauses ), is to only return ONE row ( one instance ) of the database version. :)

NOTE: the function . . . DATABASE_VERSION() is a function that is available with HSQL 2.2.8, BUT, NOT with HSQL 1.8.0.10 . :bravo:

Sliderule
Last edited by Sliderule on Thu May 02, 2013 9:24 pm, edited 1 time in total.
User avatar
Greengiant224
Posts: 283
Joined: Wed Jun 09, 2010 3:50 pm
Location: All Over The World

Re: Setting a non-constant default value

Post by Greengiant224 »

apliednano:
I attempted to upgrade my HSQL version to 2.2.8 so that I can try Triggers, but I've run into a confusing issue. I've converted my embedded database to a split database using these instructions, which seemed to be successful. Then I followed these instructions and used hsqldb2_OOo.exe to upgrade to HSQL 2.2.8. But when I then use this query to check my HSQL version:

Code: Select all Expand view
call "org.hsqldb.Library.getDatabaseFullProductVersion"()



I get this error:

The data content could not be loaded.

user lacks privelege or object not found
java.lang.ClassNotFoundException: org.hsqldb.Library
org.hsqldb.Library

This query formerly returned "1.8.0.10" before upgrading
Using

Code: Select all

call "org.hsqldb.Library.getDatabaseFullProductVersion"()
[/b] is HSQLDB v 1.8.* specific.

You need to use:

Code: Select all

SELECT DATABASE_VERSION( ) AS "HSQL_VERSION" FROM "INFORMATION_SCHEMA"."SYSTEM_TABLES" WHERE "TABLE_NAME" = 'ROUTINES'
[/b]
this was changed for the HSQLDB version 2.*.* jars. ( As advised by Sliderule several months ago)

Otherwise you look good to go......

(Sliderule beat me to it.... again....)

Greengiant224

Win 7, Portable AOO 4.1.14, LibreOffice 6.2, 7.4.5 & 7.6.1 (Java 1.7.81 & 1.8.0_281) utilising HSQL 1.8.10 & 2.4.*, MySQL, PostgreSQL, SQLite
+ Blood, Sweat and Tears (Application, Determination and Perseverance)
appliednano
Posts: 10
Joined: Mon Apr 29, 2013 5:52 pm

Re: Setting a non-constant default value

Post by appliednano »

Ah, thanks for that. :) I'm successfully running 2.2.8 now, so the use of TRIGGER is now an option.
OpenOffice 3.4.1 on Windows 7
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Setting a non-constant default value

Post by DACM »

appliednano wrote:I'm successfully running 2.2.8 now...
Great job! Those steps will all be automated in the next few hours...literally...if all goes well. And along those lines, here's a quick tutorial when your ready to make your existing 'split' database-folder portable. Follow the steps as an existing 'split' database user.
[Wizard] Create a new 'split' HSQL database
...
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Setting a non-constant default value

Post by DACM »

appliednano wrote:...so the use of TRIGGER is now an option.
Yes, a Trigger is the most universal solution.
DACM wrote:So you simply need to pass the current PID and VID from the PARTS table to the ITEMS table when creating new item records?

That can be done through SubForm links:
appliednano wrote:I'm a little familiar with SubForms, and I poked around a bit with the Survey/Invoice examples, but I don't see how to implement them to set default values. Like I said, I'm new to this, and I'm not connecting the dots here.
Okay, here's some dot-connectors (additional tips) on passing/cloning multiple fields between tables, using SubForm links, when creating new records...
  • Pass multiple fields as subform links
    Pass multiple fields as subform links
  • There are a few gotcha's and considerations involved, so here's a demo with a so-called 'tutorial' form. This is a code-free approach. But if you don't like the necessary push-button sequence, then I'm guessing it would take about 10 lines of macro-code to eliminate the extraneous push buttons.
...
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
appliednano
Posts: 10
Joined: Mon Apr 29, 2013 5:52 pm

Re: Setting a non-constant default value

Post by appliednano »

Thanks for the example. Unfortunately, I was unable to open the forms or do anything with it after installing hsqldb2_OOo.exe, since it uses an embedded database. I'll try checking it out on another computer later today.
OpenOffice 3.4.1 on Windows 7
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Setting a non-constant default value

Post by DACM »

appliednano wrote:Unfortunately, I was unable to open the forms or do anything with it after installing hsqldb2_OOo.exe...
Adding the macro to your .odb and a copy of hsqldb.jar to your database-folder as outlined in the "wizard" link above (or in my signature below) also eliminates that issue.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
appliednano
Posts: 10
Joined: Mon Apr 29, 2013 5:52 pm

Re: Setting a non-constant default value

Post by appliednano »

After playing with the example on another computer, it seems this is close to what I wanted to do. Thank you for your help, DACM. But is the most elegant way of doing it? Do I really need an extra "filter" table and a "bogus" boolean field in my Items table? Is there a simpler solution using TRIGGER?
OpenOffice 3.4.1 on Windows 7
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Setting a non-constant default value

Post by Sliderule »

appliednano:

You said / asked:
appliednano wrote:But is the most elegant way of doing it? Do I really need an extra "filter" table and a "bogus" boolean field in my Items table? Is there a simpler solution using TRIGGER?
Perhaps . . . but . . . since I do NOT have a copy of your database to 'confirm' this . . . I cannot say for certain. I do ask, that you look at the recent forum post below, where, I helped user create a few Triggers.

http://forum.openoffice.org/en/forum/vi ... 61&t=61639

Now, in your case, the following Trigger ( or something like it ) might work. I used the statement you gave above ( the ALTER statement in your first post ), to define the statement. If you are using OpenOffice Base, or, LibreOffice Base to 'issue' the command below . . . you would it from the Menu: Tools -> SQL...

Code: Select all

CREATE TRIGGER ITEMS_VID
BEFORE INSERT ON "Items" 
REFERENCING NEW ROW AS NEWROW 

FOR EACH ROW 
    BEGIN ATOMIC 
    
       SET NEWROW."VID" = (Select "Parts"."VID" From "Parts" Where "Parts"."PID" = NEWROW."PID"); 
    
END
Explanation:
  1. You may ( probably ) will want to include a when clause . . . so . . . the SET will only happen WHEN a particular condition ( NEWROW."VID" ) is equal to 0, or, NULL ( you have to decide ). :)
  2. Do you want / need another Trigger, to handle any UPDATE statement . . . in addition to an INSERT statement?
You can 'review' use of Trigger with HSQL at the following links:
  1. Create Trigger: http://www.hsqldb.org/doc/2.0/guide/dat ... r_creation
  2. Chapter 9: Triggers: http://www.hsqldb.org/doc/2.0/guide/triggers-chapt.html
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Field cloning with Triggers

Post by DACM »

appliednano wrote:But is [this code-free approach the] most elegant way of doing it? Do I really need an extra "filter" table and a "bogus" boolean field in my Items table? Is there a simpler solution using a TRIGGER?
The 'elegance' and 'simplicity' of a coded approach is generally predicated on the goodwill of someone else -- who's willing to write the specialized code on another's behalf. We're glad to accommodate these requests in these forums, particularly with SQL, but also with macros, and now with triggers and/or other SQL-procedures as enabled by HSQLDB 2.x. After all, writing code or providing and explaining code-free examples is just as difficult for all involved.

It's a balance because many users find procedural code (beyond SQL alone) to be a frustration to write, maintain, and extend over time compared to a macro-free solution. So we tend to offer code-free examples as available. Other users, like me, come from a programming background and find macros just as easy and often more elegant. In any case, I continue to believe that until you've considered the potential of SubForms it's difficult to know when and where code would be appropriate. But as this thread points-out, we can over-rely on macros and code-free solutions alike. In this case, perhaps with the advent of easy access to triggers for all Base users, the back-end trigger solution is 10x easiser and more elegant than any other solution.

I appreciate your willingness to consider the code-free approach on its merits, while recognizing the clunky (in-elegant) requirements both for the developer (filter table and bogus field) and for the end-user (bogus push buttons and sequencing). Very well articulated. :super:

And of course, I appreciate Sliderule's willingness to write the trigger solution as it's helping to 'trigger' the light-bulb for all of us. :idea:
Last edited by DACM on Mon Jan 13, 2014 8:31 am, edited 2 times in total.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Setting a non-constant default value

Post by Sliderule »

And, I would like to add one other point, to DACM's excellent explanation above, regarding an 'advantage' to using a database back-end solution ( in this case, HSQL trigger ).

If, a user is adding, updating, deleting records to the database, whether in an OpenOffice / LibreOffice Base environment, OR, outside, for example:
  1. HSQL SqlTool
  2. HSQL DBManagerSwing
  3. SQL WorkBench J
  4. other
the trigger(s) will all be used. Put another way, the database is protected / consistent. However, in the case of an OpenOffice / LibreOffice macro, the code is ONLY used, for a form within the application.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
appliednano
Posts: 10
Joined: Mon Apr 29, 2013 5:52 pm

Re: Setting a non-constant default value

Post by appliednano »

Thanks everyone. Sliderule, I will look into the resources you provided for Triggers; that option might be my best bet. DACM, I apologize, I wasn't trying to take advantage of your help. I was surprised that this was the simplest Form-based solution, and I unintentionally came off as annoyed and ungrateful!

I'll post here if I need any more guidance. Once I find the best solution for me, I'll post it here for others' reference and list the topic as [Solved].
OpenOffice 3.4.1 on Windows 7
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Setting a non-constant default value

Post by DACM »

No, your answer did not come across as annoyed and ungrateful. It came across as frustrated with a difficult, odious, code-free hack of Base. I was delighted that you rejected the code-free approach on its merits because we too often assume that users prefer such hacks. And it gave me the opportunity to properly acknowledge the additional options (triggers and procedural SQL) we now enjoy with HSQLDB 2.x.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
appliednano
Posts: 10
Joined: Mon Apr 29, 2013 5:52 pm

Re: Setting a non-constant default value

Post by appliednano »

Success! The TRIGGER solution works wonderfully. Here's the command I used, courtesy of Sliderule (I went ahead and added the WHEN clause):

Code: Select all

CREATE TRIGGER ITEMS_VID
BEFORE INSERT ON "Items" 
REFERENCING NEW ROW AS NEWROW 

FOR EACH ROW WHEN (NEWROW."VID" IS NULL)
BEGIN ATOMIC 
    SET NEWROW."VID" = (Select "Parts"."VID" From "Parts" Where "Parts"."PID" = NEWROW."PID"); 
END
Now, just as you'd expect, when I enter in the PID of a new Items entry, and leave Items.VID blank, Parts.VID is used to populate the field. I used similar commands for Price, Part Number etc.

You guys are great. Thanks so much for the help!
OpenOffice 3.4.1 on Windows 7
User avatar
ambparker
Banned
Posts: 13
Joined: Mon Oct 28, 2013 7:52 am
Location: USA

Re: [Solved] Setting a non-constant default value

Post by ambparker »

I attempted to upgrade my HSQL version to 2.2.8 so that I can try Triggers, but I've run into a confusing issue.
OOo 3.3.0 and Apache OOo 3.4.1/4.0.0, on Ms Windows 7 and Mac OS-X.
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved] Setting a non-constant default value

Post by Sliderule »

ambparker:

You said / asked:
ambparker wrote: I attempted to upgrade my HSQL version to 2.2.8 so that I can try Triggers, but I've run into a confusing issue.
I have no idea why you upgraded to HSQL Version 2.2.8 , rather than the current ( as of the date I am writing this ) version, HSQL 2.3.1 .

And, I am pleased that you have informed us you have run into a confusing issue.

I too, have run into an individual making a "confusing" post, that provides no information on what is expected and what you did. Therefore, since I do not want to contribute to your confusion, I will not respond further to you.

Sliderule
Post Reply