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

Database Newbie Needs Some (Okay, LOTS of) Help

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
Steven_Shelton
OOo Enthusiast
OOo Enthusiast


Joined: 26 Sep 2004
Posts: 104
Location: Michigan

PostPosted: Sat Dec 17, 2005 9:55 pm    Post subject: Database Newbie Needs Some (Okay, LOTS of) Help Reply with quote

Here's what I'm trying to do in OOo 2.0.1 RC5:

I am an attorney in solo practice, and I'm trying to put together a case management database that would track the following for each case:

- The internal case number
- Client details (name, address, etc.)
- Conflict of interest checks
- Case Type (Tort, Criminal Defense, etc.)
- Case Disposition (including outcome and date)
- Litigation details (including case names, docket numbers, and judges)
- Opposing party details (name, address, etc.)
- Witness details (name, address, expert status, etc.)
- Opposing counsel/co-counsel details (name, address, firm, etc.)
- Billing (including line-item charges and billable hours)
- Case notes

Several of these items require subforms because there may be several people on whom I am tracking information (for instance, there may be multiple clients, multiple witnesses, multiple opposing parties, etc. on a single case).

I put together a database with several tables (the main case details table and a separate table for each subform). For each subform, I created a field in the main table to correspond to the subform's table. (For instance, the main table contains a "BillingEventID" field, and the "BillingDetails" table contains as its primary key a "BillingEventsID" field.) I then created a relationship between the two.

Now, here are my problems:

1. None of the subform tables will read any data in my form. For instance, I cannot enter any information at all in the client details subform; I just get a blank table control.

2. When I try to add a new record into the main (CaseDetails) table, I get this error:
Quote:

Integrity constraint violation - no parent SYS_FK_52 table: BillingDetails in statement [INSERT INTO "CaseDetails" ( "BillingEventID", "CaseName", "CaseNature", "CaseNotes", "ClientID", "CoCounselNumber", "ConflictDescription", "DispositionDate", "DispositionReason", "DispositionType", "LineItemID", "OppCounselNumber", "OppPartyNumber" ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )]


I have NO CLUE what is causing this. It seems to be a table-level problem (and not a problem with my form) because it does this when I'm trying to add data directly into the table as well as when I I try to add it in the form.

3. I can't change the size of the page; I honestly don't mind if I have to scroll down the screen a bit to enter billing data or whatever. The priority is just to have everything on a single form. I changed the page size in the FORMAT > PAGE window but it had no effect.

4. I have two dropdown menus that are supposed to read their options from tables. One is for "Disposition Type" and the other is for "Case Type." (The idea being that I can keep the data consistent for easy analysis and sorting.) However, all either will display is the default value; no options appear at all in the dropdown, and in the CONTROL palette, the ability to add list items is grayed out. (This would be, I suppose, because the values are supposed to be read from the table.) The weird thing is, it worked one time, and then stopped doing it.

5. I'd like to have some calculations within my form; for instance, when I enter a service of process at, say, $20 each and three people had to be served, I'd like to be able to enter "$20" as the price-per-unit, "3" as the number of units, and have the application automatically calculate the total value as "$60" (as I would do in a spreadsheet). I'm not sure how to do this in a Base form, however. I'm sure there has to be a way.

I realize that this may be a bit of a complicated database for a total newb (or nearly total newb) to attempt, but anything simpler than this I could handle with a simple spreadsheet. And, really, part of the point of this is to learn how to do it, so what better way than to jump in up to my neck?

So, any help/advice on any (or all) of these problems would be greatly appreciated, or if someone would like to take a look at what I've done and see if there's something obvious that I'm doing wrong, I'd be glad to send a copy of the file. My tremendous thanks in advance.
Back to top
View user's profile Send private message Visit poster's website
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Sat Dec 17, 2005 10:56 pm    Post subject: Re: Database Newbie Needs Some (Okay, LOTS of) Help Reply with quote

Hi Steven,

The easy one first.
Steven_Shelton wrote:
3. I can't change the size of the page; I honestly don't mind if I have to scroll down the screen a bit to enter billing data or whatever. The priority is just to have everything on a single form. I changed the page size in the FORMAT > PAGE window but it had no effect.


When you open a form for editing in Base the default view is 'Web Layout'. Change this to 'Print Layout'. View>Print Layout. With this done you can see the canges to your page size settings. To make them 'stick', move any control down the page as far as you need it. Now when the view is switched back Tools>Web Layout, the scroll bar will be available to scroll down far enough to display the bottom most control.
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Sat Dec 17, 2005 11:07 pm    Post subject: Re: Database Newbie Needs Some (Okay, LOTS of) Help Reply with quote

Steven_Shelton wrote:
2. When I try to add a new record into the main (CaseDetails) table, I get this error:
Quote:

Integrity constraint violation - no parent SYS_FK_52 table: BillingDetails in statement [INSERT INTO "CaseDetails" ( "BillingEventID", "CaseName", "CaseNature", "CaseNotes", "ClientID", "CoCounselNumber", "ConflictDescription", "DispositionDate", "DispositionReason", "DispositionType", "LineItemID", "OppCounselNumber", "OppPartyNumber" ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )]


This is usually indicative of having the relationships backwards, or having them setup in such a way that they cancel each other out. Open your relationships window and look at the relationship between CaseDetails and BillingDetails. I am sure what you wanted to represent was a relationship of many billing events to a single Case record. The notation for this in the relationship window would be a line between the two tables where there is a 1 next to the CaseDetails table and a n next to the BillingDetails table. If it is reversed then the relationship has been created incorrectly. What you would have setup in that case would tell the database engine that before you can enter a Case record you must enter a BillingDetail record and trying to enter a CaseDetail record would generate precisely this error message.

Drew
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Sat Dec 17, 2005 11:23 pm    Post subject: Re: Database Newbie Needs Some (Okay, LOTS of) Help Reply with quote

Steven_Shelton wrote:

5. I'd like to have some calculations within my form; for instance, when I enter a service of process at, say, $20 each and three people had to be served, I'd like to be able to enter "$20" as the price-per-unit, "3" as the number of units, and have the application automatically calculate the total value as "$60" (as I would do in a spreadsheet). I'm not sure how to do this in a Base form, however. I'm sure there has to be a way.


Well, if you where to just display a calculated value you could generate it in the sql select statement (or Base query).

The use you describe however will need to a basic macro that references the data and writes the calculated value to a column.

Here is an example of some code that does something quite close to what you descibe:

http://www.oooforum.org/forum/viewtopic.phtml?p=113730&highlight=#113730
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Sat Dec 17, 2005 11:32 pm    Post subject: Re: Database Newbie Needs Some (Okay, LOTS of) Help Reply with quote

Steven_Shelton wrote:

1. None of the subform tables will read any data in my form. For instance, I cannot enter any information at all in the client details subform; I just get a blank table control.


Well, can't really hazard a guess as to why.

Quote:

4. I have two dropdown menus that are supposed to read their options from tables. One is for "Disposition Type" and the other is for "Case Type." (The idea being that I can keep the data consistent for easy analysis and sorting.) However, all either will display is the default value; no options appear at all in the dropdown, and in the CONTROL palette, the ability to add list items is grayed out. (This would be, I suppose, because the values are supposed to be read from the table.) The weird thing is, it worked one time, and then stopped doing it.


I have had listboxes behave like this, always on forms where I am actively changing things (The have never stopped working just by running the form)..anyway, sometimes it is just faster to delete the control that is no longer firing (retrieving list items) and adding back a new one.

The fact is however that neither is likely to function as you want until the relationships are setup correctly.

Drew
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Steven_Shelton
OOo Enthusiast
OOo Enthusiast


Joined: 26 Sep 2004
Posts: 104
Location: Michigan

PostPosted: Sun Dec 18, 2005 7:54 am    Post subject: Re: Database Newbie Needs Some (Okay, LOTS of) Help Reply with quote

DrewJensen wrote:

This is usually indicative of having the relationships backwards, or having them setup in such a way that they cancel each other out. Open your relationships window and look at the relationship between CaseDetails and BillingDetails. I am sure what you wanted to represent was a relationship of many billing events to a single Case record. The notation for this in the relationship window would be a line between the two tables where there is a 1 next to the CaseDetails table and a n next to the BillingDetails table. If it is reversed then the relationship has been created incorrectly. What you would have setup in that case would tell the database engine that before you can enter a Case record you must enter a BillingDetail record and trying to enter a CaseDetail record would generate precisely this error message.


This sounds like exactly the problem. But now I've got a new problem: no matter what I do, the "n" ends up next to the CaseDetails table and the 1 ends up next to BillingDetails. I've tried dragging from CaseDetails to BillingDetails, and dragging from BillingDetails to CaseDetails. Both times, the "n" ends up by the CaseDetails table.

Might I have encountered a bug here?
Back to top
View user's profile Send private message Visit poster's website
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Sun Dec 18, 2005 8:14 am    Post subject: Reply with quote

No it's not a bug.

It is the way you have designed your tables.

In the casedetails table you have a primary key column, CaseNumber. Then you added a column BillingEventID.

In the BillingDetail table you have a primary key field of, EventID. Then you added a column CaseNumber.

Now if you try to create a relationship between the columns CaseDetails.BillingEventID and BillingDetails.EventID you are indeed asking far a 1 to n relations with one BillingDetail record to multiple CaseDetail records. After all BillingDetails.EventID is the primary key and by definition identifies exactly one BillingDetail record. The column CaseDetails.BillingEventID is neither a key, nor unique and therefore does not identify a single record, but rather is an attribute for any number of CaseDetail records.

The relationship you are meaning to create is between CaseDetails.CaseNumber and BillingDetails.CaseNumber. This will be generated as the 1 CaseDetails record to the Many BillingDetails records.

The column CaseDetails.BillingDetailsID is not needed at all. BillingDetails.EventID is still needed to identify individual (unique) BillingDetail recods, and BillingDetails.CaseNumber is an attribute that relates any number of these to a single CaseDetail record.

Drew
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Steven_Shelton
OOo Enthusiast
OOo Enthusiast


Joined: 26 Sep 2004
Posts: 104
Location: Michigan

PostPosted: Sun Dec 18, 2005 9:07 am    Post subject: A-HA! Reply with quote

DrewJensen wrote:
The relationship you are meaning to create is between CaseDetails.CaseNumber and BillingDetails.CaseNumber. This will be generated as the 1 CaseDetails record to the Many BillingDetails records.

The column CaseDetails.BillingDetailsID is not needed at all. BillingDetails.EventID is still needed to identify individual (unique) BillingDetail recods, and BillingDetails.CaseNumber is an attribute that relates any number of these to a single CaseDetail record.


Bravo! I think I've got it!

So basically every subtable should have a field called "CaseNumber", and "CaseNumber" from the CaseDetails will feed into that field. Brilliant. I'll take care of that (since that's the biggest bugaboo) before I tackle the other issues.

You da man, Drew! Very Happy
Back to top
View user's profile Send private message Visit poster's website
Steven_Shelton
OOo Enthusiast
OOo Enthusiast


Joined: 26 Sep 2004
Posts: 104
Location: Michigan

PostPosted: Sun Dec 18, 2005 10:20 am    Post subject: Little glitch...probably simple... Reply with quote

That did the trick!

Now I have a small, related problem: although the "ClientDetails" table is updating, the information is not showing in my form. As soon as I move to the next CaseDetails record, the ClientDetails subform shows nothing but empty fields.

Opening the ClientDetails table shows that all of the client data is there, but case numbers are not being saved in the table (which is, I think, the crux of the issue).

One more thing unrelated to this, but I'm sure a simple fix: I have a "Notes" field that is just a text box. Unfortunately, it insists of centering everything vertically and I can't find any option to align the contents to the top of the text box. There has to be an easy way to do this.

Thanks for all the help! Sorry to have to ask on every little detail, but I'm getting closer! Once this is done, I think I'll have a pretty good handle on how this works.
Back to top
View user's profile Send private message Visit poster's website
Steven_Shelton
OOo Enthusiast
OOo Enthusiast


Joined: 26 Sep 2004
Posts: 104
Location: Michigan

PostPosted: Mon Dec 19, 2005 8:42 pm    Post subject: Similar problem now Reply with quote

Solved the two problems above, I think, but now I get a similar message on my opposing party (OP) subform, even though the relationships are set up correctly:

Quote:

SQL Status: 23000
Error code: -177

Integrity constraint violation - no parent SYS_FK_199 table: CaseDetails in statement [INSERT INTO "OpposingPartyDetails" ( "CaseID", "OPCity", "OPFirstName", "OPLastName", "OPMiddleInitial1", "OPName", "OPNotes", "OPPostalCode", "OPState", "OPStateofResidence", "OPStreet1", "OPStreet2", "OPSuffix1", "OPTitle", "OPType" ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )]


Clues? Anyone?
Back to top
View user's profile Send private message Visit poster's website
jazzcat
General User
General User


Joined: 29 Sep 2005
Posts: 19

PostPosted: Tue Jan 10, 2006 9:34 am    Post subject: Re: Database Newbie Needs Some (Okay, LOTS of) Help Reply with quote

Steven_Shelton wrote:

I am an attorney in solo practice, and I'm trying to put together a case management database that would track the following for each case:


Steven,

I know this is offtopic, but check out LibreLex. LibreLex is a project to combine best of breed open source components to build a complete law office.

So far we have an excellent case management app almost complete; I'm currently working on integrating this with an accounting program (also open source) so that all of the billing is handled properly.

Check it out at http://www.librelex.net

Thanks,
-josh
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
Page 1 of 1

 
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