OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Printing Multiple Values in One Field
Goto page 1, 2, 3, 4  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
rmalino2
General User
General User


Joined: 03 Aug 2010
Posts: 7

PostPosted: Tue Aug 03, 2010 9:19 am    Post subject: Printing Multiple Values in One Field Reply with quote

I've read a number of threads on using listboxes and subforms to create a many-many relationship that allows multiple values to be assigned to one field for one item. My question is similar. The database I am building will serve two purposes. First, it will serve as an in-house database tracking a digitization project. One of the tables records metadata for each item. That table has a few fields for which more than one value can be assigned.

The problem arises with the second use of the table. The metadata table will need to be exported as a csv file at a later date to be imported into another organizations database. Fields with multiple values must have those values separated by a semicolon.

So, my question is if I use many-many relationships to assign multiple subjects to various items (for example, a photograph have multiple subjects from a specific list), can I create a csv file from the metadata table that will follow the format required by the other institution?

Any guidance would be appreciated.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Tue Aug 03, 2010 10:49 am    Post subject: Reply with quote

rmalino2:

Welcome to OpenOffice Base.

I am not all sure I understand what you are asking . . . so . . . let me ask you a few questions that might help. Likewise, anyone else on the forum, if you can understand and help rmalino2, please jump in and respond.

Could you please tell us:
  1. What database engine are you using with OpenOffice Base? If you are not sure, after opening your OpenOffice Base file, on the status bar at the bottom, it will tell you the database engine. I am asking this question, because an answer might depend on the engine you are using.

  2. Can you give me / us a rough idea of your computer and database expertise? I am asking because I do not want to respond over your head ( understand ), nor, under it.

  3. Are you really asking about an ability to perform something like an aggregate function, something like:
    Code:
    Select
       "Item",
       GROUP_CONCAT("MyField") as "Concat Output Field"
    From "MyTable"
    Group By "Item"

  4. Would it be possible, for you to give an example -- a small example, of what a table would be like, and, what you want the 'output' to look like?
Sliderule
Back to top
View user's profile Send private message
rmalino2
General User
General User


Joined: 03 Aug 2010
Posts: 7

PostPosted: Tue Aug 03, 2010 11:40 am    Post subject: Reply with quote

1. What database engine are you using with OpenOffice Base? HSQL Database Engine

2. Can you give me / us a rough idea of your computer and database expertise? My understanding of relational databases is mostly theoretical. I've made a few simple databases in the past (2-3 tables, queries and reports using the wizard). My programming experience is pretty outdated (Visual Basic and C++ around 2003).

3. Are you really asking about an ability to perform something like an aggregate function, something like:
Code:
Code:
Select
   "Item",
   GROUP_CONCAT("MyField") as "Concat Output Field"
From "MyTable"
Group By "Item"

No, I don't think so

4. Would it be possible, for you to give an example -- a small example, of what a table would be like, and, what you want the 'output' to look like?

Below is a screenshot of the form for the metadata table I created as well as the edit view for the underlying table. At this point, the "IDA Subject" list box is populated by a table called IDASubjects using this command "SELECT "Subject", "Subject" FROM "IDASubjects""


http://dl.dropbox.com/u/9593068/Metadata%20Form.bmp
http://dl.dropbox.com/u/9593068/Metadata%20Table.bmp

At some point, I need to export the metadata table as a csv file to import into another database. That field for the subject headings mentioned above would need to have each value separated by a semicolon.
For example:
"Local History; Labor; African Americans"
where Local History, Labor, and African Americans are choices from IDASubjects.

I understand that the proper way to express this in a relational database is to use a many to many relationship with three tables as expressed in this thread http://user.services.openoffice.org/en/forum/viewtopic.php?f=61&t=25562. What I don't understand is how I will generate the csv I need if I do it that way.

I guess my main problem is that a database seems most appropriate for our in-house use, but I really need a spreadsheet to share with this other group.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Tue Aug 03, 2010 1:23 pm    Post subject: Reply with quote

rmalino2:

I think I am better understanding what you want to do, but, I am still not 100% certain.

Just as an FYI . . . one thing you wrote that confused me in your first post ( note that is now past tense ) . . . but . . . now I think I better understand . . . you have a table by the name of "Metadata". The word "metadata" does have a special meaning in the database world . . . and . . . what you are talking about is just to 'export' some of that data . . . whether to a spreadsheet, or, a flat file. OK. Smile

Now, just a couple of other questions.

  1. The data you want to 'export' . . . do you have a Query . . . that contains the 'selected' data? That is, that will only produce the desired fields ( columns ) and rows for this data? I am asking this, because, the only remaining 'issue' is how to get that data, out of the database, to your preferred format.

  2. Can you tell me, is this something that will be performed numerous times, put another way, on a 'regular' basis? I am asking because, if it is only on rare occasion, might approach ( database pun intended ) it one way, but, it is done frequently, might want to have a macro ( I have a few that are generic enough you might be able to use as a start ) to perform the task(s).

  3. You mentioned semicolons as a desired output, if I understand correctly. My question is, does any your RAW DATA that you want to 'export' contain commas? I am asking because, if the program ( database engine HSQL ) exports the fields separated by commas, and, the macro just 'replaces' the commas with semicolons . . . will this meet your needs, withOUT corrupting any data, if the RAW DATA ( text fields ) also consists of commas. I hope that is clear.

  4. I applaud you for defining your table, "Metadata" using VARCHAR_IGNORECASE rather than VARCHAR. Yes, it does make 'searching / retrieving' text data much easier ( case insensitive for others reading this Smile ).

Sliderule
Back to top
View user's profile Send private message
rmalino2
General User
General User


Joined: 03 Aug 2010
Posts: 7

PostPosted: Wed Aug 04, 2010 7:46 am    Post subject: Reply with quote

I made a mini-db to play around with, and have the many to many relationship working, and created a query that pulls all of the data I want. As you can see from the linked image of the query, each item appears a number of times, depending on how many subjects it has.
http://dl.dropbox.com/u/9593068/Query.png

I would like each item to appear only one time, with all of the subjects for that item listed in one field, separated by a semicolon, as seen in the Formatted Spreadsheet image.
http://dl.dropbox.com/u/9593068/Formatted%20Spreadsheet.png

To answer your second question, it would be ideal if this exporting function could be easily repeated.

To clarify based on your third question, I actually need a tab delineated csv file as my final output with semicolons separating multiple values that exist within one filed. For example:
"itemID" <tab> "Name" <tab> "Subject1; Subject2; Subject3" There are no semicolons in my data, although there will be commas in text fields that are not visible in this example.

Thank you for all of your help. This forum has been very useful.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Wed Aug 04, 2010 9:24 am    Post subject: Reply with quote

rmalino2:

OK, I am better understanding what you want. At least, I think I do.

Sorry to be asking so many questions, but, I think we are close.

It would be helpful for me, because, I want to create a similar SAMPLE table ( or two or three as needed ) . . . confirm how you are doing it. Your first screen image:



OK, this is what I did. I took what you showed in the image above and created one Table with the data. I create ONE Query . . . showing SQL as in image below . . . and it created the results as you can see:



The SQL I used to create the above results ( my table name is "TEST03" ) was:

Code:
Select
   "ItemID",
   "Name",
   GROUP_CONCAT("IDASubject" Separator '; ') as "Group Concat"

From TEST03

Group By "ItemID", "Name"


Explanation:
  1. I used an Aggregate Function GROUP_CONCAT . . . like I asked about in my first reply above. Smile

  2. The SQL Query says, for each UNIQUE combination of "ItemID" and "Name", return a third column of the concatenation from the column "IDASubject". Each value of "IDASubject" is 'separated' by a semicolon and a space ( your choice ) making up the returned results of this 'computed' column. Of course, any column ( Alias ) name can be assigned to it.
But, ( sorry, always a but Smile ) . . . the GROUP_CONCAT Aggregate Function with HSQL database engine is not currently ( as the date I am writing this ) available within OpenOffice ( as of version 3.2.1 ) and my understanding is, it will not be implemented in OpenOffice for a while ( probably Version 3.4 whenever that is released ).

However, you COULD, if you want . . . migrate to HSQL 2.0 . What I mean is, if you implement the HSQL newest JAVA JAR file rather than the version installed currently used by OpenOffice ( HSQL version 1.8 ). If you are interested in this 'solution' . . . let me know. I hope this is not too confusing, ask questions if you want.

Just a thought.

Let me know what you think.

Sliderule
Back to top
View user's profile Send private message
rmalino2
General User
General User


Joined: 03 Aug 2010
Posts: 7

PostPosted: Wed Aug 04, 2010 1:32 pm    Post subject: Reply with quote

I am interested in making that change if it will solve my problem. How would I do so?

I really appreciate the help you've given me with this project.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Wed Aug 04, 2010 1:48 pm    Post subject: Reply with quote

OK, one more question for you . . . you see . . . I am really the Question person, not the Answer person. Smile

Just to be clear . . . I would like to know . . . I will assume, the way you have this set up now . . . PLEASSE CORRECT ME IF I AM MISTAKEN you are running this in a one person ( not a multple person ) environment. Right?

Put another way . . . will only one person will ever 'access' ( database pun intended ) the data 'concurrently'?

The reason I am asking is, if you need this in a multi-user environment . . . one set of instructions . . .l if you need this only in a single user ( one computer ) environment . . . different set of instructions. One other point . . . I find HSQL a very 'solid' database environment . . . BUT . . . not necessarily when using it as the 'embedded zipped' OpenOffice default way. So, either make some modifications and use HSQL and store the data outside of the *.odb file ( much more reliable ) using HSQL 2.0 with GROUP_CONCAT functions . . . OR . . . MySQL as a database server . . . also with a GROUP_CONCAT capability.

Another user on this board . . . dacm has written some good posts on the topic . . . and . . . perhaps he could join in here ( Please ) . . . or . . . I could find a few of his posts.

Oh, one more question ( could not be here and ask only one question Smile ) . . . while maybe not important, but, just in case . . . what Operating System are you using? For example, WIndows, Linux, Apple ?

Concolustion: Let me / us know if this is a single user only implementation . . . OR . . . a desired multiple user environemtn . . . so . . . know where to suggest you proceed.

Sliderule
Back to top
View user's profile Send private message
rmalino2
General User
General User


Joined: 03 Aug 2010
Posts: 7

PostPosted: Wed Aug 04, 2010 2:02 pm    Post subject: Reply with quote

Multiple people will be using the database, but there's no need for them to do so at the same time. Currently, my database files are stored in a Dropbox folder and accessed from whichever computer I happen to be working at. If Dropbox would be an issue, we can have a dedicated computer.

So to answer your question, we're looking for a solution for a single user at a time. If it will work with Dropbox, great. If not, I'll stop using Dropbox and just use one machine.

Also, I'll take a look at some of dacm's posts tonight or tomorrow.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Wed Aug 04, 2010 2:12 pm    Post subject: Reply with quote

OK, you might want to start at the link below . . . with great suggestions from both dacm and fredt ( key developer at HSQL ) .

http://www.oooforum.org/forum/viewtopic.phtml?t=97522

If, you will ONLY ever need this for one user at a time . . . then perhaps, using HSQL 2.0 and as fredt said in link above:

fredt wrote:
Thanks, I've seen it, but what I'm suggesting here is not the server-mode and does not need starting a server. It is running HSQLDB in-process but avoids the OOo container/compression services to improve speed and reliability. You can use properties such as zero write delay to make it crash-proof.


I passed a private message ( PM ) to dacm asking him to help here, if / when he has a chance.

Please, do not hesitate to ask questions, between us, I am sure what you want can be done.

Sliderule
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Thu Aug 05, 2010 2:26 am    Post subject: Reply with quote

Good, it looks like sliderule has designed a query that will generate the table you need for export purposes, but you'll need another database engine in order to implement the Group_Concat solution.

So the immediate question is which Base-comaptible database to use: HSQLDB 2.0, H2, or MySQL? These offer Group_Concat. There's also PostgreSQL and Firebird which don't have a built-in Group_Concat function but they're are extremely powerful so they can emulate Group_Concat using other functions -- but this involves considerably more effort in this case. The first two (HSQLDB 2.0 and H2) are virtually drop-in replacements, while MySQL with the InnoDB engine is a good choice if you can meet the licensing requirements. H2 loses some advantage in this case because you don't need the multi-user automation or linked tables. So that leaves HSQLDB 2.0 (which can also be configured for multi-user). All I can say is, you'll get the most support in this particular forum with HSQLDB. And remember that Base is tuned for HSQLDB in general (avoiding Schema issues, etc), so it's always a good option when it fits the bill. Bottom Line: it is increasingly difficult to outgrow or actually require features not found in HSQLDB since the 2.0 version release, so I'll proceed with the steps to upgrade from HSQLDB 1.8.x to 2.0.

Edit: to rearrange the process order:
First, migrate your database to the "file: mode" configuration by following this post.
NOTE: The resulting database folder offers seamless read/write access for a single-user, plus read-only access for additional LAN users. This may even work in-place inside a Dropbox folder online...?
As a bonus, the same "file: mode" folder/files can be used with HSQLDB running in "server-mode" which enables simultaneous, multi-user, read/write access -- requiring some additional setup plus HSQLDB "server" startup/shutdown management.


Second, upgrade from HSQLDB 1.8.x to HSQLDB 2.0:
1. Read the upgrade information from the HSQLDB documentaion and make a backup(s) of your latest .odb file before proceeding.
2. Open your database (.odb file) in Base and execute SHUTDOWN COMPACT from the Base: Tools > SQL... input box.
3. Make sure OpenOffice/Base is using Java 1.5 or newer by selecting Tools > Options > OpenOffice.org > Java ; Test for Java version installed.
4. File Exit Base, and make a backup(s) of your .odb database file.
5. Download and install hsqldb2_OOo.jar from this post. Or follow steps 5a-5c manually below instead:
    5a. Download the 'Latest Version' of HSQLDB 2.0 (select Java 1.6 version 6, or Java 1.5 version 5 as applicable)
    5b. Extract the hsqldb.jar file from the zip archive:
    hsqldb-2.0.0.zip\hsqldb-2.0.0\hsqldb\lib\hsqldb.jar
    5c. Create the folder 'hsqldb2' and place hsqldb.jar in the folder as shown:
    Program Files\openoffice.org 3\Basis\program\classes\hsqldb2\hsqldb.jar
6. Open your database (.odb file) in Base and execute SHUTDOWN COMPACT from the Base: Tools > SQL... input box.
7. File Exit Base, and make a backup(s) of the new .odb file.

That should upgrade your database to HSQLDB 2.0 ! You can now restart Base to test the Group_Concat query.
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'


Last edited by dacm on Thu Oct 28, 2010 12:18 am; edited 14 times in total
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Thu Aug 05, 2010 6:43 am    Post subject: Reply with quote

A quick comment suggestion here:

dacm above said:

dacm wrote:
7. Drop-in replace the file by the same name in your Program Files:openoffice.org\Basis\program\classes\hsqldb.jar


For safety sake . . . if it were me . . . I would recommend not DELETING the 'original' hsqldb.jar file . . . but rather . . . renaming it . . . for example . . . hsqldbORIGINAL.xxxjar .

The reason . . . just for 'safety' . . . in event you ever wanted to get back to the original HSQL version ( 1.8 ) that is currently downloaded with OpenOffice . . . you will be able to get it back. This is just for 'safety'.

I hope that is clear . . . well at least as clear as mud. Smile

Sliderule
Back to top
View user's profile Send private message
rmalino2
General User
General User


Joined: 03 Aug 2010
Posts: 7

PostPosted: Mon Aug 09, 2010 11:33 am    Post subject: Reply with quote

The system I'll be importing to is called ContentDM [url](http://www.contentdm.org/)[/url]. It's designed for libraries, etc. to get digital collections online. We'll be adding our collection to a statewide site, so our formatting should coincide with theirs, and the only information I have about the configuration I've already shared.

Dropbox automatically syncs edits among computers. I have the option to upload/download if I don't want to wait for the scheduled sync. I usually just wait.

My project has two forms, one of which has two subforms, and one query at this time.

It sounds like HSQLDB is a good choice. Reading your instructions, I have a couple of questions. In steps 2 and 8, should I have my project open in Base, or open create a new database?

Also, after I rename the original jar file, should I leave it in the classes folder or move it somewhere for safekeeping?

I followed the steps dacm gave, using a copy of project file for steps 2 and 8. I left both the hsqldbORIGINAL.jar and the hsqldb.jar files in the classes folder. When I got to step 8, I got the error messge:
Quote:
java.lang.NullPointerException in statement [SHUTDOWN COMPACT]
.

I removed the new HSQLDB and restarted things, and my database is functioning as it was before. Should I have done something differently, or is there another setting I should check somewhere?[/quote]
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Mon Aug 09, 2010 12:58 pm    Post subject: Reply with quote

rmalino2 wrote:
In steps 2 and 8, should I have my project open in Base, or open create a new database?

I'm sorry that was unclear, but yes that's your project open in Base.

Quote:
Also, after I rename the original jar file, should I leave it in the classes folder or move it somewhere for safekeeping?

Either way...both jar files are easily replaced and won't conflict when renamed.

Quote:
...I left both the hsqldbORIGINAL.jar and the hsqldb.jar files in the classes folder. When I got to step 8, I got the error messge:
Quote:
java.lang.NullPointerException in statement [SHUTDOWN COMPACT]

As I understand it, this step actually accomplishes the conversion process. I don't know why it would fail, but monitoring the HSQLDB forum has revealed some upgrade issues with over-sized data in VARCHAR columns; in other words, the data string was longer than the column property specifed and therefore allowed but HSQLDB 1.8 didn't care, while HSQLDB 2.0 checks such things. So as long as you're following the upgrade steps properly, this error would be a question for the HSQLDB forum. Did you get a successful SHUTDOWN COMPACT in step 2?

Quote:
I removed the new HSQLDB and restarted things, and my database is functioning as it was before. Should I have done something differently, or is there another setting I should check somewhere?

The upgrade is a one-way process with no backwards compatibility. But it doesn't sound like your database was upgraded due to the NullPointerException.
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
rmalino2
General User
General User


Joined: 03 Aug 2010
Posts: 7

PostPosted: Mon Aug 09, 2010 1:40 pm    Post subject: Reply with quote

I checked all of my datatypes and sizes, and there were some that were cutting it close. I updated the sizes of my varchar fields to definitely exceed my needs. I saved and backed up and followed the steps again, but I got the same message.

The SHUTDOWN COMPACT command worked in step 2.

When I try to reopen my file after getting the "java.lang.NullPointerException in statement [SHUTDOWN COMPACT]" error, I get the message "The connection to the data source "FILE NAME" could not be established. The connection could not be established. The database was created by a newer version of OpenOffice.org."

When I delete the new HSQL file and use the backed up version of my project to get back into things.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Goto page 1, 2, 3, 4  Next
Page 1 of 4

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group