Customize "add existing records" in a relationship

[conkhikho]

I have two tables, Assets and Events, which have a many-to-many relationship, which is maintained in the table asset_event as follows:

Table Asset (id, Name, Year)

Table Event (id, Name, Year) (event's year is not necessary the same as its associated assets)

Table asset_event (a_id, e_id)

Problem: We have a lot of events and this causes inconvenience when a user chooses to add existing related Event records, because he/she needs to go through a long list of events.

It would be much easier if the user is given the choice to jump, say, in decades. For instance, there can be a select list for the user to choose which decade he/she wants to see. Depending on which item the user chooses, respective events in that decade are shown.

Do you guys know how we can go about achieving this? Any pointer is much appreciated.

Thanks a bunch guys.
Tim

[shannah]

There are many ways to achieve this, but no clear-cut winner. Here are just a few of the options.

1. Create a custom action for adding these records. This gives you full flexibility to do whatever you want -- but it means some manual coding on your part.

2. Set the title on the destination table so that it is something like "DECADE/More info" (e.g. 80's/Event Title). That way the select list will show the records that can be added in this format, and you can easily jump to a decade by typing "80" with the select list open (for example).

3. Define a custom vocabulary on this relationship, to achieve the same thing as #2.

4. Use javascript (e.g. jquery) and slots to insert your filter fields just before the "add existing related records" select list. E.g. you might add a select list for decades in a slot just before the existing related records form, and use javascript to filter the existing related records select list depending on what is selected in that field. (To find out which slot or block is best for you add the debug=1 directive at the beginning of your conf.ini file to have a listing of all available blocks and slots.

5. Instead of using the add/existing record form, you could add a transient field to your record with the grid widget, then add your related records using the grid widget (and the lookup widget - which allows you to easily do searches and add new records on the fly).

6. Instead of using the add existing record form, you could create a utility relationship only to the asset_event table. This would be a one to many relationship. You can add records to this relationship using the new related record form, and via the lookup widget for the e_id field you could make it "like" an existing related record form. Effectively adding a "new" related record to this relationship is equivalent to adding an "existing" related record to your existing many-to-many relationship.

7. I could go on all day, but hopefully one of these hits the mark.

Let me know if you require more elaboration on any of these approaches.

-Steve

[omills]

Hi Steve,

Could you clarify what you mean by a "utility" relationship (in option 6) and how you would go about setting that up.

Also, would it be that difficult to replaced the drop down select box with a lookup widget?

Thanks!

[shannah]

Any relationship that has an add-existing option will be a many-to-many relationship that involves at least 3 tables (the source table, a join table, and the destination table).

My suggestion here was to create a relationship that only goes half way (i.e. just the source table to the join table). Then adding a new record to this relationship would be similar to adding an existing record to the full relationship.

For example, Suppose I have a table "groups" and a table "users" and a join table "group_roles" that keeps track of the roles a user has for a group. The group_roles may look like:

Code: Select all

create table group_roles (
    group_id int(11),
    user_id int(11),
    role enum('User','Admin'),
    Primary Key (`group_id`,`user_id`)
)


So the groups table would have a relationship defined on it called "roles" that conceptually is a many-to-many relationship to the "users" table. However we can simplify things by only relating it to the group_roles table.

Code: Select all

[roles]
    __sql__ = "select * from group_roles where group_id='$group_id'"


Then you could use whatever widget type you want for the user_id field of the group_roles table. Select, lookup, autocomplete, etc...


_Steve

[jmboettger]

i got the same issue. But i need it to add more than one record to the relation table. I realized it like you wrote but i'm just able to add one record, although it presents a advmultiselect list. Any ideas?

Best Regards

Jakob

[shannah]

You may want to check out the grid widget or the checkbox widget for adding multiple records to a relationship at a time.