Jump to content
  • 0

How to manage many-to-many relationships



I manage a many-to-many relationship we have between some tables.



Primary tables are “Studies” and “Sites”. 

Have a table to join them, “study_sites”


For each Study many sites could be associated. Sites can be associated with many Studies


I can’t find anyway to create a UI that can:

  • Show me all the sites that are currently associated to a site.
  • Allow me to remove an existing association
  • Allow me to add a new association, but also ensure the site/study relationship does not exists. (don’t want any duplicates in “study_sites” table)


e.g. I can use two data pages.  Top one shows all sites that are currently associated. Bottom submits a form add a new record to  “study_sites” table. The problem is this can allow duplicates.

Is there someway to stop a duplicating from being inserted by triggers?

There some smarter way to do this?

I did try using a table report adding SQL to a calculated field, but it won't render the HTML 

 WHEN (SELECT COUNT(study_site_id) FROM sr_tbl_study_sites WHERE study_id='GU986E2K34MD' and site_id=target.[@field:site_id]) = 1 THEN 'Exists, click to remove'

 ELSE '<a href=/admin-associate-study-site.html?study_id=GU986E2K34MD>Not associated, click to add</a>'



I have similar requirement to association many users to many sites.

any advice greatly appreciated.


Link to comment
Share on other sites

5 answers to this question

Recommended Posts

  • 0

I would refrain on using List data types. Right now, they have a very limited use as they cannot be used in both triggered action and SQL.  

Im still not sure if I got your inquiry correct, but if I do, what I think you wanted is that a user should only be able to see a site if they are assigned to that site.

What I would do on this case is that, I would create a text255 or text 6400 on the site table. then per site, I will have a comma seperated values of the users ID there which is allowed to view the specific site.

When you then create a datapage, it can be then filtered that if the CSV field contains an ID of the current user, then show the record. Otherwise, dont.


Link to comment
Share on other sites

  • 0

In my idea about assigning a staff to that site, I would have an editable tabular report that dsplays the user. This page should only be accessible by admin and higher and from here, they have a dropdown where they can set which site that user belong to.

I would then have a trigger on the user table that runs on update (or even on insert). This trigger will add or delete the user ID on the Site record depends if they are assigned there or not.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...