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

Here is my situation:

I currently have the following setup in my record view on one of my modules (lets call it Estate_Orders for the sake of this post)

My Goal is to be able to make the "Joint Venture" field related to the "Office Number" field. Every record has a different office number and a corresponding Joint Venture name. These two fields are directly correlated. 

For example: Office Number: 841 is equal to Joint Venture: Real Estate Corp, LLC

My Issue is that I am unsure of how to create this formula in the Sugar Studio. Clearly I am very new at this and would appreciate any and all guidance on how to best implement this!!

Have a wonderful 2020

-Ryan

Parents
  • 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!

Reply Children
  • 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
  • Hi Chris,

    Here are the screenshots

    1. JV Metric Field Listing:

    2. JV Metric Relationship Listing

    3. 

    Unless I am clearly making a mistake in one of these screenshots; I would also like to note that I am not seeing the related "Company Name" field pulling into the RQ Order Module from the JV Data module.

    Thank you for your continued assistance throughout this issue resolution!!!

    Ryan

  • Thanks, Ryan! Everything looks setup properly on that end. In the RQ Orders record view, you should have a relationship field called 'Joint Venture Data Label' (unless you renamed the field) similar to the stock 'Account' field you would see in Contacts. What happens when you select a record from the 'Joint Venture Data Label' field? What value shows in the field, and do any of the calculated fields populate once a record is selected from that field?

  • AHH; yes I was not selecting the field to be populated. When I click into it and choose the desired JV the other fields are populating!! THANK YOU. I was just assuming that it would auto fill.

    That being said, considering that I have 150,000 files is there anyway that I can set it to auto fill all of the existing records based on a value that I currently have in the RQ Order?

    This is great stuff Chris, I really appreciate it.

    -Ryan

  • I'm glad to hear that is working as expected! With regards to updating the existing records, you cannot automatically trigger those records to populate based on another value as the calculated fields depend on the relationship to be established.

    One approach you can take is using the import and update functionality. Sugar limits the number of records you can export through the list view UI at 1,000, so you will need to use an alternative method to get all 150k records out of the RQ Orders module.

    The first option is to create a rows and columns report on the RQ Orders module with no filters. The columns to display in the report should be:

    • The record ID
    • The record name
    • Any other required fields on the module. This is necessary because importing requires you to map all required fields.
    • The field containing your JV reference (if it already isn't a required field)

    Once the report is generated, attempt to export the report. Please note that this may fail due to the number of records you need to export exceeding the server resources. If it does fail, you can either add filters to the report to generate smaller lists and export those (e.g. RQ Order name begins with 'A' -> export, then RQ Order name begins with 'B' -> export..... RQ Order name begins with 'Z' -> export), or if your instance is hosted in SugarCloud, raise a support case to see if they can provide you a CSV file of the records you need. The contents of the CSV need to include the same fields you added to the report. 

    Once you have the CSV file, add a column to the file named 'Joint Venture Data Labels', and use the reference field in your file to populate the new column with the appropriate JV company names. It is important that the names match exactly what you have stored in the Joint Venture Data Labels module or else the import will fail to establish the relationship. 

    Importing automatically chunks any file you upload into 1k record increments. While a 150k record file should work in theory, I recommend breaking down your main file into smaller files (~10k records each) to help mitigate potential server resource issues. The Import product documentation should cover the steps to update your records if you are unfamiliar with that process. When mapping fields, set your new column from the file to the 'Joint Data Venture Labels' option from the field list, and everything should be good to go.

    Good luck!

  • Hi Chris!

    I apologize for the late response to your latest post. I appreciate the help from you!! This has made our Sugar CRM instance much more reliable.

    Thank you for everything. If I can leave you a recommendation or written endorsement for your eagerness to help me I would be glad to do so.

    Best of luck with everything. I look forward to talking again in the future!

    -RS

  • Thanks, Ryan! I'm glad we were able to work towards an efficient solution and good luck to you as well!