Joined: Jul 05, 2005 Posts: 685 Location: North West England
Posted: Mon Oct 12, 2009 1:17 pm Post subject:
I've now got the Classification data in a table with the number as the Primary Key.
I've identified the problem with data entry. For some reason the Form Navigation toolbar wasn't showing. Once it was visible everything was obvious!
The form is untidy now, but functional. I need to think more about the classification number issue. I think what I'd like to be able to do is scroll through the numbers and see the text, and then when I select a number to have the text automatically placed in the relevant field.
Does that mean a sub-form?
Anyway enough for tonight (local time). I've got a dying battery and I'm trying to do an online grocery order.
Thanks for all your help
James _________________ MacBook Pro
13-inch, Mid 2012
Processor 2.5 GHz Intel Core i5
Memory 4 GB 1600 MHz DDR3
Graphics Intel HD Graphics 4000 512 MB
OS X 10.9.3 (13D65)
Joined: Jul 05, 2005 Posts: 685 Location: North West England
Posted: Tue Oct 13, 2009 6:37 am Post subject:
Lorinda wrote:
It might be easiest for me to figure out what's going on if I could see a copy of the odb file. If you don't want to post it here (or can't due to size issues), send me a pm and we can figure out a way to get it to me.
Thank you for your offer of help. PM (about to be) sent. The file is about 2.2 MB. If you agree, I think it would be worth continuing our conversation on the forum for the sake of other users.
James _________________ MacBook Pro
13-inch, Mid 2012
Processor 2.5 GHz Intel Core i5
Memory 4 GB 1600 MHz DDR3
Graphics Intel HD Graphics 4000 512 MB
OS X 10.9.3 (13D65)
Joined: Jun 20, 2006 Posts: 2051 Location: Midwest, USA
Posted: Tue Oct 13, 2009 2:32 pm Post subject:
I realized that I didn't respond to your post from Monday. I don't think a subform will do what you want there, as subforms are always connected by a common field.
I'll hopefully have a better sense of your options when I see the file. I agree that continuing our discussion here in the forums is a good idea.
Joined: Jul 05, 2005 Posts: 685 Location: North West England
Posted: Wed Oct 14, 2009 3:11 am Post subject:
Well, I've now sent you the file. By the time I was ready to send it, it had expanded to 2.8MB!
I was preparing a data-reduced version of it to upload for Patrick on the Base crash thread on the Early Access forum and even after I'd removed most of the data it was still the same size. This seemed odd to say the least so I Googled "openoffice base compact file" which got me this link and introduced me to the wonderful 'SHUTDOWN COMPACT' command.
So I've been cleaning up all round and the file I have sent you is a mere 368KB.
AFAICT 'SHUTDOWN COMPACT' isn't documented on the Wiki or in Help - do you think it would be worth adding to the Wiki somewhere? It has just freed up 4-5MB of disk space for me. _________________ MacBook Pro
13-inch, Mid 2012
Processor 2.5 GHz Intel Core i5
Memory 4 GB 1600 MHz DDR3
Graphics Intel HD Graphics 4000 512 MB
OS X 10.9.3 (13D65)
Joined: Jun 20, 2006 Posts: 2051 Location: Midwest, USA
Posted: Wed Oct 14, 2009 6:36 am Post subject:
In terms of numbers and descriptions, the best way to go (at least according to standard database protocol) is to relate the CategoryID field in the Saward Classification 2 table and the Class# field in the Books Catalogue table.
Then using a query you can connect the Detail description as well.
I was planning on giving it a try with the file you provided, but none of the records in the Books Catalogue table have Class#s, probably because most of the Theology section is missing.
Joined: Jul 05, 2005 Posts: 685 Location: North West England
Posted: Wed Oct 14, 2009 7:22 am Post subject:
Lorinda wrote:
In terms of numbers and descriptions, the best way to go (at least according to standard database protocol) is to relate the CategoryID field in the Saward Classification 2 table and the Class# field in the Books Catalogue table.
Then using a query you can connect the Detail description as well.
I wondered if there might be some way of doing that.
Lorinda wrote:
I was planning on giving it a try with the file you provided, but none of the records in the Books Catalogue table have Class#s, probably because most of the Theology section is missing.
Lorinda
Strange... I've just opened the file I sent you. When I first open the Books catalogue table it shows (bottom left) "Record 1 of 40", but if I click the "go to end of table" arrow to the right, it jumps to Record 2454, and the information at the bottom left now reads "Record 2,454 of 2454". Records 1197 to 2454 all have Class# and Class Descr data. _________________ MacBook Pro
13-inch, Mid 2012
Processor 2.5 GHz Intel Core i5
Memory 4 GB 1600 MHz DDR3
Graphics Intel HD Graphics 4000 512 MB
OS X 10.9.3 (13D65)
Joined: Jul 05, 2005 Posts: 685 Location: North West England
Posted: Wed Oct 14, 2009 8:09 am Post subject:
Lorinda wrote:
Sound like a good addition. Are you able to add it? (Can't remember if you are a wiki editor or not)
I am a wiki editor. My reservations about doing it are (a) there is another SQL command CHECKPOINT DEFRAG which also eliminates waste space and reduces file size, and I'm not sure of the relative benefits of one vs. the other, and I have only a misty understanding of what they do even having located them on this page and looked them up; and (b) I'm not sure where the information would best be put in the wiki.
Maybe a link to this page of the HSQLDB User Guide would help with SHUTDOWN COMPACT. From this page I think the effect of the CHECKPOINT command is to wwrite all buffered changes to the database, and the addition of DEFRAG, causes it to recover any unused space.
If I knew where to put them I could probably cobble something together which would say 'I don't know the full implications of this command, but it also compacts your database' - though I'd probably phrase it so it didn't look quite as stupid as that sounds! _________________ MacBook Pro
13-inch, Mid 2012
Processor 2.5 GHz Intel Core i5
Memory 4 GB 1600 MHz DDR3
Graphics Intel HD Graphics 4000 512 MB
OS X 10.9.3 (13D65)
Joined: Jun 20, 2006 Posts: 2051 Location: Midwest, USA
Posted: Wed Oct 14, 2009 8:55 am Post subject:
Ah, I forgot about the "only show part of the data at first" thing with Base, it is all there. What threw me was a single THEOLOGY entry at the beginning of the file, with no Class#
In terms of the wiki, I would suggest creating a new page, named something like "Reducing the Size of the Database File" and putting a link to it in the Other Base Tips in the Wiki section of the NeoBase article. One of us could also ask for a clarification on the two commands in the OOo Community forums. If you aren't already registered there, let me know and I can ask.
I'll have a go at making the connections I'm thinking about in the file, but I may not get to it until much later today. I've got some work and family responsibilities to get to first.
On the form, what do you want to see in the drop box:
-the Class#
-the Detail Description
-the Class# and Detail Description
-the Class #, Main Heading, Sub Heading, and Detail
-some other combination of this info?
The first two are easiest, the remainder require building a query, but all are possible.
Lorinda
Joined: Jul 05, 2005 Posts: 685 Location: North West England
Posted: Wed Oct 14, 2009 9:14 am Post subject:
Lorinda wrote:
In terms of the wiki, I would suggest creating a new page, named something like "Reducing the Size of the Database File" and putting a link to it in the Other Base Tips in the Wiki section of the NeoBase article. One of us could also ask for a clarification on the two commands in the OOo Community forums. If you aren't already registered there, let me know and I can ask.
OK that seems like a good idea. I'm not registered on the OOo Community forums, so if you could ask there that would help. Once we've got the information, I'd be glad to set up the page.
Lorinda wrote:
I'll have a go at making the connections I'm thinking about in the file, but I may not get to it until much later today. I've got some work and family responsibilities to get to first.
That's fine. Me too. I'm supposed to be completing tax forms at the moment...
Lorinda wrote:
On the form, what do you want to see in the drop box:
-the Class#
-the Detail Description
-the Class# and Detail Description
-the Class #, Main Heading, Sub Heading, and Detail
-some other combination of this info?
The first two are easiest, the remainder require building a query, but all are possible.
Lorinda
I think what I'd like to see is the Class #, Sub Heading, and Detail. I want to insert the whole into the table. The existing Class Descr field is (as you probably realise) simply a concatenation of Main Heading, Sub Heading, and Detail. If I were sticking with the classification system, I'd probably choose to create new fields and insert each item of data into a separate field, and normalise the existing data at a later stage. Since I'm planning to move away from it anyway, it's probably simplest to insert new data in the same form as the existing data. _________________ MacBook Pro
13-inch, Mid 2012
Processor 2.5 GHz Intel Core i5
Memory 4 GB 1600 MHz DDR3
Graphics Intel HD Graphics 4000 512 MB
OS X 10.9.3 (13D65)
Joined: Jun 20, 2006 Posts: 2051 Location: Midwest, USA
Posted: Sun Oct 18, 2009 12:13 pm Post subject:
Sorry for the delay. It's been busier than I thought, and this week will be even worse. The instructions below are 'quick and dirty.' I'm afraid I didn't have time to cover every single step and button. Hopefully I gave you enough info to work with.
Create the relationship between the CategoryID field in the Saward Classification table and the Class# field in the Books Catalogue table. See the instructions here, although you don't need to create the unique index or constraint, Neo should make them automatically.
Note that my copy was missing an entry in the Classification table for 79.020. I got an error message when establishing relationships until I fixed that.
I ended up only including the ID# and Detail in my sample, but with some more work you could include more info.
In the Tables section of the main database window, click on Create View...
Add the Saward Classification table to the "workspace" near the top (you should be asked about tables to add)
Double click on CategoryID to move it to the first column in the lower half of the View window
In the second column, click in the Field row and type the following:
Code:
CONCAT("CategoryID","Detail")
Hit return; in the Alias row of this column, give this "field" a more short descriptive name (like DetailConcat)
Click on the "Run Query" icon at the top (the datasources with the green arrow pointing down) and verify the results.
Save this view and give it a name
Unfortunately, the ID and Detail will be run together with no space. As far as I know, Base (unlike Calc!) will not accept nested CONCAT commands, so to Concat spaces and additional fields means create a series of Views, each building on the other.
Open the form in edit mode, ungroup the Class Number grouping (the label and field are grouped). Delete the field for Class Number.
From the Form Controls toolbar, choose the list box icon, then draw a list box on the form.
The List Box wizard should open.
For the table to draw the data from, choose the View you just made
For the display field, choose the Concat "field" you created in the view.
For the linking field, choose CategoryID and Class# in the appropriate columns.
Save the field and try it out.
You spoke of wanting to save several different fields from the Saward Classification table into the Books Catalogue. Proper relational database protocols insists that data should not be duplicated across tables, with the one exception of the linking (or key) field. So you should really only save the CategoryID/Class# in the Books Catalogue table. For reports and other uses, the other fields (Main heading, sub-heading, etc.) can be combined with the data from the Books Catalogue table in a Query or a View.
Joined: Jul 05, 2005 Posts: 685 Location: North West England
Posted: Sun Oct 18, 2009 9:42 pm Post subject:
Thank you for this, Lorinda, I'm hugely grateful - it looks like a very helpful way forward. It may be a little while before I respond properly. My next two or three weeks look very busy too. Thank you again. _________________ MacBook Pro
13-inch, Mid 2012
Processor 2.5 GHz Intel Core i5
Memory 4 GB 1600 MHz DDR3
Graphics Intel HD Graphics 4000 512 MB
OS X 10.9.3 (13D65)
Joined: Jul 05, 2005 Posts: 685 Location: North West England
Posted: Tue Oct 20, 2009 9:52 pm Post subject:
Well, I think I'm almost there. Thank you again for your help. Base seems a bit reluctant and I've had one or two hangs and force quits - unfortunately it is not easy to get predictable repeatability on that. Your instructions worked fine, thank you. I have the following comments:
Lorinda wrote:
Unfortunately, the ID and Detail will be run together with no space. As far as I know, Base (unlike Calc!) will not accept nested CONCAT commands, so to Concat spaces and additional fields means create a series of Views, each building on the other.
Base will accept nested CONCAT commands, provided each command has only two elements:
Code:
CONCAT(CONCAT("CategoryID",' '),"Detail")
and in NeoOffice 3.0.1, Base will accept this format:
Code:
"CategoryID"||' '||"Detail"
To display the data I wanted I created a Table View combining the Books Catalogue and the Saward Classification tables, and then produced a report from that.
It's still a bit rough and ready, but it is working.
Thank you again _________________ MacBook Pro
13-inch, Mid 2012
Processor 2.5 GHz Intel Core i5
Memory 4 GB 1600 MHz DDR3
Graphics Intel HD Graphics 4000 512 MB
OS X 10.9.3 (13D65)
Joined: Jun 20, 2006 Posts: 2051 Location: Midwest, USA
Posted: Wed Oct 21, 2009 12:17 am Post subject:
I'm glad you discovered that nested CONCAT commands work...and the pipe option. I couldn't get nested CONCATs to work for me, but I may have missed something, as I was in a hurry. Last night I remembered that I was going to do some checking on that in the OOo Community Forums and learned that they ought to work, but I didn't get around to adding a post here.
Joined: Jun 20, 2006 Posts: 2051 Location: Midwest, USA
Posted: Wed Oct 21, 2009 7:58 am Post subject:
I realized I hadn't tracked down the CHECKPOINT DEFRAG AND SHUTDOWN COMPACT commands. I found this glossary that defined the terms. It's more readable than I remember the hsqldb documentation being in the past. Is that helpful to you?
If you still have questions, I can ask in the OOo Community forums.
All times are GMT - 7 Hours Goto page Previous1, 2, 3Next
Page 2 of 3
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 You cannot attach files in this forum You cannot download files in this forum
