Restricting checkbox relationship field

[notesgnome]

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

[shannah]

A little-used directive for the relationships.ini file is the vocabulary:existing directive that allows you to specify a valuelist that should be used to define the addable values for a relationship.

e.g.
relationships.ini file:

Code: Select all

[my_relationship]
    __sql__ = "select * from tableb where parent_id='$id'"
    vocabulary:existing="my_relationship_addable"


valuelists.ini:

Code: Select all

[my_relationship_addable]
    __sql__ = "select id, name from tableb where addable=1"


Or something along those lines.

[notesgnome]

Apologies if I don't quite understand.

I have a many to many relationship, so, presumably, I need the long __sql__ in the relationships.ini
__sql__ = "SELECT * FROM quality_markdowns_connection rqmc INNER JOIN quality_markdowns rqm ON rqmc.quality_markdowns_id = rqm.ID WHERE rqmc.quality_id = '$ID'"
which is covering my links to the joining table.

If I add the:
vocabulary:existing="Active_Topics"
to relationships.ini

where (in valuelists.ini)
[Active_Topics]
__sql__ = "SELECT rqm.ID, rqm.markdown_option FROM quality_markdowns rqm WHERE rqm.retired=0"

I get an error at the top of the screen:
Warning: array_keys() expects parameter 1 to be array, null given in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\cc\xata\Dataface\Relationship.php on line 1272 Warning: Invalid argument supplied for foreach() in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\cc\xata\Dataface\Relationship.php on line 1272

which, I suspect is what's causing my selection to not be displayed. ( A new record has no options, an existing record has
Marked Down for: ID=5 ID=6 ID=20 ID=21
displayed in edit mode (although in view mode, it doesn't error, and displays the markdown reasons).)

I have checked my sql for the valuelists.ini, and it returns data with only the correct entries listed.

I know we had an issue with checkbox relationships before (with AND AND being returned which you patched). Hopefully this isn't something similar.

Thanks in advance for your continued assistance

Simon

PS. Running Dataface 1.3rc3 2167

[notesgnome]

sudden thought (having a quick glance at the php)

Do I need a different valuelists.ini

I currently only have 1 for the main table.

Is this meant to be in a directory for the joining table or the option table at all?

[notesgnome]

That was it, it needed to be in the quality_markdown directory.

It now all works as expected.

Thanks again.

[notesgnome]

One last question...

If I have an entry that has "old" options from the relationship selected (ie the record was created when the option was valid, but the option is now "grandfathered"), when opened in edit mode, the relationship will show the "grandfathered" option, but as follows:
ID=32 (for example).

I suspect there's no way to hide these options is there?

[shannah]

Can't think of an easy way to hide this off the top of my head. You could do a javascript find/replace on these.

[notesgnome]

Ok, thanks.

They'll have to live with it (it'll be very few cases where they'll even see it!!!)

Cheers