I manage a many-to-many relationship we have between some tables.
e.g.
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
CASE
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 '<ahref=/admin-associate-study-site.html?study_id=GU986E2K34MD>Not associated, click to add</a>'
END
I have similar requirement to association many users to many sites.
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.
Question
skwaler
I manage a many-to-many relationship we have between some tables.
e.g.
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:
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
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
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.