Trying to figure out the best way to deal with multiple related fields in one record. I'm building Sugar for a law firm. In each Case record, they want to be able to see the Lawyer on the other side - which is a related field to Contacts, the Judge - which is also a related field, the Defendant - yet another related field to Contacts.
Yes, these Contacts are listed in the subpanel of the Case module, but they want to be able to see these primary contact's information on a tab in the Case record to avoid hunting through all the related Contacts in each Case - which can be extensive.
I'm trying to figure out a way to distinguish the difference between each related field to be able to pull the Judge's assistant's name and phone number into the Case record, as well as the Lawyer and their assistant's contact details when using the related($contacts,.....) formula.
The problem is, Sugar doesn't know which Contact to pull this data from when there are multiple contacts related to a record. I asked Sugar support for some insight and this feature is already documented in Enhancement Request 66796.
Sugar Support offered the following idea for a possible workaround: "If you have multiple related records and wish to pull a specific phone number field from one of the records, you would have to flag that record somehow. For example, if you placed a checkbox on the related contact that you want to use and named it 'Use this record' or something to flag it as the record to reference. Then in your formula you could use ifElse() to determine which related record to use. If user_this_record_c equals 1, then pull the related phone number field. I believe that might work for you but you would have to test it."
I haven't tried this yet, but am planning to. In the meantime, has anyone encountered this issue and thought of a better way to go about a solution?
Thank you for any insight! I greatly appreciate it.
