• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

Adding data to two joined tables

relsig

Member
Hi

I have been working on a address directory for our first aid association using fabrik. Unfortunately I don't get any further.

I have tree tables:
- osv_vereine (containing all the sections in the association)
- osv_adressenliste (containing all kinds of addresses of people in the sections)
- osv_kl_tl_ausbildung (skills of these people)

I made three list with these three tables, no problem.
osv_vereine to osv_adressenliste is a one to many relationship
osv_adressenliste to osv_kl_tl_ausbildung a 1:1

Also the forms work well. Displaying the information and editing existing people works fine.

Adding a new person though doesn't really work. Adding a person seems to work. If I try to edit the person I get a duplicate key error from the skills table.

What I would like to do is that I can add people, at the same time fill out the skills (different group in the form), as well as also add just the address and a couple of weeks later add the skills.
The skills group is normaly hidden. It is made visible with the right top most radio button setting KL/TL to ja.

It seems the me that there is a problem in the join of the tables.

I add a couple of pictures.

List = showing an overview of data from all three tables
Join = Join statements used
Detail = result (skills group at the very bottom "Technische Ausbildung")

Can you give me a hint?

Thanks

Roger
 

Attachments

  • List.JPG
    List.JPG
    28.5 KB · Views: 445
  • Joins.JPG
    Joins.JPG
    65 KB · Views: 449
  • Detail View.JPG
    Detail View.JPG
    63.9 KB · Views: 446
In Fabrik one "leg" of a join has to be a primary key, so what are the PKs of your tables.

But not sure about your setup.
In which list did you add the joins, adressenliste?
If adressenliste - ausbildung is a 1:1 you could use just one list with an additional group "Techn. Ausbildung"
 
Hi thanks for your answer.

I have primary keys in all three tables. Do I have to remove the primary key in ausbildung?
Yes the main list is adressenliste. The joins in the picture are in the list adressenliste.
There is a 1:1 between adressenliste and ausbildung.
I don't understand what you mean with "just one list with an additional group"
I setup three lists for all the three mentioned tables.

Would you mind having a look at the page?

Thanks

Roger
 
Just FYI, troester isn't able to see the My Sites info. That's only available to Rob and myself.

I'll take a look.

-- hugh
 
OK, I'm logged in, but I can't actually do anything, as I'm getting:

Forbidden

You don't have permission to access /XXX/administrator/index.php on this server.

... whenever I submit any kind of backend form, like List settings. Not just Fabrik pages, I tried setting the backend language to en-GB for the account I'm using, and that failed with the same error. Of course it doesn't actually say XXX, I've just changed that for anonymity.

-- hugh
 
Hi Hugh

Was able to reproduce the problem, but don't know the solution yet. Will inform you as soon as possible.

Thanks

Roger
 
Can you try again? Was a firewall topic. Sorry about the language of the user. Now it is in English. I did some changes in Fabrik with the user you have. This worked.

Roger
 
OK, as troester mentioned, one of the restrictions on our joins is that one end of the the join has to a Primary Key (long story). And on your address list you have:

inner JOIN osv_vereine ON osv_adressenliste.EDV = osv_vereine.EDV

... which is FK to FK, which we don't support. So ... you'll need to modify that so the "child" (vereine) points back to the PK on the "parent" (adresseniste).

I didn't change anything, as I don't know if your site is backed up. Obviously changing the join structure when you already have data in the tables will break it. If it's test data, that's not a problem, but I can't make that assumption.

-- hugh
 
Thanks Hugh for your support. The site is a testsite with play data. I'm doing a backup right now to be save.
I think I know if I understand you right. The name of the section (verein) is only written in the osv_vereine table, the address table has just the number. I understand this is a foreign key, correct?
The addresses table (adressenliste) and the skills table (ausbildung) are linked by another number (lehrer_id). When I get you correct this shouldn't be a problem. The problem is the first connection.

Could you show me what I have to do? Doing a databasejoin in the vereine table wouldn't help me I'm afraid. Every section can have multiple address.

Thanks a lot

Roger
 
I'm trying to work on this now, but I'm going to need you on live chat with me, so we can work out together what needs to happen. Basically I'm not sure what the EDV element is. But that's the join which will definitely need changing, as EDV isn't a PK on either table. The vereine end of the join will have to have an FK (foreign key) that points back to the PK of the addressenliste ... but I'm not sure if that's supposed to be a one-to-many, or a many-to-many ... so we may need a "linkage" table, like vereine_addressen, which allows the many-to-many.

So ... can you get hold of me either on the live chat (bottom right of Fabrik home page), or find me on Skype ('cheesegrits'). If we need to schedule a time, let me know what day(s) / time(s) are good for you.

-- hugh
 
Hi Hugh

I'm on the road at the moment. I will be home at 4pm CET (Central European). What timezone are you in? Can we try at 7pm CET?

Thanks

Roger
 
Hugh gave me your login data, so I could have a look (it's easier to understand your setup in German).

1. I think you don't need the join Adressenliste-Vereine at all, the dbjoin element EDV in Adressenliste is enough. But the setup should be value = Vereins_ID (which is the PK of Vereine) even if vereine.EDV is an unique key (so maybe rename adressenliste.EDV to something like j_vereins_id to avoid confusion).

2. Not sure about your Ausbildung table.
You are joining from Personen_id (=PK adressenliste) to Lehrer_id (PK ausbildung) which can't do.
You can join from Personen_id (=PK adressenliste) to ausbildungs_id (PK ausbildung).

But I didn't get the Ausbildungs-logic:
is the person (in the addressenliste) a teacher (Lehrer) and Ausbildung describes the (exactly one) course he may hold?
Why is the PK (internalid) of ausbildung called Lehrer_id, did you change this?
I think it would make sense to have ausbildungs_id as PK and Lehrer_id as dbjoin to adressenliste (if adressenliste contains Lehrer (=teachers)). In this case the join Personen_id (=PK adressenliste) to Lehrer_id (foreign key in ausbildung) would be correct.

In your first post you said Adresse-Ausbildung is 1:1, this means one person (Adresse) has exactly one Ausbildung/skill (not many) and nobody else has this Ausbildung? (Usually I would think of a 1:n or many-to-many relation).
 
Thanks all of you for your help. I got a message from the user Jaanus who did a copy of the Adressen list and made an adjustment. I'm not sure what he did, but it seems to work.

Getting back to the thread above. I also added a picture of what I would like to do.

1. That might be the case. I didn't know how I could display a list in Fabrik containing data from all three tables. The join sure helped. At least to have such a list. (see attachment). If there is another way of doing that I would be pleased to know how. Jaanus has probably done that. The EDV is not the primary key since I thought that every Fabrik table uses an internalid for each row. Since this is a increased number I figured I could use that. EDV is like a ZIP code, in my case for different sections and not cities.

2. You are right I have there two id columns. I looked at it the same way as described with the internalid above. A person in adressenliste doesn't have to have a record in the skills table (ausbildung). That was my thought. Since the Lehrer_id is increased automatically I thought I need another id. Probably I just have a record in ausbildung for everyone, bit the record can be empty for people without the desired skills.

The peron in the adressenliste can be different kind of people. We have teachers (with the skills), section presidents, honorable members, guest etc. We need to track the education of our first aid teachers, that's why just those people have a record. Every teacher has one record with many columns (see attachment). So a 1:1 relationship.

I'm online at the moment, a chat or skype might make it easier, if you have time.

Thanks

Roger
 

Attachments

  • My desired configuration.pdf
    280.8 KB · Views: 415
What Jaanus did helped this solved the problem. I was looking to far to solve the problem.
I will change the settings in my Fabrik list tomorrow to see if it works according to my desire. I'm pretty confident.

Thank you to all of you for your support.

Roger
 
We are in need of some funding.
More details.

Thank you.

Members online

No members online now.
Back
Top