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

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!

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