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

Help with many to many relationship

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
acidblue
General User
General User


Joined: 24 Apr 2012
Posts: 7

PostPosted: Tue Apr 24, 2012 1:02 pm    Post subject: Help with many to many relationship Reply with quote

I have a mysql database with 3 tables, Client table, Petition Table and a Ref table.
The Ref Table is used for the relationship links between the client and petition tables.

I'm having trouble designing a form that will assign a petition to a client.
The Client and Petition tables have forms for entering data in to them and work fine.

I want to be able to create a form that has a combo list box linked to the petitions table and another combo list box linked to the Clients table, so I can assign a petition to a client.
Also a text/list area that lists all the current petitions assigned to a given client.

I am having a real hard time trying to figure this out.
I have a field in the Clients table 'clients_petitions', I was planning on using this to hold the Petitions value,This works only in a one at time situation.

I can only enter one client with one petition at a time, if client 'Joe' has 3 petitions, I have to add 'Joe' 3 times to the table thus having 3 instances of 'Joe' for every petition that I add.

I want to only have 1 instance of 'Joe' and display the 3 petitions he has in the form/report.
This where I thought a drop down combo box would come in handy, I could just click the combo box and choose another petition to add, but this overwrites the existing record instead of adding it.

Could anyone show me the light.
Screen shots:
http://flic.kr/p/bQmeET
http://flic.kr/p/bQmhpK




[/url]
Back to top
View user's profile Send private message
Billyray
OOo Enthusiast
OOo Enthusiast


Joined: 06 Mar 2007
Posts: 144
Location: Lake Erie's Shore in Ohio

PostPosted: Wed Apr 25, 2012 11:04 am    Post subject: Reply with quote

Your setup is that one client can have any number of petitions right?

Can any one petition belong to more than one client? Or can one petition belong to just one client?
_________________
Billyray
using:
Linux distro: Ubuntu 10.04 LTS, Lucid Lynx, OOo Base 3.3, connected to MySql database using Java jdbc (note: ONLY sun-java-6-1.6.0_22 jre works right), and converted from MS Access 2003.
Back to top
View user's profile Send private message
acidblue
General User
General User


Joined: 24 Apr 2012
Posts: 7

PostPosted: Wed Apr 25, 2012 1:10 pm    Post subject: Reply with quote

Billyray wrote:
Your setup is that one client can have any number of petitions right?

Can any one petition belong to more than one client? Or can one petition belong to just one client?



Clients can have many petitions and vice versa, petitions can have many clients
Back to top
View user's profile Send private message
Billyray
OOo Enthusiast
OOo Enthusiast


Joined: 06 Mar 2007
Posts: 144
Location: Lake Erie's Shore in Ohio

PostPosted: Thu Apr 26, 2012 5:28 am    Post subject: Reply with quote

What if you thought about your design like this:

Your Client table is like an "order"
Your Ref table is like the "order details"
Your Petition table is like your "inventory"

IOW, the client table has a 1 to many relationship wtih Ref table which can therefore be a subform of the mainform Client. You add records with new "detail lines". One of the fields in the details is the "inventory" item, or Petitions. This can be a drop down box (in the subform) which has the Petitions table as its source data. (It also has a one to many relationship with the detail record, but is irrelevant to your form's function, other than the linkage) Then you assign the petition in the detail record (really the ref table) That way you should not be adding new client records when you add new Petitions. Yet you are adding the petition to the correct customer.

Now when you review a client, you will see all of his petitions listed as line items in the subform based on the ref table.

You can also make another form with the Petition table as the main form, and the ref table as the subform, to show all of the Clients (in line items) associated with that petition. Smile
_________________
Billyray
using:
Linux distro: Ubuntu 10.04 LTS, Lucid Lynx, OOo Base 3.3, connected to MySql database using Java jdbc (note: ONLY sun-java-6-1.6.0_22 jre works right), and converted from MS Access 2003.
Back to top
View user's profile Send private message
acidblue
General User
General User


Joined: 24 Apr 2012
Posts: 7

PostPosted: Thu Apr 26, 2012 8:34 am    Post subject: Reply with quote

That sounds like a good idea, but what do you mean by 'detail lines'?
Back to top
View user's profile Send private message
Billyray
OOo Enthusiast
OOo Enthusiast


Joined: 06 Mar 2007
Posts: 144
Location: Lake Erie's Shore in Ohio

PostPosted: Thu Apr 26, 2012 9:52 am    Post subject: Reply with quote

What I mean is that the Client would be the main form, and the ref table would be the subform. Each "detail" line is simply a record in the ref table. By making it a subform, all of the records from the ref table would be listed (as line items you might say) in the subform which are related to that client. Within each record, the fields showing (depending on your design) may be only one (petition number listbox) You would select the petition number (or name or whatever it is) from the listbox that applies to that client (in the main form) when adding a new record in the subform: (detail line, or ref table record)
_________________
Billyray
using:
Linux distro: Ubuntu 10.04 LTS, Lucid Lynx, OOo Base 3.3, connected to MySql database using Java jdbc (note: ONLY sun-java-6-1.6.0_22 jre works right), and converted from MS Access 2003.


Last edited by Billyray on Thu Apr 26, 2012 10:14 am; edited 2 times in total
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Apr 26, 2012 10:03 am    Post subject: Reply with quote

[Example] Relations reflected by list boxes in forms (the forms about things and persons)
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
acidblue
General User
General User


Joined: 24 Apr 2012
Posts: 7

PostPosted: Thu Apr 26, 2012 1:21 pm    Post subject: Reply with quote

AAhh, ok, let me go back and do some tinkering with my database/forms and I''l get back to you.

Thanks for the link Villeroy Very Happy
Back to top
View user's profile Send private message
acidblue
General User
General User


Joined: 24 Apr 2012
Posts: 7

PostPosted: Sat Apr 28, 2012 4:34 pm    Post subject: Reply with quote

After some rearranging and other things I have a form with the Clients table as the main form and the ref table as the sub-form as suggested and I used a list box for the ref table.

But The list box only displays the key of the petition not the name of the petition.
Is there a way to have the list box for the ref table display the name of the petition instead of just the key?
Back to top
View user's profile Send private message
Billyray
OOo Enthusiast
OOo Enthusiast


Joined: 06 Mar 2007
Posts: 144
Location: Lake Erie's Shore in Ohio

PostPosted: Sat Apr 28, 2012 5:04 pm    Post subject: Reply with quote

Look at:

Sticky: Beginners - Example Database - Base Tutorial

at the top of the forum list. There is a good explanation on how to work with List boxes about half way down. Wink You're almost there!
_________________
Billyray
using:
Linux distro: Ubuntu 10.04 LTS, Lucid Lynx, OOo Base 3.3, connected to MySql database using Java jdbc (note: ONLY sun-java-6-1.6.0_22 jre works right), and converted from MS Access 2003.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun Apr 29, 2012 9:50 am    Post subject: Reply with quote

Look at the list box properties in my example db. They all use some record set like this:
SELECT "text field or concatenation" AS "VisibleField",
"PrimaryKey" AS "BoundField"
FROM "OtherTable"
ORDER BY "VisibleField"

Property "Bound Field" = 1 (which is the index of the second field, the visible text has index 0)
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
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