Steve,
I know you helped me in the past with checkboxes and relationships, here's hoping you can weave your magic again.
I have 3 tables:
quality
quality_markdowns
quality_markdowns_connection
The main table is quality. There is a list of options in quality_markdowns, and the joining table is quality_markdowns_connection.
The only table visible to users is quality, so all the following files are under the quality sub-directory.
::fields.ini
[MarkedDown]
widget:label = "Marked Down for"
widget:description = "Select all the reasons this quality has been marked down"
widget:type = "checkbox"
transient= 1
relationship = Markdown
::relationships.ini
[Markdown]
__sql__ = "SELECT * FROM quality_markdowns_connection rqmc INNER JOIN quality_markdowns rqm ON rqmc.quality_markdowns_id = rqm.ID WHERE rqmc.quality_id = '$ID'"
action:visible = 0
action:addexisting = 0
action:addnew = 0
action:label = "Reasons for Markdown"
section:limit = 20
It all works perfectly.
However, I now want to restrict which items in the quality_markdowns table are visible in the checkbox for the quality form. To do this I have added another column (retired). I only want it to return values where retired=0 (ie the markdown options are "live").
After reading through the forums, I tried several things:
-adding the AND rwm.retired = 0 to the __sql__ in relationships.ini
-> no joy
-adding
[Active_Topics]
__sql__ = "SELECT rqm.ID, rqm.markdown_option FROM quality_markdowns rqm WHERE rqm.retired=0"
to valuelists.ini
and
vocabulary:existing=Active_Topics
to relationships.ini
-> this caused only the selected items to show, and only show the ID numbers.
--Question--
Is there a (easy) way of restricting which options are returned from the quality_markdowns table to show in the checkbox for the MarkedDown field in quality? Or am I being a numpty and missing the obvious?
Thanks for your time.
Simon