Advanced field formula to name Opportunities associated with Person Accounts. - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Julien CampJulien Camp 

Advanced field formula to name Opportunities associated with Person Accounts.

Hello,

I have standardized our Opportunity naming convention via a Workflow Rule and  (Formula) Field Update, which works great with regular Accounts:

IF (RecordType.Name  = 'Safari New',
TEXT(YEAR (CloseDate)) & "  -  " & Account.Name & "  -  " & TEXT (Product_Type__c) & "  -  " & TEXT(Type)  & "  -  " & TEXT (Number_of_Users__c) & " Users","")

However, this does not work for Person Accounts - Account Name does not populate.

I'm trying to add to the formula field to account for Opportunites associated with Person Accounts but am having trouble writing the formula field syntax. So far I have:

IF(Account.PersonContact.RecordTypeId = '0120000000099Lo',
&&(AND(RecordType.Name  = 'Safari New',
TEXT(YEAR (CloseDate)) & " - " &  Account.PersonContact.Account.Name & " - " & TEXT (Product_Type__c) & " - " & TEXT(Type)  & " - " & TEXT (Number_of_Users__c) & " Users",""))

... but this does not pass the syntax check. I'm convinced it's something relatively simple, so was hoping for some insight as to what Function or Operator I'm missing or have imput incorrectly.

Thanks so much!

Best,
Julien
Best Answer chosen by Julien Camp
Julien CampJulien Camp
First of all, thank you VERY MUCH Mayank for your contiued help and experimentation; it was really generous of you. Cheers and gratitude!

The good news is that I found a solution. It lies in singling out the the non-Person Account related Opps., first in the formula; then setting the Person Account related Opps. logic to pull the FirstName and LastName.

Final formula that works and has passed all tests:

IF( Account.IsPersonAccount = False, 

IF (RecordTypeId = '0120000000097QC', 
TEXT(YEAR (CloseDate)) & " - " & Account.Name & " - " & TEXT (Product_Type__c) & " - " & TEXT(Type) & " - " & TEXT (Number_of_Users__c) & " Users","")& 

IF(RecordTypeId = '0120000000097QH', 
TEXT(YEAR (CloseDate)) & " - " & Account.Name & " - " & TEXT (Product_Type__c) & " - " & TEXT(Type) & " - " & TEXT (Number_of_Users__c) & " Users","")& 

IF(RecordTypeId = '0120000000097QM', 
TEXT(YEAR (CloseDate)) & " - " & Account.Name & " - " & TEXT (Product_Type__c) & " - " & TEXT(Type) & " - " & TEXT ( Existing_Users__c ) & "/" &( TEXT(Number_of_Users__c ) & " Users"),"") , 



IF (RecordTypeId = '0120000000097QC', 
TEXT(YEAR (CloseDate)) & " - " & Account.FirstName & " " & Account.LastName & " - " & TEXT (Product_Type__c) & " - " & TEXT(Type) & " - " & TEXT (Number_of_Users__c) & " Users","")& 

IF(RecordTypeId = '0120000000097QH', 
TEXT(YEAR (CloseDate)) & " - " & Account.FirstName & " " & Account.LastName & " - " & TEXT (Product_Type__c) & " - " & TEXT(Type) & " - " & TEXT (Number_of_Users__c) & " Users","")& 

IF(RecordTypeId = '0120000000097QM', 
TEXT(YEAR (CloseDate)) & " - " & Account.FirstName & " " & Account.LastName & " - " & TEXT (Product_Type__c) & " - " & TEXT(Type) & " - " & TEXT ( Existing_Users__c ) & "/" &( TEXT(Number_of_Users__c ) & " Users"),"") 

)

All Answers

Mayank SrivastavaMayank Srivastava
Julien,
Did you insert all field names using the Insert Field button on the formula editor?

Assuming all the referenced field names above are correct, change your formula to be this:
IF(
     AND(     
            Account.PersonContact.RecordTypeId = '0120000000099Lo',
            RecordType.Name  = 'Safari New'
        ), TEXT(YEAR (CloseDate)) & " - " &  Account.PersonContact.Account.Name & " - " & TEXT (Product_Type__c) & " - " & TEXT(Type)  & " - " & TEXT (Number_of_Users__c) & " Users",  NULL
)

Note: I am not very familiar with Person Accounts so not sure if the above referenced fields make sense or not. I siply corrected the syntax. 
Julien CampJulien Camp
Yes, both formulas were built using the Insert Field button; the first one above is working fine - it's just the Person Account piece that isn't working. Thanks!
Julien CampJulien Camp
New errror message on Syntax check:

"You have reached the maximum number of 10 object references on OpportunityYou must remove at least 2 relationship(s) in order to save this Workflow Field Update"

Yes - Person Accounts were the worst idea ever. Thanks for your help!
Mayank SrivastavaMayank Srivastava
Oh well. That complicates things. You have a couple of things to do here:
  1. Conscientiously go through the references being made in your formulas on the Opportunity object. Now these can be workflow rules, validation rules, formula fields, process builder formulas etc. I believe Salesforce gives you a list of all places where the references are being made.
Here's a nice writeup from Shannon Hale:
I know that's not the response you're looking for right now, but here's some information that might help you reduce your object references. Even if you request a limit increase to address your immediate needs, you may wish to consider these as longer term solutions.

The object references (aka "spanning relationship") limit is based on the number of unique relationships referenced in formulas on an object: formula fields, workflow rules, validation rules, assignment rules, escalation rules, and auto-response rules. It doesn't include lookup filters, and it's not related to lookup fields. If you click the "Show references" link in the error message, it shows a list of the components that are contributing to the count.

Each relationship traversal is one unique relationship. For example, from the Case object, Parent.CaseNumber would be one unique relationship (Case-Case). Account.CreatedBy.LastName would be two unique relationships (Case-Account-User). Account.LastModifiedBy.LastName on the same Case object would be one additional traversal (the relationship to Account already exists). Global merge variables (e.g. $User.ProfileID) don't count towards the limit.

The alternative to spanning relationships is to use Apex or VIsualforce, which are tooled to handle more complexity than the formula engine is. Start by looking at the "Show References" list and see if you have validation rules you can move to a trigger. Or consider if there are other ways to expose data in formula fields -- for example, using the detail hover on a lookup field for information 1-2 levels away, if you want to see information on the page layout but don't need to report on it.


https://success.salesforce.com/ideaView?id=08730000000gKsbAAE

http://salesforce.stackexchange.com/questions/49539/you-have-reached-the-maximum-number-of-10-object-references-on-case


              2. After you have evaluated the existing references and removed a few of them, check if you can now build your formula. If you still get that error, log a case with Salesforce support and ask them to increase that limit to 15.

One very common thing that I have noticed:

On the Opportunity object, where-ever you see something like the following:

Account.Id
Replace it with:
AccountId

That alone would save you 1 reference.

Another tip: Get rid of RecordType.DeveloperName reference if you have it anywhere since that uses up two references.


Do a thorough analysis of what you can clean up in terms of references because it will make your life easier in the long run. Otehr wise every time you create a new formula on the Opportunity object, you are going to run into this.

Julien CampJulien Camp
Okay, have updated the formula reducing number of traverse references. It passes the syntax check now but still doesn't populate the Opps.' names with Person Account Account Name unfortunately:

IF (RecordTypeId  = '0120000000097QC',
TEXT(YEAR (CloseDate)) & " - " & Account.Name & " - " & TEXT (Product_Type__c) & " - " & TEXT(Type)  & " - " & TEXT (Number_of_Users__c) & " Users","")&

IF(RecordTypeId  = '0120000000097QH',
TEXT(YEAR (CloseDate)) & " - " & Account.Name & " - " & TEXT (Product_Type__c) & " - " & TEXT(Type)  & " - " & TEXT (Number_of_Users__c) & " Users","")&

IF(RecordTypeId  = '0120000000097QM',
TEXT(YEAR (CloseDate)) & " - " & Account.Name & " - " & TEXT (Product_Type__c) & " - " & TEXT(Type)  & " - " & TEXT ( Existing_Users__c ) & "/" &( TEXT(Number_of_Users__c ) & " Users"),"")&

IF( 
AND( 
Account.PersonContact.RecordTypeId = '0120000000099Lo', 
RecordTypeId = '0120000000097QC' 
), TEXT(YEAR (CloseDate)) & " - " &  Account.PersonContact.LastName  & " - " & TEXT (Product_Type__c) & " - " & TEXT(Type) & " - " & TEXT (Number_of_Users__c) & " Users","")& 

IF( 
AND( 
Account.PersonContact.RecordTypeId = '0120000000099Lo', 
RecordTypeId = '0120000000097QM' 
), TEXT(YEAR (CloseDate)) & " - " & Account.PersonContact.LastName & " - " & TEXT (Product_Type__c) & " - " & TEXT(Type) & " - " & TEXT (Number_of_Users__c) & " Users","")& 

IF( 
AND( 
Account.PersonContact.RecordTypeId = '0120000000099Lo', 
RecordTypeId = '0120000000097QH&' 
), TEXT(YEAR (CloseDate)) & " - " &  Account.PersonContact.LastName  & " - " & TEXT (Product_Type__c) & " - " & TEXT(Type) & " - " & TEXT (Number_of_Users__c) & " Users",NULL)
Mayank SrivastavaMayank Srivastava
There were syntax errors in your formula. Change the formula to be this:
IF ( 
    RecordTypeId  = '0120000000097QC',
    TEXT(YEAR (CloseDate)) & " - " & Account.Name & " - " & TEXT (Product_Type__c) & " - " & TEXT(Type)  & " - " & TEXT (Number_of_Users__c) & " Users", 

IF(
     RecordTypeId  = '0120000000097QH',
TEXT(YEAR (CloseDate)) & " - " & Account.Name & " - " & TEXT (Product_Type__c) & " - " & TEXT(Type)  & " - " & TEXT (Number_of_Users__c) & " Users",

IF(
      RecordTypeId  = '0120000000097QM',
TEXT(YEAR (CloseDate)) & " - " & Account.Name & " - " & TEXT (Product_Type__c) & " - " & TEXT(Type)  & " - " & TEXT ( Existing_Users__c ) & "/" &( TEXT(Number_of_Users__c ) & " Users"),

IF( 
AND( 
Account.PersonContact.RecordTypeId = '0120000000099Lo', 
RecordTypeId = '0120000000097QC' 
), TEXT(YEAR (CloseDate)) & " - " &  Account.PersonContact.LastName  & " - " & TEXT (Product_Type__c) & " - " & TEXT(Type) & " - " & TEXT (Number_of_Users__c) & " Users",

IF( 
AND( 
Account.PersonContact.RecordTypeId = '0120000000099Lo', 
RecordTypeId = '0120000000097QM' 
), TEXT(YEAR (CloseDate)) & " - " & Account.PersonContact.LastName & " - " & TEXT (Product_Type__c) & " - " & TEXT(Type) & " - " & TEXT (Number_of_Users__c) & " Users",

IF( 
AND( 
Account.PersonContact.RecordTypeId = '0120000000099Lo', 
RecordTypeId = '0120000000097QH' 
), TEXT(YEAR (CloseDate)) & " - " &  Account.PersonContact.LastName  & " - " & TEXT (Product_Type__c) & " - " & TEXT(Type) & " - " & TEXT (Number_of_Users__c) & " Users",    NULL))))))

Now give it a shot. Also I would replace the Id with RecordType.Name to make the formula more readable and avoid any confusion while testing.
Julien CampJulien Camp
Thanks again for your help. I can't replace Id with record name because that causes the too many relationships issue again.

My working code had passed the Syntax Check and it works for regular accounts. I see your changes and appreciate that but still not loading the Person Account Account Name. 
Julien CampJulien Camp
I also think part of the issue is that there doesn't seem to be a way to create a lookup relationship from the Opportunity to the Person Account object.
Mayank SrivastavaMayank Srivastava
Ah I get it. Yes that might be a possiblity. As one last shot (if you haven't tried this already), can you give the following a try?
IF( 
  AND( 
    Account.PersonContact.RecordTypeId = '0120000000099Lo', 
    RecordTypeId = '0120000000097QC' 
),  TEXT(YEAR (CloseDate)) & " - " &  Account.PersonContact.LastName  & " - " & TEXT (Product_Type__c) & " - " & TEXT(Type) & " - " & TEXT (Number_of_Users__c) & " Users", NULL)
Please note that the test record should satisfy both the statements within the AND block.

If that doesn't work either, then I believe we are out of ways to accomplish this declaratively and Apex would be the only option left.
Julien CampJulien Camp
First of all, thank you VERY MUCH Mayank for your contiued help and experimentation; it was really generous of you. Cheers and gratitude!

The good news is that I found a solution. It lies in singling out the the non-Person Account related Opps., first in the formula; then setting the Person Account related Opps. logic to pull the FirstName and LastName.

Final formula that works and has passed all tests:

IF( Account.IsPersonAccount = False, 

IF (RecordTypeId = '0120000000097QC', 
TEXT(YEAR (CloseDate)) & " - " & Account.Name & " - " & TEXT (Product_Type__c) & " - " & TEXT(Type) & " - " & TEXT (Number_of_Users__c) & " Users","")& 

IF(RecordTypeId = '0120000000097QH', 
TEXT(YEAR (CloseDate)) & " - " & Account.Name & " - " & TEXT (Product_Type__c) & " - " & TEXT(Type) & " - " & TEXT (Number_of_Users__c) & " Users","")& 

IF(RecordTypeId = '0120000000097QM', 
TEXT(YEAR (CloseDate)) & " - " & Account.Name & " - " & TEXT (Product_Type__c) & " - " & TEXT(Type) & " - " & TEXT ( Existing_Users__c ) & "/" &( TEXT(Number_of_Users__c ) & " Users"),"") , 



IF (RecordTypeId = '0120000000097QC', 
TEXT(YEAR (CloseDate)) & " - " & Account.FirstName & " " & Account.LastName & " - " & TEXT (Product_Type__c) & " - " & TEXT(Type) & " - " & TEXT (Number_of_Users__c) & " Users","")& 

IF(RecordTypeId = '0120000000097QH', 
TEXT(YEAR (CloseDate)) & " - " & Account.FirstName & " " & Account.LastName & " - " & TEXT (Product_Type__c) & " - " & TEXT(Type) & " - " & TEXT (Number_of_Users__c) & " Users","")& 

IF(RecordTypeId = '0120000000097QM', 
TEXT(YEAR (CloseDate)) & " - " & Account.FirstName & " " & Account.LastName & " - " & TEXT (Product_Type__c) & " - " & TEXT(Type) & " - " & TEXT ( Existing_Users__c ) & "/" &( TEXT(Number_of_Users__c ) & " Users"),"") 

)
This was selected as the best answer
Julien CampJulien Camp
Hopefully the above will help others who encounter this issue! Good luck!