Converting a Relationship from one:many to many:many in SugarCRM

Requirements change. And sometimes you just click the wrong button in Studio. Now you have this wonderful one:many relationship between two modules, but what you really need now is many:many relationship. How can we convert the relationship without losing data?

It’s a multi-step process that will involve some superior SQL shogun-ism. If you’re not a code-level developer and part-time DBA, it may be a good idea to read through this one before attempting.

Terms:

For the sake of the discussion, let’s assume we’re talking about a one:many relationship that exists between Accounts and Contacts, where an Account can have several Contacts, but a single Contact many only have one Account.

Step One: Create the subpanel definition.

The one:many relationship only created one subpanel definition, because that’s all that’s required. In our case, the Account has the subpanel, and the Contact only has a field.

Remove the field from the detail and edit views (and anywhere else you might have put it) on the Contact module.

Then add the subpanel definition. This is as streight-forward as copying the existing one and renaming some things. Copy the file from custom/Extension/modules/Accounts/Ext/Layoutdefs/something.php to custom/Extension/modules/Contacts/Ext/Layoutdefs/something.php.

Now, in your new copy, switch “Accounts” with “Contacts” and “Contacts” with “Accounts” to reverse the references. You’ll see this in the $layoutdefs["Accounts"], and the ['module'] key. You’ll also want to change the ['title_key'] key value to something relevant so that it outputs Accounts instead of Contacts.

Step Two: Modify the Relationship Metadata

When relationships are created, they’re housed in custom/metadata and the files are named something like accounts_contactsMetaData.php. Open up your Metadata file and make a few quick changes.

  1. Change the ‘true_relationship_type’ value to ‘many-to-many’
  2. Adjust the ['indicies'][1] an ['indicies'][2] values ([0] is the primary key, which is fine). Currently there are two keys created, but a many:many relationship only requires one.
1 => array (
'name' => 'accounts_contacts_ida1',
'type' => 'index',
'fields' => array (
0 => 'accounts_ida',
),
),
2 => array (
'name' => 'accounts_contacts_alt',
'type' => 'alternate_key',
'fields' => array (
0 => 'contacts_idb',
),
),

Will become

1 => array (
'name' => 'accounts_contacts_alt',
'type' => 'alternate_key',
'fields' => array (
0 => 'accounts_ida',
1 => 'contacts_idb',
),
),

Step Three: Modify the Relationship Table in the Database
Assuming the relationship is housed in a table called accounts_contacts, you’ll run a query similar to this:

alter table `accounts_contacts`
drop INDEX accounts_contacts_alt,
drop INDEX accounts_contacts_ida1,
add KEY `accounts_contacts_alt` (`accounts_ida`,`accounts_idb`);

You’ll likely have to adjust that heavily to suite your field names. You can run a ‘describe’ query on your table to get field names, and I found that the MySQL Workbench tool was helpful in diagnosing the need for this.

It’s worth noting that the next step (the repair/rebuild) may take care of this, but I’ve had awkward experiences with the SQL that this tool generated in the past. I wanted to handle this piece directly so that I a) fully understood what was going on and b) didn’t let a tool misguide me.

Step Four: Repair and Rebuild

The last step is the easiest. Cross your fingers and run a repair-rebuild. Test your new subpanels and see if you don’t have a many:many relationship functioning as expected.

If this helped you out, you owe me a high-five. Either way, feel free to leave questions or comments below!

This entry was posted in SugarCRM and tagged , , , , . Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

3 Comments

  1. Sugar dev
    Posted August 31, 2011 at 3:34 pm | Permalink

    Could this be added to module builder?

    • Matthew Poer
      Posted September 2, 2011 at 9:24 am | Permalink

      That would be difficult; this is more intended for an “oops”/”after-the-fact” change. In our case, we had an existing relationship defined and deployed that had been in use for several months, and so the data already existed. In module builder, it would probably be easier to just delete the relationship and create a new one correctly.

  2. Posted June 7, 2013 at 2:56 pm | Permalink

    Great post. I was checking constantly this blog
    and I am impressed! Very helpful info specifically the last
    part :) I care for such info much. I was seeking this certain information for a long time.
    Thank you and good luck.

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Sage President\

Profiling Solutions, Inc
174 Carroll Street
Atlanta, GA 30312
Sales: 1 (866) 515-9959
Office Phone: (404) 525-8003
Office Fax: (404) 525-8005
info@profilingsolutions.com