What Join path is the best here

[hsmdk]

Hi I have to lists

a Movie list and an a Actor list i would like to gain what you can see on the images

What have i tried

1. Made a list move_cast and made elements like "actor" movies" and "role" the movie and actor field is a databasejoin field and the role field is a text field. and in the movie list I have made a databasejoin to the "move_cast". this works . I can see the actor and the role.

I have tried to join the "move_cat" in the actor list so i can get the movie name and the role, but i cant get to work.

If any one have tip to what path i have to go i will be happy.

By the way, for some time ago there were some videos there gave an example of how to make a many-to-many relation. I can't find this... does anyone have a clue?

I found one video there partly tell about the subject, but not to the end.

Fabrik Many To Many - New tutorial - YouTube

Regards

 

[troester]

I don't know what you mean with "I found one video there partly tell about the subject, but not to the end." but @startpoint saved also the old Fabrik videos on his site https://www.youtube.com/channel/UCshAqi3jtr3nK_hWSDx2KVg/videos

 

[hsmdk]

Thanks, it was properly what I had in mind, but am pretty sure that have seen a video there give an example of how to see data from both angels as far I remember it was band vs. venuse where it showed that venuses have what bands and bands were on what venues.

Like my example jus with movies vs actors

 

[Stevebenson223]

For many-to-many relationships you need a third table as an intermediary between actors and movies, which would create a separate record for every actor and every role in every movie. If you have a lot of movies and you will be listing all the actors, this might be huge!
I had a similar requirement for theatrical performances and I'm using a different approach, which might work for you too?
Two lists: an actor list and a movies list.
In the movies list, create a new group for "cast" which has two elements: actor and role. Actor is databasejoin element to actor list and role is text element. Set the group as repeating so you can have as many rows of actor/role as you need.
Now, in your Actor list you should be able to use the Related Data tab under Lists-Data to link to your Movies list. This gives you a new column for each of your actors which will display all the movies for that actor.
I haven't yet got a way to display the role but this might start you on a better path?

 

[hsmdk]

Hi, and thanks for your input. And I am on precisely this path as you suggest.


I have now list like this


movie - Have all info about the movie


movie_actor - This has an actor field with a database join to the actor list, and a text field to the role, and an image( upload field)


actor - Have all the information about the actor


actor_movie - There are these fields actor_id, movie_Id, movie_name, movie_year , movie_image


In the movie list, there a database join to the movie_actor list, so now I can post actors and the role name, and upload an image


So far so good, but I do have some issues,


When I select the actor in movie_actor form it will not save it. It just goes back to the first in the actor list

and last but not least, if I eg have 3 actors and want to delete them, it is not possible to delete the first one.


My next step



What I need is a PHP code there to create/update the actor_movie fields (if someone knows someone there can make this I am ready to pay for it). By this, i can make a database join in the actor list to the actor_movie list as read-only. So now I can show want movie the actor has been part of.


Any input are welcome

Regards