{"id":266,"date":"2013-11-06T22:19:23","date_gmt":"2013-11-06T18:19:23","guid":{"rendered":"http:\/\/www.bandidor.info\/wp\/?p=266"},"modified":"2013-11-06T22:22:36","modified_gmt":"2013-11-06T18:22:36","slug":"many-to-many-relationships-in-sugarcrm-via-rest-and-perl","status":"publish","type":"post","link":"https:\/\/www.bandidor.info\/wp\/?p=266","title":{"rendered":"Many-to-many relationships in SugarCRM via REST and perl"},"content":{"rendered":"<h1><strong>Subject<\/strong><\/h1>\n<div>In my previous article &#8220;<a title=\"Create appointments (meetings) in SugarCRM via REST and perl\" href=\"http:\/\/www.bandidor.info\/wp\/create-appointments-meetings-in-sugarcrm-via-rest-and-perl\/\">Create appointments (meetings) in SugarCRM via REST and perl<\/a>&#8221; I described how I created appointments using Web services. This worked, but with one major glitch &#8211; solution provided didn&#8217;t put appointment status in accepted for the appointment owner and I was determined to solve this. So, read on.<\/div>\n<h1><strong>Challenge<\/strong><\/h1>\n<div>An appointment created simple way is linked to two persons &#8211; appointment owner (<em>User<\/em>) and appointment participant (<em>Contact<\/em>). Just creating links to these two won&#8217;t set appointment status to accepted and I would say this is not logical for the appointment owner. \u00a0If we for example consider<strong> My Meetings<\/strong> dashlet, we realize that for these appointments there is still an option to accept or decline as on the screenshot below:<\/div>\n<div><a href=\"http:\/\/www.bandidor.info\/wp\/wp-content\/uploads\/2013\/11\/ScreenShot050_1.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-280\" alt=\"ScreenShot050_1\" src=\"http:\/\/www.bandidor.info\/wp\/wp-content\/uploads\/2013\/11\/ScreenShot050_1.png\" width=\"639\" height=\"234\" srcset=\"https:\/\/bandidor.info\/wp\/wp-content\/uploads\/2013\/11\/ScreenShot050_1.png 639w, https:\/\/bandidor.info\/wp\/wp-content\/uploads\/2013\/11\/ScreenShot050_1-300x109.png 300w, https:\/\/bandidor.info\/wp\/wp-content\/uploads\/2013\/11\/ScreenShot050_1-250x91.png 250w, https:\/\/bandidor.info\/wp\/wp-content\/uploads\/2013\/11\/ScreenShot050_1-150x54.png 150w\" sizes=\"auto, (max-width: 639px) 100vw, 639px\" \/><\/a><\/div>\n<div>\n<p>The reason for this is the following. The relation between appointment (<em>Meeting<\/em>) and appointment owner (<em>User<\/em>) is a many-to-many relationship, which is stored in the table <code>meetings_users<\/code>. But this table also holds the information about appointment accept status in the field\u00a0<code>accept_status<\/code>. \u00a0By the way, same is valid for appointment-contact relationship, it will be\u00a0meetings_contacts table, but I will focus on appointment owner only.<\/p>\n<\/div>\n<div>Fact is that my first solution doesn&#8217;t update this\u00a0\u00a0<code>accept_status<\/code> field and searching for a solution has turned out to be challenging.<\/div>\n<h1><strong>Platform\/Tools<\/strong><\/h1>\n<p>Nothing new here:<\/p>\n<div>\n<ul>\n<li>SugarCRM CE (Community Edition) version\u00a06.5.16<\/li>\n<li>ActiveState perl\u00a0(v5.14.2) on Windows 7<\/li>\n<li>And also one nice perl module\u00a0<a href=\"http:\/\/search.cpan.org\/~nito\/Net-SugarCRM\/lib\/Net\/SugarCRM.pm\" target=\"_blank\">Net::SugarCRM<\/a>\u00a0found on CPAN (version\u00a02.17738).<\/li>\n<\/ul>\n<\/div>\n<h1><strong>Solution<\/strong><\/h1>\n<div>The problem I&#8217;m trying to solve could be described as follows. Many-to-many \u00a0relationship between Meetings and Users has some additional attributes and we need to update these attributes. It turned out that the support for this is not completely implemented in SugarCRM Community Edition out of the box. Not sure about other editions though.<\/div>\n<h2>Modifying <em>Meetings<\/em> vardefs<\/h2>\n<div>Here is what I learned from the source code. Out of the box version of the <code>vardefs.php<\/code>\u00a0file for <em>Meetings<\/em> module contains the following declaration for <code>users <\/code>field:<\/div>\n<pre class=\"brush: php; title: ; notranslate\" title=\"\">\r\n...\r\n'users' =&gt;\r\n  array (\r\n  \t'name' =&gt; 'users',\r\n    'type' =&gt; 'link',\r\n    'relationship' =&gt; 'meetings_users',\r\n    'source'=&gt;'non-db',\r\n\t\t'vname'=&gt;'LBL_USERS',\r\n  ),\r\n...\r\n<\/pre>\n<div>In SugarCRM terms the above declares an attribute of the <em>Meeting<\/em> bean, which is an array of links to <em>User<\/em> beans. This array is backed by <code>meetings_users<\/code>\u00a0relationship. But this fragment doesn&#8217;t declare any additional attributes on this link (relationship) and this is why modifying these attributes is not working.<\/div>\n<div>So the mission is to adjust the above declaration in an update-safe way. Fortunately this works.<\/div>\n<div>First we need to create a new file in <code>SUGAR_ROOT\/custom\/Extension\/modules\/Meetings\/Ext\/Vardefs\/<\/code>. Please note that this location is very important. File name seems to be less important as explained in some tutorials on the Web. Let&#8217;s name it <code>vardefs.ext.php<\/code>:<\/div>\n<pre class=\"brush: php; title: ; notranslate\" title=\"\">\r\n...\r\n$dictionary&#x5B;'Meeting']&#x5B;'fields']&#x5B;'users']&#x5B;'rel_fields'] = array(\r\n\t'accept_status' =&gt; array(\r\n\t\t'type' =&gt; 'enum',\r\n\t\t'options' =&gt; 'dom_meeting_accept_status',\r\n\t),\r\n);\r\n...\r\n<\/pre>\n<div>Next step is to run\u00a0<em>Quick Repair and Rebuild<\/em> from SugarCRM administration page. If there are no syntax errors in the above file it will report\u00a0<span style=\"font-size: 12px;\"><strong><span style=\"color: #000000; line-height: 1.5em; font-family: arial, helvetica, sans-serif;\">Database tables are synced with vardefs.<\/span><\/strong><\/span><\/div>\n<div><strong><\/strong>With the above code we extended existing declaration for <em>Meetings<\/em> bean, adding one attribute to it, named <code>accept_status<\/code>. This will make existing code in <code>SUGAR_ROOT\/service\/core\/SoapHelperWebService.php<\/code>\u00a0to update relationship attribute.<\/div>\n<div>Final definition would probably look like this, this is what SugarCRM is doing during run-time:<\/div>\n<pre class=\"brush: php; title: ; notranslate\" title=\"\">\r\n...\r\n'users' =&gt;\r\n    array (\r\n        'name' =&gt; 'users',\r\n        'type' =&gt; 'link',\r\n        'relationship' =&gt; 'meetings_users',\r\n        'source'=&gt;'non-db',\r\n        'vname'=&gt;'LBL_USERS',\r\n        'rel_fields' =&gt; array(\r\n\t        'accept_status' =&gt; array(\r\n\t                'type' =&gt; 'enum',\r\n\t                'options' =&gt; 'dom_meeting_accept_status',\r\n\t         ),\r\n         ),\r\n    ),\r\n...\r\n<\/pre>\n<div>Just to emphasize, the above code snippet doesn&#8217;t exist in any file in the system, this is just my guess regarding how SugarCRM is merging its standard definition for <em>Meetings<\/em> bean with my custom extension.<\/div>\n<div>Please also note that in the above code I use <code>enum <\/code>type for the<code> accept_status <\/code>attribute and the values are defined in the drop-down list\u00a0<code>dom_meeting_accept_status<\/code>, which you can edit in SugarCRM administration interface. I observed also that it is possible to assign values, different from these in this drop-down list, but was too lazy to further investigate this.<\/div>\n<h2>Re-writing client side code<\/h2>\n<p>The rest was easy. I again used my perl module\u00a0Net::SugarCRM::Links (see &#8220;<a title=\"Create appointments (meetings) in SugarCRM via REST and perl\" href=\"http:\/\/www.bandidor.info\/wp\/create-appointments-meetings-in-sugarcrm-via-rest-and-perl\/\">Create appointments (meetings) in SugarCRM via REST and perl<\/a>&#8220;) and my code looked like this:<\/p>\n<pre class=\"brush: perl; title: ; notranslate\" title=\"\">\r\n...\r\nmy $user_link_entry = {\r\n\taccept_status =&gt; {\r\n\t\t\t'name' =&gt; 'accept_status',\r\n\t\t\t'value' =&gt; 'accept'\r\n\t}\r\n};\r\n\r\n$out = $s-&gt;create_module_links('Meetings',$meeting_id,'users',&#x5B;$user_id],$user_link_entry);\r\n...\r\n<\/pre>\n<div>Note that the <code>accept_status<\/code>\u00a0attribute has to be constructed in a bit complex way, as a name-value pair. I don&#8217;t know if there is any explanation for this, in my case it worked and I was satisfied.<\/div>\n<div>\n<h1><strong>Discussion<\/strong><\/h1>\n<\/div>\n<div>And below is the result:<\/div>\n<div><a href=\"http:\/\/www.bandidor.info\/wp\/wp-content\/uploads\/2013\/11\/ScreenShot050_2.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-279\" alt=\"ScreenShot050_2\" src=\"http:\/\/www.bandidor.info\/wp\/wp-content\/uploads\/2013\/11\/ScreenShot050_2.png\" width=\"639\" height=\"234\" srcset=\"https:\/\/bandidor.info\/wp\/wp-content\/uploads\/2013\/11\/ScreenShot050_2.png 639w, https:\/\/bandidor.info\/wp\/wp-content\/uploads\/2013\/11\/ScreenShot050_2-300x109.png 300w, https:\/\/bandidor.info\/wp\/wp-content\/uploads\/2013\/11\/ScreenShot050_2-250x91.png 250w, https:\/\/bandidor.info\/wp\/wp-content\/uploads\/2013\/11\/ScreenShot050_2-150x54.png 150w\" sizes=\"auto, (max-width: 639px) 100vw, 639px\" \/><\/a><\/div>\n<p>Now my appointments are created via Web service and they are already accepted by myself. I implemented this only for <code>accept_status<\/code>\u00a0attribute, but one could also do it for <code>required <\/code>attribute and also for appointment participants (Contacts).<\/p>\n<div>I have also checked other SugarCRM beans and just very few of them are making use of this <em>vardef<\/em> declaration for <code>rel_fields<\/code>. So, to modify any other many-to-many relationships with attributes <em>vardef<\/em> customisation will be needed as well.<\/div>\n<h1><strong>Caveats<\/strong><\/h1>\n<div>I must admit that I haven&#8217;t consulted any SugarCRM developer nor have I posted this to SugarCRM forums for discussion and probably the solution proposed here is not the best one. But this is the solution, which works for me and solves my particular case. So any suggestions to do it differently are welcome.<\/div>\n<div>As to my plans for the future &#8211; I&#8217;ll continue blogging about my experience with SugarCRM customization, <strong>so stay tuned!<\/strong><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Subject In my previous article &#8220;Create appointments (meetings) in SugarCRM via REST and perl&#8221; I described how I created appointments using Web services. This worked, but with one major glitch &#8211; solution provided didn&#8217;t put appointment status in accepted for the appointment owner and I was determined to solve this. So, read on. Challenge An&#8230;<\/p>\n","protected":false},"author":1,"featured_media":291,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[12],"tags":[14,15,25],"class_list":["post-266","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sugarcrm","tag-perl","tag-rest","tag-sugarcrm"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/bandidor.info\/wp\/wp-content\/uploads\/2013\/11\/IMG_0276-e1383762124935.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2EszU-4i","_links":{"self":[{"href":"https:\/\/www.bandidor.info\/wp\/index.php?rest_route=\/wp\/v2\/posts\/266","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.bandidor.info\/wp\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bandidor.info\/wp\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bandidor.info\/wp\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bandidor.info\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=266"}],"version-history":[{"count":18,"href":"https:\/\/www.bandidor.info\/wp\/index.php?rest_route=\/wp\/v2\/posts\/266\/revisions"}],"predecessor-version":[{"id":290,"href":"https:\/\/www.bandidor.info\/wp\/index.php?rest_route=\/wp\/v2\/posts\/266\/revisions\/290"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.bandidor.info\/wp\/index.php?rest_route=\/wp\/v2\/media\/291"}],"wp:attachment":[{"href":"https:\/\/www.bandidor.info\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=266"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bandidor.info\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=266"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bandidor.info\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=266"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}