Best way to create a many to many page

Wappler General
#1

I’m flying now with Wappler creating full CRUD pages very quickly.

I have three tables - schools, events and a many-to-many to link them. This is because multiple schools can be linked to an event and multiple events can be linked to a school.

What’s the best way to build this page to make it easiest for admins to manage it? I’m thinking they select the event, then see a list of all the schools and tick all those which are to be linked to it. I’m struggling to work out in my mind how to go about that, though.

Can someone give me a steer? Cheers.

Inserting and updating a new record in multiple tables joined by a foreign key?
#2

@sitestreet, so glad to hear it! I am very much the same way and can now create CRUD pages very quickly.

I have a similar concept on my site as well and I have been working through my work flow for it.

What I am thinking about doing is something like this:

  1. School table
  2. Event table
  3. Join table

In the join table, I would insert the school table id and the event table id. This way, a new record could be added either through the school page or through the event page.

On the school and event pages, I would have an add button that links to a modal and have the insert form there. Within the form on the school page, I would have a list of the events to choose from to add and I would have the school id as a hidden field. Within the form on the event page, I would have a list of schools to choose from to add and I would have the event id as a hidden field. That way, no matter which page the admin visits, they could add either the school or the event.

That’s my thought process at least. Would love to hear others feedback because it would help me out too.

1 Like
#3

@scott, great to hear you’re doing much the same as me.

Your description of the tables is exactly what I have. The join table literally has three fields - an index autoincrement, schoolID and eventID. What you’ve suggested sounds like a good solution. I will give it a go.

My only concern is when there are lots of schools to add to an event. I don’t want the admins to have to add them one at a time so a list of them all with checkboxes alongside is the obvious solution and it’s that I’m not sure how to go about in Wappler.

#4

That is a good point. I currently have it setup to add one at a time. However, I do have a search field above the table list that filters the table list. So it is easy to search for a match and then select the checkbox to add.

I have not done multiple selections as of yet. This might help though, @sitestreet, Select all or more rows from the database to delete

#5

I’m wondering if creating two columns is the solution - one containing all the schools and the other starting empty. Clicking on a school on the left moves it to the right. This would be very quick. Likewise, clicking on the right will move it back to the left.

So the workflow would be… select the event, see all the schools on the left, click them to move them to the right. Save when done or, even better, the records are created/deleted on each click.

What do you think?

#6

I have seen what you are describing on other websites, but I have not seen anyone do it in Wappler as of yet so I am not sure how to accomplish it. If that is possible, it would definitely make life easier. :slight_smile:

#7

Agreed. Over to @George and @Teodor :wink:

#8

I have done this with check boxes in the past.

Basically I have a server connection which selects all the items to link, in your case that would be events I assume
You then generate a dynamic list of check boxes each with the unique id’s of the events labelled with the name

This is then submitted and in the server connection you simply set up a repeat and insert the id’s into the link table

In production I also set the check boxes to checked for those already selected which allows the server action to simply delete all the linked events then re-create them, much easier than having a “if not present insert otherwise update” type logic

Example

image
image.png1732×582 106 KB

#9

I’ve done something similar to add related records in a many:many relationship. Eg in this example subjects are assigned to items. Something to consider - to make entry quicker - is to use keyboard shortcuts. The mouse isn’t used at all while searching and adding related records below (though the buttons can be used too):

manymany
manymany.gif794×646 1.5 MB

If the number of items to choose from is relatively small, I think a checkbox solution - as suggested by @Hyperbytes - would be a better option.

(I should have added that the editing facility shown appears in a modal window - so the window resizing is probably not as irritating as it seems here.)

#10

Hi @Hyperbytes and @TomD

Thanks for the really helpful info. I shall look at both of those and see how I get on.

Cheers

Jon

#11

Hi everyone

I’ve got it all working using the technique suggested by @TomD. I put them in two columns (unassigned and assigned) with + and - buttons. I went one stage further and did a custom query so the left column (unassigned) only showed the schools which weren’t in the right column so every school is in just one column and clicking the + and - buttons switches it across. It works a dream.

In case it’s helpful, the SQL for the unassigned list is this:

SELECT
*
FROM
schools
WHERE
SchoolID NOT IN (
	SELECT esSchoolID FROM eventSchools
	WHERE esEventID = 1)

(NB. Change the 1 at the end to :P1 so it gets the ID from the selected record)

Thanks again for all your help.

4 Likes