Pages

Saturday, March 17, 2012

SugarCRM Cookbook: Adding Related Records

Lets jump right into this one and begin by looking at a code snippet:

$rel_name = 'contacts';
$record_id = 'some_id_value';


$bean->load_relationship($rel_name);
$bean->$rel_name->add($record_id);

So what does it do?

Assuming we execute the above code within the scope of a before_save logic hook for Opportunities, the above code would add a linked contact to the opportunity being saved. 

While that example may have limited applications, the snippet of code does not. It can be reused within any custom PHP code you are writing for SugarCRM to link records together, all via the SugarCRM framework.

The advantage of this approach is that it is applicable to any module. In addition, and perhaps more importantly, it is upgrade-safe and eliminates the need for us to know about relationship tables, linking fields or any of the finer details that SugarCRM relies upon to establish relationships between records.

In order to see the power of this little snippet, we need to dissect it line-by-line and expand scope of our example.


Here is our first line:


$rel_name = 'contacts'; 

This line simply contains a variable that has been assigned the string value of: contacts. This may seem rather simple, but it is the key to the process. 

As you might already know, every relationship in SugarCRM has a unique name, for example, opportunities_contactsThe key to making this code work is knowing the name of the relationship we wish to affect. 

In our example, we are linking a contact to an opportunity during the save process. It happens to be that the name of the Opportunity/Contact relationship SugarCRM utilizes for this process is simply: contacts (it does not use the opportunities_ prefix). That is our key to working with contacts that are related to a specific opportunity.

Our next step is to provide the ID value of the record we wish to link it to, in this case, the ID of the contact entry. Remember, our base is the opportunity record, so we need the ID of the other record, not that of the opportunity.  We are storing this value in variable defined in this line of code:

$record_id = 'some_id_value'; 

The manner in which you come about getting that value is irrelevant to the process. It could be the result of an SQL query that was executed earlier in the code, hard coded into the process or any other means. 

Next we find this line:

$bean->load_relationship($rel_name); 

There are couple of noteworthy points relating to this line. First, the usage of $bean. Because our code is being executed within the scope of a logic hook, it is possible for us to reference the current Opportunity record by simply using $bean. There is no need for us to actually load the Opportunity class or instantiate an instance of it. 

Lastly, we are calling the load_relationship() method for that Opportunity record. Notice we are passing the string value of contacts as the parameter (via the $rel_name variable). This essentially connects us to the area of the SugarCRM framework that allows us to manipulate which contacts are linked to the current opportunity.

Note: I have not confirmed the matter, but I suspect the relationship names used in this context are case-sensitive.

Alright, so we are connected to the appropriate area, but how do we actually link a contact? That question is answered by the last line in our code snippet:

$bean->$rel_name->add($record_id); 

There is a very important point to notice in this line of code. Although the earlier line of code connected us to the relationship, in order to add or delete corresponding records from it, we must reference the relationship name again and it must match the name used for the load_relationship() method. Thus, the syntax to be used is:

$bean->[relationship_name]->add([record_id]);

To finalize our analysis, it is not necessary to call $bean->save() because that is automatically taken care of for us by SugarCRM.

For those of you wondering about the manner in which you can adapt this code so you can use it outside the context of logic hooks, do not despair. That too is rather simple and does not require much modification to the above. The key to executing it outside the context of a logic hook is to instantiate an instance of the corresponding class first, in this case, an Opportunity. 

Suppose we wanted to link the contact to an opportunity, but using an external PHP script.

To do that, we would do something like this:

require_once('modules/Opportunities/Opportunity.php');
$rel_name = 'contacts';
$contact_id = 'id_of_contact_to_link';
$oppty_id = 'id_of_oppty_to_affect';

$oppty = new Opportunity();
$oppty->retrieve($oppty_id); //Load a specific opportunity
$oppty->load_relationship($rel_name);
$oppty->$rel_name->add($contact_id); //Note use of $rel_name
$oppty->save() //Call save because it is not within a logic hook!

Wondering how to delete a relationship entry instead of adding one?

Simple, change 

$oppty->$rel_name->add($contact_id); 

to

$oppty->$rel_name->delete($oppty_id, $contact_to_unlink_id)

That is it!

53 comments:

  1. Angel-

    I am a very big fan of your blog. I am running the latest version of SugarPro 6.4 on a third party hosted site and it runs well. I am very well versed with php/mysql and am very happy with sugar. I recently entered the world of logic hook writing and recently implemented your "autoUserAssignment.php" as my hello world example it worked really well. I would like to write a logic hook that creates a note when an account custom field called "Current Engagement" changes. I would like a note created and in the subject line it would write the current value of "Current Engagement" before the change and also pickup the value of another account custom field called "account status" and write that in the subject line after the previous current engagement value and then setting the current status to "no status" Thanks so much. kevin@evepartners.com.

    ReplyDelete
    Replies
    1. will u please tell where is a database query for the listing of retrieving data from database on clicking on leads module.

      Delete
  2. @Kevin:

    Thanks for your feedback. I enjoy helping people and it always makes me happy to hear I've been of help.

    As for your desired logic hook, if memory serves me, $bean->fetched_row['field_name'] would give you the value of a given field at the time it was retrieved from the database, i.e. when you clicked Edit. As you likely already know, $bean->field_name would give you the value *after* you clicked save. Thus, I would assume that if you compare the values within the scope of a before_save logic hook, you would be able to tell if the value changed.

    As for creating the note, it would likely be something like this (where $bean represents the account record):

    require_once('modules/Notes/Note.php');
    $note = new Note();
    $note->name = $bean->account_custom_field; //the Subject line
    $note_id = $note->save();

    $bean->load_relationship('notes');
    $bean->notes->add($note_id);

    Give that a try.

    ReplyDelete
  3. Angel,

    In Sugar 6.4.2, you have to call $bean->save() after adding a many-to-many relationship call, due to the intended Sugar functionality changes.

    ReplyDelete
    Replies
    1. But for adding one-to-many relationship, we don't need to call $bean->save() in 6.4.2. I guess it's a bit inconsistency for Sugar Framework.

      Delete
    2. @siyang

      Hmmm. I'll have to give that a look. I am pretty sure I've used 642 before with this code.

      Thanks for the note.

      Delete
  4. Hi Angel.
    Great blog, really enjoying reading it, although I understand the code, not sure if I could do this sort of thing myself.
    I have a related question.
    We use an accounting package which uses a account number as the unique reference.
    When we create an opportunity we would like to either search for using that field rather than the name field, or alternatively autocopy this field to the opportunity as it is created.
    Does this require some php or can it be done within studio using the relate function.
    I have tried creating a field the the exact credentials, that didn't work.
    I also created a one to many relationship which showed the field in the portal but I still couldn't add it to the layout.

    Thanks again for the blog really helping me
    thx Darren

    ReplyDelete
    Replies
    1. I am not entirely sure I understand what you are trying to do.

      Copying the value into the opportunity at save time would be the easiest approach as it could be done through a logic hook, but how would you know which value to copy from the accounting system for any given opportunity? Would it be based off of the related account? If some, it would seem easier to copy that ID to the account record in Sugar and then copy it down to the opportunity whenever a new one is added for that account.

      Other methods would be more complicated as they would likely require a custom controller or other techniques as the accounting data is not actually in Sugar's database.

      Delete
  5. Hi Angel,
    I have many-to-many relationships between Contact and RE1_Properties(custom model), and another many-2-many relationship between Lead and RE1_Properties. When the Lead converted, I need to carry all the RE1_Properties to the Contact. The code below is not working.
    I can get the the RE1_Properties from Lead, but can not save it

    $source -> load_relationship($rel_name_source);
    $beans = $source -> $rel_name_source -> getBeans();
    $target->load_relationship($rel_name_target);
    foreach ($beans as $key) {
    $item = new RE1_Properties();
    $item->retrieve($key->id);
    $item->load_relationship($rel_name_targets);

    $item->$rel_name_target->add($target->id);
    $item->save();
    //$target->last_name .=$key->id;
    }
    Can you give me some idea?

    ReplyDelete
    Replies
    1. It seems you are trying to do something similar to what I describe here: http://cheleguanaco.blogspot.com/2012/03/logic-hooks-lead-history-transfer.html

      Give that a look as it should get you going.

      Delete
    2. This comment has been removed by the author.

      Delete
    3. It works finally. Thanks.
      By the way, the code you use"$contact->notes->add($note);"
      ,where is the add() function from? I mean in which folder/file and class? Thanks again!

      Delete
    4. I believe it is in data/Link.php or one of the files it includes.

      Delete
    5. Thanks, Angel.
      I appreciate it.

      Delete
  6. Great Blog Angel and thanks for the tips. I'm wanting to do this same thing but with a ProspectList but can't get it to work. Wondering if you woulnd't mind looking at my code snippet and giving me a few tips? I'm using SugarCRM 6.4.4 and php 5.3.10

    ------ Begin Code --------
    require_once('modules/ProspectLists/ProspectList.php');
    //$contact_id was set in a previous call and does returns a valid id
    $pl_id = '24053784-f8d3-22a4-5e99-4fc6a7d5159f'; //This is the ID from the table for the Target List (ProspectLists) table...I just copied it straight from the table.

    $pl = new ProspectList();
    $pl->set_prospect_relationship($pl_id, $contact_id, 'ProspectLists');
    $pl->save();
    ----- End Code ---------

    The above code does not create a relationship and gives me a error saying "Not A Valid Entry Point";


    I've also tried the following code to add a contact to the target list, but without any success...no errors thrown, and no errors in apache log file.

    ------ Begin Code --------
    $relationship = array(
    'module1' => 'Contacts',
    'module1_id' => "$contact_id",
    'module2' => 'ProspectLists',
    'module2_id' => "24053784-f8d3-22a4-5e99-4fc6a7d5159f"
    );
    ----- End Code ---------

    been googleing and trying to figure this out for 3 days now...Thanks for any help you are willing to give.

    Aaron

    ReplyDelete
    Replies
    1. Hi Aaron,

      Thanks for the feedback. Glad to hear you find the blog useful.

      You seem to have a combination of things going on here. The earlier code snippet you provide is using the SugarCRM framework, but the second snippet looks like you were attempting to use the SOAP API.

      Anyhow, something like this should work for you:

      require_once('modules/ProspectLists/ProspectList.php');
      $pl = new ProspectList();
      $pl->retrieve($pl_id);
      $pl->load_relationship('contacts');
      $pl->contacts->add($contact_id);
      $pl->save();

      Now, that should link the contact to your target list, but I doubt it'll solve your entry point issue, as that's related to the manner in which you are executing the code. That is a completely separate subject and neither of your snippets give me any insight as to how it is you are doing it. My only guess is that you might be trying to execute this code via an external PHP script.

      Delete
  7. i had made a one-to-many relationship through module builder what should be the relationship-name, should it be name of the relationship table ganerated

    ReplyDelete
    Replies
    1. It should show you the name in the "Relationships" section of Studio for the parent module.

      Delete
  8. How would you delete a relationship between two custom modules? For example, Contacts and Opportunities?

    ReplyDelete
    Replies
    1. There is a similar method one can use to remove linked records.

      The syntax is as follows:

      $bean->[relationship_name]->delete($parent_id, $child_id);

      Delete
    2. Hi Angel ,

      I'm having an problem deleting relationship..
      The code is running but doesn't update in database.

      code:
      $contact_bean = new Contact();
      $contact_bean->retrieve($user_id);
      $contact_bean->load_relationship('accounts');
      $prev_acc_id = 'acc prev id';

      $contact_bean->accounts->delete($user_id,$prev_acc_id);
      $contact_bean->save();

      I've tried this one as well: $contact_bean->accounts->delete($prev_acc_id, $user_id);

      IN database still delete=0 .. =(

      select * from accounts_contacts where contact_id = 'my contact id'

      Delete
    3. I suspect the problem might be related to the ID variables you are using....

      The line where you call $contact_bean->retrieve() references an ID value stored in the $user_id variable. Are you sure that is the appropriate variable? It should be the ID of a contact record, but the name implies it is the ID of a User record.

      Delete
    4. Hi angel

      the delete method is not working for me,

      How would you delete a many to many relationship?

      Delete
    5. I have confirmed that this does not work for accounts & contacts

      Delete
    6. Try just blanking out the account_id field on the contact object.

      Delete
  9. hi angel,
    i am creating relationship between calls and leads(lead as an invitee) from rest api v2 sugarce6.5.4 and i am successfull but issue is i am unable to set invitee status(field name accept_status) using rest call and also i am unable to get accept_status field value.
    i am using this code
    function createRelation (id, related_id, module_name, link_field, callback) {
    var params = {
    session: session_id,
    module_name: module_name,
    module_id: id,
    link_field_name: link_field,
    related_ids: [related_id],
    name_value_list: [{
    name: 'accept_status',
    value: 'accept'
    }],
    delete: 0
    };
    if (!callback) {
    var callback=function(response){
    if (response.created > 0) {
    console.log("relationship created");
    }
    if (response.deleted > 0) {
    console.log("relatinoship deleted");
    }
    if (response.failed > 0) {
    console.log("Error creating the relationship");
    }
    }
    }
    doRestCall('set_relationship', params, callback);
    }
    i have searched alot and unable to find any solution docs says that set_relationship method supports name_value_list param but in this case its just ignoring this param.
    any help will be appreciated

    ReplyDelete
    Replies
    1. It is possible that field is not accessible via the API. Use get_module_fields() to verify it is accessible.

      Delete
    2. According to sugarcrm documentation it should be possible: http://support.sugarcrm.com/02_Documentation/04_Sugar_Developer/Sugar_Developer_Guide_6.5/02_Application_Framework/Web_Services/05_Method_Calls/set_relationship/

      But I could also not get sugar to set the relationship attribute, perhaps sugar needs some extra definition of this variable, somewhere?

      Delete
  10. Hi Angel,

    Great blog, i've got a lot of hint on how using the sugarbean effectively with your advices.

    This one about how to add relationships too and it's great, but i tun into a memory leaks troubles.

    I'm on SugarcrmCE 6.5.15 actually (going to upgrade on 6.5.16 soon).

    I've got two modules : events and presence to events

    There is events, where people participate (cocktails, meetings etc...) and each participation is stored with what they have done (coming, drink, etc...) on presence to events

    I wish to add the list of people who have come and participate via targetlists generated by campaigns.

    So basically what i do is :

    - First : create event, easy it's on sugarcrm directly
    - Second : loop on the contacts on the target list then :
    - create presence to event for each contacts on the targetlist (newbean)
    - add relation to this presence with the contact (->add)
    - add relation to this presence with the event (->add)

    All works great until i use ->add for each contact and with event

    Globally my function looks like that :

    function creer_presence_evenement($id_liste,$id_evenement,$nom_evenement){

    //On récupère les infos de la liste de cibles que l'utilisateur a choisi + l'id & nom de l'evenement
    $liste_contacts = BeanFactory::getBean('ProspectLists',$id_liste);
    //On récupère la liste des contacts lié à la liste de cibles
    $liste_contacts->load_relationship('contacts');
    foreach($liste_contacts->contacts->get() as $contacts){
    echo "
    utilisation de la memoire 0: ".memory_get_peak_usage();
    //On crée une nouvelle présence aux événements (le module s'appelle hec4_intra_jury)
    $inscription_evenement = BeanFactory::newBean('hec4_intra_jury');
    //On enregistre la thématique de la présence aux événements (ou autre champ au choix si par exemple on sait que la personne participera/etc..)
    $inscription_evenement->description="Inscription : ".$nom_evenement."";
    $inscription_evenement->present=true;
    //On enregistre la nouvelle presence aux evenements
    $inscription_evenement->save();
    //On récupère l'id de l'objet nouvellement créé (içi la présence aux événements)
    $record_id = $inscription_evenement->id;
    $inscription_evenement->hec4_intra_jury_contacts->add($contacts);
    $inscription_evenement->hec4_intra_jury_hec4_evenements->add($id_evenement);
    }
    }

    Memory start to grow on the last two lines and never clean up until the loop is ended....

    I've tried gc_collect_cycles(), on each iteration, unset too but nothing seems to work...

    Did you run into a similar thing? did you have any advice?

    ReplyDelete
  11. This comment has been removed by the author.

    ReplyDelete
  12. Hi Angel,
    Great blog,it helped me a lot .
    i have one question , while manually creating a target in sugarcrm , i want to automatically link the target to an Account based on the domain of the email id field.
    for example , if i have entered bhaskar@ibm.com then it should link to Account named as IBM . Can you please suggest me how to achieve it.
    Thanks in advance.

    ReplyDelete
    Replies
    1. Thanks, glad I could help.

      I assume that you have already established a relationship between the Targets and Accounts module. Given that, you might want to consider creating an after_save logic hook that queries the email addresses table for the account record with the email address with that domain and then use methods similar to the above to link the records.

      Delete
  13. Hi Angel,
    How can I relate a call/note/task to a contact that is a converted lead?
    I'm using SugarCRM 6.5.16 CE.

    I've created relationships of Lead->Calls/Tasks/Notes
    Also I've created relationships of Contacts->Calls/Tasks/Notes

    Therefore there are sub-panels of Calls/tasks/notes in Leads and Contacts

    When I convert Lead to Contact I select "move activities to" Contacts.
    The call/note/task ownership moves to the Contact (I can verify by the "relate to" field")
    However the Notes/Calls/Tasks don't show in Contacts sub-panels.

    Thanx in advance.

    ReplyDelete
    Replies
    1. Relating to the above post:

      I did try this
      http://cheleguanaco.blogspot.in/2012/03/logic-hooks-lead-history-transfer.html
      But it does not have any correlation to my problem

      Delete
    2. Apologies, but I am not sure I understand the problem. Did you create a custom relationship between Contacts->Calls/Tasks/Notes? If so, why? If you did, that would likely explain the behavior as it is not necessary to create custom relationships and the "move activities" feature would rely on the default relationships, not the custom ones.

      Delete
  14. Thanx for the reply, but yes I did create a custom relationship i.e

    Leads->Calls
    Leads->Tasks
    Leads->Notes

    Contacts->Calls
    Contacts->Tasks
    Contacts->Notes

    I did that basically to get a sub-panel of Calls/Notes/Tasks when I 'Detail View' OR 'Edit' either Leads OR contacts.

    I'm guessing there's another way to achieve the same with the default 'relationship' fields?

    Thanx again for the assistance.

    ReplyDelete
  15. My bad, really sorry to be requesting such mundane requirements.

    I got all of them via Activities and History sub-panel. I had hidden them before and therefore was aware.

    Thanx for pointing me in the right direction.

    ReplyDelete
    Replies
    1. No problem. I am happy to hear you sorted it out.

      Delete
  16. Quick question,

    I have

    Activities -> Meeting, Calls, E-mail
    History -> Notes, Archive E-mail, Summary

    How can I move Notes to Activities Panel?

    ReplyDelete
  17. Hi Angel,
    I am new to SugarCRM development and I am trying to automatically save all the leads that are generated into one particular campaign. Is this possible using after_save hook? If so can you guide me to do this?

    ReplyDelete
    Replies
    1. How are you inserting the Leads into Sugar? You should be able to do this quite easily by simply populating the campaign_id field on the Lead record entry with the ID of the corresponding Campaign record.

      Delete
    2. I am inserting the leads through a lead-form in my website that was generated using the 'lead-form create' wizard. I am trying to save all these leads in a target-list automatically which can be then used in the campaign. I havent used any logic hooks previously, thus the complication.

      Delete
  18. Have you ever seen this error before? "Cannot access empty property" ... I am successfully getting the relationship to load but whenever I do the ->add() part I get that error...

    ReplyDelete
    Replies
    1. No, I am sorry, I've never come across that error. Are you able to interact with $bean otherwise?

      Delete
    2. I sure am.. here is my code

      $focus = new aCase();
      $newComm = new commHub;

      $newComm->description = "new description";
      $newComm->save();

      $focus->retrieve($parentID);
      $focus->load_relationship($relationshipName);

      $focus->$relationshipName->add($newComm->id);
      $focus->save();

      I can var_dump the $focus->$relationshipName and see it just fine... AND it was working but a couple days ago it stopped and of course no one here has done anything to the Cases module I've been told sooo... lol

      Delete
  19. How to load all relationships with load_relationships()
    $bean = BeanFactory::getBean("Accounts", $_POST['duplicate_with_detail']);
    $relationships = $bean->load_relationships();

    print_r(var_dump($relationships));
    die;
    it output's as null

    How do i load all relationship and get all relationship details

    Thanks


    ReplyDelete
    Replies
    1. You really shouldn't be doing that, but instead, load individual relationships as described in this post.

      See here for comments from the Sugar Dev team on the load_relationships() method:

      * Loads all attributes of type link.
      *
      * DO NOT CALL THIS FUNCTION IF YOU CAN AVOID IT. Please use load_relationship directly instead.
      *
      * Method searches the implmenting module's vardef file for attributes of type link, and for each attribute
      * create a similary named variable and load the relationship definition.
      *
      * @return Nothing
      *
      * Internal function, do not override.

      Delete
  20. Hi, this post has a lot of info in it. Was hoping for a little help. I am not familiar with php at all really. So that said, how could i adapt this code for module asset_Assets to contacts, i have the relationship many to many between the modules. The goal however, and the original may already do this, i just wanted to check before i try implementing, is i need the ability to change the relationship. Example if i create an asset and use relate feild "Contacts" to build relationship between asset and contact. If the asset changes owners i need to be able to go into the asset and simply edit the contact to delete the existing relationship and create the new one. It would turn a 3 step process into a one step process. Thanks so much for any help. using version 7.5.1

    ReplyDelete
  21. This comment has been removed by the author.

    ReplyDelete
  22. Hi Angel, this blog has been very important in my first Sugar developing steps, so i write here hoping to solve this problem some months later.

    In different functions (and different extended classes), i have this:

    // $relName is the name of a 1-1 relationship
    $this->bean->load_relationship($relName);
    $this->bean->{$relName}->delete($this->bean->id); // i've tried to add, as second parameter, the "$old_related->id", but the problem is not solved

    $this->bean->load_relationship($relName);
    $this->bean->{$relName}->add($new_related->id);

    $this->bean->load_relationship($relName);
    $GLOBALS['log']->info('Before save in the bean: '.$this->bean->[$relName]_name." - ".$this->bean->[$relName]custom_module_idb); // log #1
    $GLOBALS['log']->info('Before save relationship: '.array_values($this->bean->{$relName}->getBeans())[0]->id); // log #2
    $this->bean->save();
    $GLOBALS['log']->info('After save relationship: '.array_values($this->bean->{$relName}->getBeans())[0]->id); // log #3

    Without the last "$this->bean->save", all works fine (but i need that save because of other business logics in the after save logic hook).
    With the last save, in the logs i see:
    - log #1: name and id of the $old_related (relationship deleted at the first step)
    - log #2: id of the $new_related
    - log #3 id of the $old_related (new record in the relationship table; at the end of the 3 steps in the relationhip table i have 3 records, one with the $new_related (deleted) and two with the $old_related (one deleted at the first step and one not deleted, created by the last save)).

    With these instructions before the save, i can solve the problem:
    $this->bean->[$relName]_name="";
    $this->bean->[$relName]custom_module_idb="";
    but i'd like to find a better solution about updating relationships in bean.

    Thank you and best regards.

    ReplyDelete
    Replies
    1. The problem seems to be in the delete function, that deletes correctly the record in the relationship table, but does not update the bean.
      I solved the problem in this way:
      $this->bean->{$relName}->delete($this->bean->id);
      $this->bean->{$relName."_name"}="";
      $this->bean->{$relName."pd_prodotto_idb"}="";
      but i think it's not correct that in the bean id and name of the related bean are still there after the delete instruction.

      Delete
  23. Hi Angel,

    I am having trouble with SuiteCRM around getting it to automatically name the Opportunity Name from a few other fields in the Opportunity upon save. Can you please help me?

    ReplyDelete
    Replies
    1. Apologies, while I am familiar with Sugar Community Edition, upon which SuiteCRM is based, I've never actually used SuiteCRM.

      That being said, you need to elaborate on your problem. Unless SuiteCRM has a specific feature that allows for it, an automated naming mechanism is not a feature of Sugar CE. This means that it would have to be a customization, of which, there are multiple ways of accomplishing the above.

      What exactly is not working?

      Delete

Your comments, feedback and suggestions are welcome, but please refrain from using offensive language and/or berating others. Thank you in advance.