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

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!