How to create a field that returns a specified value based on a related field in Sugar CRM?

Hi Ryan Siwinski,

Please review this article and let us know if it helps.

https://support.sugarcrm.com/Knowledge_Base/Studio_and_Module_Builder/Sugar_Logic/Sugar_Logic_Walkthrough_Related_Module… 

EDIT: Chris Raffle's answer below addresses the specific nature of the "based on another field value," which I recognize as a fundamental detail in the question. His is, in my opinion, the right answer. Using the dropdown in that way, you can essentially create a switch.

Hi Ryan,

This use case can be achieved in a few ways. One potential way to achieve this is through a custom dropdown list and Sugar Logic. 

I achieved your example by doing the following:

1. Go to Admin > Dropdown Editor > Add Dropdown
2. Create a dropdown list called joint_venture_office_list where the item name is the office number and the display label is the joint venture:
   
3. For my example, I created Office Number as an integer field (office_number_c), and Joint Venture as a text field (joint_venture_c). In the Joint Venture field in Studio, I selected the 'Calculated Value' option and used the following formula: 
   

   getDropdownValue("joint_venture_office_list",toString($office_number_c))

   This formula pulls the numberic value from the Office Number field, converts it to a string, and then finds the matching display value from the joint_venture_office_list dropdown list.

4. I then added both fields to the module, and confirmed the calculation works as expected:
   
   

This solution is not ideal under the following use cases:

• You have a large number of existing office numbers/joint ventures. Creating the dropdown list manually can be tedious in this scenario. If you have file-level access and are comfortable with basic coding syntax, this can be mitigated by defining the list in that method.
• The list you have to maintain is highly volatile. For the same reasons as the first bullet, maintaining this through the dropdown editor can be tedious.
• You are looking to access additional related information as part of the office number / joint venture relationship. 

The alternative to the above use cases would be to create a related module via Module Builder where you could store the office number, joint venture, and any other data points critical to that relationship. A custom module gives you access to the import utility where you can maintain the list easier than you can with a dropdown list.

I hope that helps!

Chris! Chris Raffle

I cant thank you enough for this assistance. You have significantly helped me and saved plenty of time due to my inexperience. I appreciate the help.

My list is 50-60 offices and will only increase 3-5 offices/quarter. I think that this is fairly manageable on my end at this time. Your third bullet point interests me. I created a Module for this information. I am working on pulling this data from my new module (Joint Venture Info) into the (RQ Orders) Module. Do you have any suggestions on how to do this? If you know of any articles please link them! 

Again, Thank you for your assistance! I truly appreciate it.

Best,

Ryan

Hi Ryan,

I'm glad I have been able to help! Please clarify your current design direction as I don't want to mislead you on the solution:

• Are you proceeding with the dropdown solution I presented and also using the Joint Venture Info module to store additional information? Or are you looking to store all info in the related module and have that surfaced in the RQ Orders module?
• How many fields do you want to pull from the Joint Venture Info module into RQ Orders?

Chris Raffle,

Thanks for the consideration. I went with the related module. I created the module and called in "JV Metrics". I wanted to be able pull in fields from this "JV Metrics" Module into my "RQ Orders" Module. I plan to pull in 5 fields. I don't necessarily need all of these fields to come in if there is a constraint.

Let me know if that is enough info!

THank you for your help.

-Ryan

Hi Ryan,

Thanks for additional details! Going the route of the related module gives you some added flexibility. It also has a few challenges you need to consider. My assumptions based on what you said so far are:

• A JV Metrics module record will contain both the reference of the Office Number and Joint Venture info. 
• The JV Metrics module would have a 1:many or 1:1 relationship with RQ Orders (1:many seems to be most likely). 
• Searching for JV Metrics records by Office Number is the most desirable workflow for your users

Please confirm whether those assumptions are accurate, and I will be happy to give further guidance on how I would proceed with this use case. 

Thanks!

Chris,

The Module will contain all JV Names and Office Numbers (Like Below):

The JV Metrics Module holds the following information. I uploaded this via excel. The relationship would be 1:Many. The most desirable JV Metric would probably be "Company Name" as it is the easiest one to remember for all employees.

Let me know if I answered your questions!

Best,

Ryan

Hi Ryan,

Thanks! Searching by 'Company Name' simplifies this use case. Here is my recommended approach:

1. Create and deploy the JV Metric module in Module Builder with all the desired fields and layout changes. Do not create any relationships with the module yet. (Note: I strongly recommend following the general guidelines presented in the Best Practices When Building Custom Modules KB article. If you have already deployed the module with the relationship, I recommend keeping that intact rather than trying to adhere to the guidelines with this article.)
2. Create the desired relationship(s) in Studio.
3. In Studio, create fields that will mirror the data from the related JV Metric record except for Company Name (since that value will be shown in the relationship field).
   1. The field type should be either integer, decimal, or text field depending on the type of data stored in its JV Metric counterpart.
   2. When creating each field, select the 'Calculated Value' checkbox and access the formula builder. Click the 'Related Field' button at the top right to select the JV Metric relationship and corresponding field from that module. You should see something like the following:
      
4. Once all the fields have been created, add them to the RQ Orders layouts as desired. You should be good to go!

For your stated use case of bringing over ~5 fields, this solution should be performant and satisfy your use case. The following performance issues may occur with this solution:

• Saving JV Metric Records - Individual JV Metric records are related to an exorbitant number (100s) of RQ Orders and JV Metric records are updated frequently. Changes to a JV Metric record where the field is mirrored in RQ Orders will cause all RQ Orders to be automatically updated with that new value. That behavior can be changed so that an explicit save of the RQ Order is required to update any related fields from the JV Metric record as described in the Introduction to Calculated Fields KB article.
• Saving RQ Order Records - If you have a large number of calculated fields (~50+) depending on 1 or more related modules, then saving these records would take more time to complete. The typical solution in this scenario is to offload the real-time processing incurred with calculated fields to a custom-developed logic hook. 

Let me know if you have any more questions!

Chris,

Again, thank you for all of the assistance with this topic. You have been nothing but extremely helpful with this.

I followed the progression you posted and was able to create the fields. The issue is that the field was not populating with the data in the RQ Orders Module. On the plus side the field was available in the module (just no data). 

My next questions is the following:

1. Do I need a unique Identifier in my JV Metric Module that will link up with the RQ Orders?

2. The company name field is not available in the relationship. How do I access this field to be able to be used.

Thanks for all of the help!!

Best,

Ryan

Hi Ryan,

The JV Metric and RQ Orders modules are linked by the relationship you create in Studio/Module Builder, so there should be no need create a unique identified in JV Metric. As for the Company Name field, that is a value that should be displayed automatically upon establishing the relationship on the RQ Order record. When you create the 1:many relationship from JV Metric to RQ Orders, Sugar should have placed a relate field on the RQ Orders record view layout. That is the field you use to select the corresponding company name, and upon selection, should trigger the other calculated fields to populate. If that is not occurring, please provide a screenshot of the following from Studio:

1. JV Metric field listing
2. JV Metric relationship listing
3. Formula builder for one of the calculated fields in RQ Orders