How to create a Related Field that automatically takes the value of another field and uses that to query the related Module?

For example, i have this table of data in Module 1:

Module 1

Main Field Related Field
example 1 example 1 (linked to item in related module)
example 2 example 2 (linked to item in related module)

Module 2

Main Field other other 
example 1 ex1 other ex1 other
example 2 ex2 other ex2 other


As you can maybe see, i am trying to describe what the title is saying. I have Data imported into Module 1, and want the Related Field in Module 1 to automatically link to Module 2, using the value of Module 1's Main Field. Is there a way to do this? I tried to import the data directly from Main Field into the Related Field but it was messy and i now have many duplicates.

  • Hi

    Some rules which are part of the core and default behavior of SugarCRM application:

    • A module which has a field related to another module has the link in the field <relationship>_name, for example module2_name.
    • The value displayed in the field module2_name is the name field itself in Module 2.

    I'm not sure you want to render in the relate field the value of a field other than the name itself of the Module 2.

    Can you kindly clarify it a little more?

    André Lopes
    Lampada Global
    Skype: andre.lampada
  • If I understand correctly, you are importing data from another system And, as you import records you also want to link them to existing records in another module.

    So, for example, let's say that Module 1 is a purchase order and the Main Field on the Purchase Order is the Account Name.
    For each Purchase Order that you import you want to look up the Account Name in the Accounts Module (Module 2) and have that linked in a Relate Field on the Purchase Order.

    Module 1

    Account Name (what you have) Related Field Account (what you want)
    Francesca a link to Account with name Francesca
    Keaton a link to the Account with name Keaton

    Module 2

    ID Account Name phone email
    1 Keaton 1234 some email
    2 Francesca 4567 some other email

    First of all you need to determine if your "Related Field" is the result of a "Relate Field" type or a "Relationship"

    https://support.sugarcrm.com/Knowledge_Base/Studio_and_Module_Builder/Introduction_to_Relationships_and_Relate_Fields/

    --

    If the "Related Field" in your Module 1 is a "Relate Field" then you will need to identify the name of the id in Module 1.

    To do this look in your cache/modules/Module1/Module1vardefs.php, this file contains ALL the vardefs for Module1 out of the box and custom in one place and is the easiest way to look things up.

    Find the definition for your "Related Field", it should be of type "relate".
    In the vardef you will find a field called 'id_name", this is the field that you need to populate with the ID of the record in Module 2 in order to link the module 2 record to module 1 record you are creating.

    So, in my example:

    Look in cache/modules/Module1/Module1vardefs.php for the Related Field Account in the vardefs for Module 1.
    Let's say it has: 'id_name' => 'account_id_c'

    Loop through the data you need to import, for each record use SugarQuery to find the corresponding id in Module2 based on the Account Name.

    Now you have the value you need to put in account_id_c and you can use the BeanFactory to insert the record into Module1 and include 'account_id_c as one of the bean's fields to link the two records.

    If you already imported all your Module1 records and just need to relate them to Module2 you can still go through the process above but instead of creating the beans you will be updating the account_id_c for each one.
    --

    If you are working with Relationships you will need to do this in two steps:

    Look in 
    cache/modules/Module1/Module1vardefs.php and find the definition of the link to Module2 (I find the easiest way is to search for the module name Module2 and make sure I have the right one if there is more than one). Identify the 'name' of the link.

    Loop through the data you need to import, for each record use SugarQuery to find the corresponding id in Module2 based on the Account Name. Store the $id of the record you need to link

    Then use the BeanFactory to insert the record into Module1. So you have $bean1
    And now that both beans exist and have been stored you can link the two:

    //Link Beans
      $link = 'the_name_of_the_link'; //identifies the link to bean2
      if($bean1->load_relationship($link)){
        $bean2->$link->add($bean2->id);
      }
    

    Here if you already imported all your Module1 records and just need to relate them to Module2 you're already partly done and you just need to loop through the existing records and add the relationships.

    --

    There may be some limitations to what you can do if you are working in a Cloud environment, in which case you may need to use APIs to insert/update records instead of working directly with beans. 

    The Dev Blog post here is a good place to start for that: Upsert as explained by "Upsert"

    I hope I interpreted your request correctly and have given you some ideas on where to start.

    Good luck,

    FrancescaS

  • Yeah, ideally the List view of Module 1 would just have one field that has the link to Module 2, since the Main Field of Module 1 would be the same text but without the link to module 2. Apologies if my wording is confusing!