2

I am essentially trying to create a glorified contacts list using LibreOffice Base. Many of our contacts have multiple addresses (office, mailing, home), and sometimes multiple people have the same address.

I've created a simple contacts table with the Contact ID, Last Name, and First Name. I've created an address table with an Address ID, City, State, etc. I've also created a junction table with Contact ID and Address ID, and connected the three tables using the relationships tool.

Now I want to add everything into a single form. I watched this youtube video, which was very helpful, but I want to be able to add new cities instead of only selecting from a pre-established list. So I followed along with the video, but set the columns to "combo box" instead of "list box." However, when I try that I get an error message:

enter image description here

Error inserting the new record

SQL Status: 23000 Error code: -177

Integrity constraint violation - no parent SYS_FK_94 table: Address Table in statement [INSERT INTO "Contact-Address Junction" ( "Address ID","Contact ID") VALUES ( ?,?)]

I assume there's something obvious that I'm missing, but at this point I'm pretty stuck.

E: I took more screenshots to show the relationships, tables, and how things connect in the form:

enter image description here
enter image description here
enter image description here
enter image description here
enter image description here

Community
  • 1
  • 1
user9058183
  • 21
  • 3
  • Are your "foreign keys" exactly matching their counterparts in the parent table? – Hovercraft Full Of Eels Dec 05 '17 at 21:32
  • I ask because a Google search for [similar problems](https://www.google.com/search?q=libreoffice+base+integrity+constraint+violation+no+parent+in+statement+insert+into) suggests that the most likely issue in this situation is a foreign key violation. – Hovercraft Full Of Eels Dec 05 '17 at 21:34
  • I don't think that's it - the forms work if I'm using a list box, just not a combo box. I'm not 100% sure, though, since I'm still pretty new at this – user9058183 Dec 06 '17 at 01:11
  • The junction table should have two fields, and 1) **both fields** need to be assigned as "key" fields, and 2) in the Relationship design, these two fields need to be connected to the primary fields of your two other tables with the 1 to many relationship (1 to n) line extending from the original table key to the corresponding junction table key. Please check on this, and even consider posting a link to images showing what your table edits look like, and what your relationships look like. If you post links to images, I can edit your question and show the images in your question. – Hovercraft Full Of Eels Dec 06 '17 at 03:52
  • For example, link to an image like [this](https://i.imgur.com/Fjl14tQ.jpg) for your relationships. – Hovercraft Full Of Eels Dec 06 '17 at 03:59
  • OK, I took [more screenshots](https://imgur.com/a/sEqDD) and added them to the question. Does that help or is there something else you would need to see? – user9058183 Dec 06 '17 at 12:42
  • Btw - thank you for taking the time to look at all of this – user9058183 Dec 06 '17 at 12:42
  • You're welcome. Another possible issue per [this link](https://ask.libreoffice.org/en/question/128102/can-not-create-relation-in-base/) is that perhaps you've got "orphaned" records. This can happen if you try to create the relationships in a database that already has records. Otherwise I have no idea how to fully answer this question well since LibreOffice Base is a visual database and form designer, and other than SQL code that is injected into forms, not really a programming language or programming development environment. Meaning, it doesn't look like you can post... – Hovercraft Full Of Eels Dec 06 '17 at 14:06
  • ... code or a minimal program example plus sample data, and allow us to answer as we could with say a Java or C# programming problem. I suggest that you continue with Google searching, especially the LibreOffice question database, and compare your current problem to similar problems that the search brings you. – Hovercraft Full Of Eels Dec 06 '17 at 14:08

1 Answers1

1

In the video you cite, this is kind of silly as there is no need for a many to many relationship. The author could have simply added a Movie ID field to the genre Table, (so multiple genre records can point to each movie record).

I have a complex many-to-many contacts structure that I'm in the process of porting from MS Access to LibreOffice. It has 4 primary data tables: Groups, Address, Phones, and People. And there are 6 link tables to connect those primary tables: GroupAddress, GroupPhone, GroupPerson; AddressPhone, AddressPerson; and finaly PhonePerson. Unlike the video you cite above with only 2 fields, in my link tables there are 3 fields. GroupAddress for example has GroupAddressID (the unique id for the link table itself), GroupID (which points to the Groups table), and AddressID (which points to the addresses table). This allows any number of addresses per each group, and at the same time, any number of groups per each address.

So the whole thing has maximum data flexibility: For example it allows for unlimited number of people per group, each person can have an unlimited number of phones or addresses, each address an unlimited number of people, etc.

Implementing it in LO: Because in LO you can't edit a query based on more than one table (you can view it, but not edit it), like you can in Access, (and therefore you also can't build a form with a table that can edit a query based on more than one table), in LO this is not as clean as it is in Access.

In Access it works very well and the link tables manage themselves! In LO you have to manually edit the link tables in a separate table. I'm starting to think about how I might use some macros to improve on this, but for the moment it's bare bones.

To give you a better idea, the first form to edit Groups, which also edits the group's addresses, people, and phones (not just those tables, but also the links to those tables) looks like this:

Group lookup pulldown (used to find a group record)  
(this is a drop down box with custom code that helps me find group records)

Group editing fields, e.g. Group name, category, url, etc

Then below that

Group-Person links table

Then to the right of that

Persons table (plural) - to create new person, then..

And below that

Person table (singular)   
 [pointed to by Group-Person link]  
 to view person pointed to.

Group-Address links || (similar structure to above)
(for example, when a business has two or more addresses)

Group-Phone links || (similar structure to above)
(this is for phone#'s that the group owns directly, not personal phones of the group's members, and not phones tied to specific addresses).

----------

Then there are 3 other similar forms,

one for Addresses w/ Person, Group & Phone links;

one for Phones w/ Group, Address & Person links;

and one for People w/ Group, Address and phone links;

Here is a screen shot of the first of the 4 editing forms, to edit the groups and the links associated with the group:

enter image description here

Hope this helps. I would be interested to see what you develop. Thanks.

Elliptical view
  • 3,338
  • 1
  • 31
  • 28
  • I just noticed under LINKS: `pID` is in each of the 3 tables below it. This column might be better labeled in concert with their underlying field data: Person ID, Address ID, and Phone ID, respectively. – Elliptical view Dec 06 '17 at 19:26