function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Matthew Hayes 46Matthew Hayes 46 

Help with a formula:

I am getting the below error on a formula and not sure where the error is.  Everything looks right to me, I am a admin not a developer.

User-added image
Best Answer chosen by Matthew Hayes 46
Andrew GAndrew G
ok,
first i note that you have an extra '+' at the end of the formula, try removing that.


After that, try troubleshoot as below

1. cut and paste what you have into notepad.
2. take the first line only and paste it into the formula.  Try and save. 
3.  if error, review the format to ensure it matches the IF fomat i described earlier. 
IF(TEXT(Somefield__c) = 'Needed', 'SomefieldName__c' + br(),  '' )
4. if error continue, try changing format or contents to troubleshoot. e.g. 
IF(
  TEXT(Somefield__c) = 'Needed',
  'Some field' ,
  'nothing found'
)
5. once a single line is resolved, and one extra line in same format 
IF(TEXT(Somefield__c) = 'Needed', 'SomefieldName__c' + br(),  '') +
IF(TEXT(Someotherfield__c) = 'Needed',  'Someotherfield__c'+br(), '')

Once two lines work, add the extras.  

Regards

ANdrew
 

All Answers

VinayVinay (Salesforce Developers) 
Hi Mathew,

Why are you using multiple if conditions?

Instead try to use OR condition something like below.
  
IF(

OR(
Field_X__c > (0.25*Field_Y__c)+Field_Y__c,
Field_X__c< Field_Y__c,
Field_X__c = 0,
Field_X__c = 0
),
“Yes”,
“No”
)

https://salesforce.stackexchange.com/questions/20848/how-to-use-or-within-a-if-statement-in-formula-field

Thanks,
Vinay Kumar
Matthew Hayes 46Matthew Hayes 46
Sorry Vinay, I am not following.  Again I am not a developer.  
What we are trying to do is create a formula field that will list fields by name that have a specific shared value.  All the fields have the same picklist value set.  We need to group by this list for a report for the CEO.  I am trying to avoid creating a new object because we have too much other work at the moment. We just want to group fields together by name in a formula field that have the same picklist value.  I will need the same formula for 2 other fields.  Does that make sense?
Andrew GAndrew G

If this field is purely for a report, why not just build a computed field in the report?  Think about the overhead.  If it's purely for a report, don't have the calculation of the computed field happening every time you open the record.  Have it just when the report is run. 

And to be honest, why does the CEO really need that level of detail ?  The CEO should be presented with reports that give overall numbers and status, not the individual long break down of various fields into a long and ugly text field about an individual account, because he isn't going to read it.  And to be honest, showing the API names of the fields in a concatenated list in a report is just wrong.

Reports are about showing data quickly and easily to read.  Think about how that field will look on a report of 1000 accounts.  And then to group by it????? there is going to be no clarity of detail.  If we think about the possible options, i believe (and i'm no mathematician) that the number of permutations would be 15!  Thats factorial 15 (or 1x2x3x4...x14x15) or 1.3x10^12

I would more likely look to give him a report of all accounts and then do a % of how many are "Why Pet Insurance Now" etc.

Ask the hard questions.  What is he really wanting to see?   Is it an ugly list of API field names or is he wanting to know how many of his accounts need  a "NPS Strategy"  versus how many want a "Paper Solution"?  What is the problem that he is trying to identify or solve?

but   (sigh) if you really want to keep this ugly formula, remember that an IF statement takes 3 arguments, IF(logic test, True result, false result).

And you say "picklist"?   then we need to factor in that with either ISPICKVAL or TEXT

so
 

IF(TEXT(Somefield__c) = 'Needed', 'SomefieldName__c' + br(),  '') +
IF(TEXT(Someotherfield__c) = 'Needed',  'Someotherfield__c'+br(), '') +
...... and so on
note the use of commas and the identification of the 3 parts to the IF statement.  and the use of TEXT around the picklist field.

Note also, that formula are limited to 5000 bytes (compiled size) which doesn't correlate directly to the number of characters in the formula itself.  But building formula fields like this start to take it to the limit.

best of luck
Andrew

p.s. remember that sometimes, as the custodian of the CRM, we need to guide the users to the best use of the tool.
 
Matthew Hayes 46Matthew Hayes 46
Andrew,
I 100% agree with everything you said.  What I failed to say was that is we are a small start up and this report for the CEO is used to have them help with sales.  There will never be more than 30/40 prospects on this list, and our CEO does get into the weeds on this particular project.  I am trying to get it to work, but continue to get syntex errors:

User-added image
Andrew GAndrew G
ok,
first i note that you have an extra '+' at the end of the formula, try removing that.


After that, try troubleshoot as below

1. cut and paste what you have into notepad.
2. take the first line only and paste it into the formula.  Try and save. 
3.  if error, review the format to ensure it matches the IF fomat i described earlier. 
IF(TEXT(Somefield__c) = 'Needed', 'SomefieldName__c' + br(),  '' )
4. if error continue, try changing format or contents to troubleshoot. e.g. 
IF(
  TEXT(Somefield__c) = 'Needed',
  'Some field' ,
  'nothing found'
)
5. once a single line is resolved, and one extra line in same format 
IF(TEXT(Somefield__c) = 'Needed', 'SomefieldName__c' + br(),  '') +
IF(TEXT(Someotherfield__c) = 'Needed',  'Someotherfield__c'+br(), '')

Once two lines work, add the extras.  

Regards

ANdrew
 
This was selected as the best answer