Hierarchial relationships

[mariluzrm]

Hello.
I want to do a join list with hierarchial tables, how can I join this tables? (see attached)
Note that a record with the same id must be created in the animals and carnivores / animals and herbivores tables, that is, for example, for a carnivore a record must be created with id_animal = 1 in the animals table and a record with id_animal = 1 on the carnivore table.



Thanks.

 

[juuser]

Wouldn't simply this setup of joins work?

1) from animals.id to carnivores.animal_id
2) from animals.id to herbivores.animal_id

if needed
3) from animals.zoo_id to zoo.id

 

[mariluzrm]

But in the form I would have two repeating groups, one for animals and the other for carnivores and I don't want to appear two ... Imagine creating a new record ... I don't want to create a new record in each table (animals and carnivores). This must be transparent to the user. I would like a single record to be added with all the animals + carnivores fields. I don't know if I explain myself ...
I.e in zoo form (join list zoo and animals) I want add carnivores and herbivores with all fields (the animal's fields too)

 

[juuser]

Seems you want to achieve something similar:
http://fabrikar.com/forums/index.php?threads/list-join-table-template.52326/

I would rather think through the setup. E.g. like having the carnivores and herbivores in same table where you choose one or the other from a dropdown.
If needed the extra fields can be in the same table in separate groups and you could show/hide the groups based on the dropdown selection etc.

 

[Nagtegaal]

Personally I would keep 1 table for Animals and have an element with a dropdown choice for carnivore or herbivore and another element for there favorite food. You can then use the element carnivore or herbivore as a filter in the list.
If you really want to store the animal_id in 2 tables the user must still be able to set his choice if it is a carnivore or herbivore in the Animal table. Then with a PHP event you can create the animal_id in one of the subsequent tables. Since my SQL is much better then PHP an alternative is to built an Insert trigger on the Animal database table.

 

[mariluzrm]

I would rather think through the setup. E.g. like having the carnivores and herbivores in same table where you choose one or the other from a dropdown.
If needed the extra fields can be in the same table in separate groups and you could show/hide the groups based on the dropdown selection etc.

But this is how I can do it if I register the animals in the "zoo" form? (Having a join zoo - animals). I think that in repeatable groups I can't:

... separate groups and you could show/hide the groups based on the dropdown selection etc.

Is that so or am I wrong?

If you really want to store the animal_id in 2 tables the user must still be able to set his choice if it is a carnivore or herbivore in the Animal table. Then with a PHP event you can create the animal_id in one of the subsequent tables. Since my SQL is much better then PHP an alternative is to built an Insert trigger on the Animal database table.

Although I would like to leave it as is (in separate tables). But I don't know how to complete in the same repeatable group the weight field (animals field) and the fields of herbivoros table.

Thanks

 

[juuser]

I think I'm still not sure how exactly you would like your setup to be.

If you cannot solve this, you can make a sketch of form layout and elements e.g. in Word or Excel. And preferably in English. This would make it much easier to help.

 

[Nagtegaal]

Maybe the suggestion of @juuser helps indeed. If you create a table animal_type with records for carnivore and herbivore you can put the id of that record (id_animal_type) in your animal table

 

[mariluzrm]

Of course!! , I attach it. Thanks!!



Yes, animals table has animal_type field.

 

[juuser]

OK, seems clear now, I guess

You can just make the joins in zoo table like:

1) zoo.id to herbivores.zoo_id
2) zoo.id to carnivores.zoo_id

And both, in herbivores and carnivores table add databasejoin element referring to animals table.

Make the joins repeatable and that should be it (if I'm not too tired and not missing something obvious).

 

[mariluzrm]

Many thanks!! What I don't understand is:

And both, in herbivores and carnivores table add databasejoin element referring to animals table.

Why a databasejoin element? What I need is to add (with php, I guess) a record in animals every time I add it to herbivores or carnivores, right? And also being able to enter the data in the fields of the animal table, how do I do this?
Sorry to bother you, I'm new to web development and I don't have much of an idea (I've always developed desktop applications ...). Also I don't speak much English ...

Thanks again!!

 

[juuser]

Now I'm lost again. Don't you have animal names already pre-filled in Animals table so you could choose them from the dropdown of databasejoin element referring to Animals table?

 

[mariluzrm]

No, when I add an animal (herbivore or carnivore) I add all the fields that are shown in the form (table animals fields and table herbivores (or carnivores) fields (look at the form I have attached before. All these fields are entered (and the problem is that they are distributed in the animals table and in the herbivores and carnivores tables, in addition to zoo). Keep in mind that for example the weight field is from animals (and that each animal has its specific weight, can be already pre-filled)

 

[juuser]

I think you really need to think through the whole setup again.

If you also need to add "animals" from your main table, you can have "Add option in front-end" enabled in databasejoin element which is referring to animals table.

I cannot see any other reasonable way to add the animals. If they will be added in "main" table manually every time, you will have a mess. Someone enters Sheeep, someone Shep etc. and how would the user know the animal id (this should be coming from animals table via databasejoin element in my opinion).

No, when I add an animal (herbivore or carnivore) I add all the fields that are shown in the form (table animals fields and table herbivores (or carnivores) fields (look at the form I have attached before.

If this is the goal, why do you need animals table at all?

P.S I might be too tired for this today, so might be missing something important :9

 

[mariluzrm]

Wow, I thought I could do it

 

[juuser]

There is very likely a way to do what you need as I'm almost sure the problem is in general logic of databases/lists setup. I just cannot fully understand your exact goal based on your descriptions. There might be someone else with a fresh view who could understand it better and can give better advice.

Meanwhile I suggest to read wiki about Fabrik list joins:
http://fabrikar.com/forums/index.php?wiki/list-joins/

And maybe also something general about relational databases.

Good luck

 

[mariluzrm]

If this is the goal, why do you need animals table at all?

Then the animals have diseases, treatments, and I am interested in having a unique table with all the animal IDs...

 

[mariluzrm]

There is very likely a way to do what you need as I'm almost sure the problem is in general logic of databases/lists setup. I just cannot fully understand your exact goal based on your descriptions. There might be someone else with a fresh view who could understand it better and can give better advice.

Thank you, I hope someone can help me

 

[juuser]

OK, in that case my previously suggested setup should work perfectly. You can have the "common" animals "pre-filled" in animals table. And if the animal is missing while filling in the "main" table, it can be added via "Add element in front-end" option which creates "add" button next to databasejoin element.

 

[mariluzrm]

OK, then my previously suggested setup should work perfectly. You can have the "common" animals "pre-filled" in animals table. And if the animal is missing while filling in the "main" table, it can be added via "Add element in front-end" option which creates "add" button next to databasejoin element.

It's not valid. In the animal table there are no generic animals (lion, tiger, giraffe, etc) there are specific animals peter_the_lion, john_the_tiger, sarah_the_giraffe, ect) so the animals table cannot be preloaded, I don't know if I'm explaining myself? There can be a field in animals that is animal_type (dropdown: tiger, lion, giraffe, these values are generic, because it is the type).
But animals - herbivores and animals - carnivores are 1 TO 1 relationship

I'll quit, tomorrow more ... Thanks