Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Christine MatthewsChristine Matthews 

if, else statements

I have a forumula field that currenlty works perfectly; however, I need to add to it and I'm not sure how.
I am using it in a bid scorecard or Go/NoGo process. Each question answered contributes to the final score and determines if it is a Go or NoGo. For this, is works perfectly. I'm using the formula:
IF ( Total_Points__c >0.85,IMAGE ("/servlet/servlet.FileDownload?file=0150y000002wOqE", "Green"), 
IF ( Total_Points__c >0.80,IMAGE ("/servlet/servlet.FileDownload?file=0150y000002wOq9", "Yellow"), 
IMAGE ("/servlet/servlet.FileDownload?file=0150y000002wOq4", "Red")))
For Example....
User-added image

However, it doesn't account for when the scorecard is incomplete or isn't completely filled out. For that, I've created another pic that shows "Incomplete".

If the formula shown above is based on scores, can I insert into the formula something that states that if any of the questions aren't filled out, then the "Go/NoGo Based on Bid Status %" would show the new "Incomplete" pic?
Best Answer chosen by Christine Matthews
bretondevbretondev
OK.
Picklist fields are a bit different.
So you have to nest your field in a TEXT() function before calling your ISBLANK() function.
That gives the follwong, you must do it for each field :

NOT(ISBLANK( Address_Pain__c )),

becomes :

NOT(ISBLANK(   TEXT(Address_Pain__c)   )),

for each field

All Answers

bretondevbretondev
I assume each of your questions is represented by a field.
If so, then I would create a new formula -checkbox-type-  field called "Completed" that checks if all questions have been answered or not.
If all answered it returns TRUE.
If not all it returns false.

The formula would look something like this :
  
AND (
       NOT(ISBLANK(Question1__c)),
       NOT(ISBLANK(Question2__c)),
       NOT(ISBLANK(Question3__c)),
       // on and on for each question
)

Then you can reference this formula in your original formula to check completeness and add new condition
Christine MatthewsChristine Matthews
ok...so I created a new formula -checkbox-type-  field called "Scorecard Complete". My formula looks like this: (it's long, I know)
AND (
NOT(ISBLANK( Address_Pain__c )),
NOT(ISBLANK( Already_Budgeted__c )),
NOT(ISBLANK( Award_Determination__c )),
NOT(ISBLANK( Bid_Margin__c )),
NOT(ISBLANK( Competition_Level__c )),
NOT(ISBLANK( Contact_with_GC__c )),
NOT(ISBLANK( Contact_with_Owner__c )),
NOT(ISBLANK( Delivery_Type__c )),
NOT(ISBLANK( Dollars_per_Man_Hour__c )),
NOT(ISBLANK( Early_Involvement__c )),
NOT(ISBLANK( Future_Work__c )),
NOT(ISBLANK( How_many_GC_s__c )),
NOT(ISBLANK( Major_Equipment__c )),
NOT(ISBLANK( Man_Power_Available__c )),
NOT(ISBLANK( Manufacturing_Opty_s__c )),
NOT(ISBLANK( Margins_for_this_Type_of_Work__c )),
NOT(ISBLANK( Negotiated_or_Bid__c )),
NOT(ISBLANK( Private_or_Public_Owner__c )),
NOT(ISBLANK( Project_Location__c )),
NOT(ISBLANK( Project_Size__c )),
NOT(ISBLANK( Safety_Advantage__c )),
NOT(ISBLANK( Strategic_or_NonStrategic_Client__c )),
NOT(ISBLANK( Vendor_Supplier_Advantage__c )),
)

I'm getting the error: Error: Syntax error. Found ')'

All of these are Picklist fields...does that matter?
bretondevbretondev
You must not put a comma at the end  in the last condition

NOT(ISBLANK( Vendor_Supplier_Advantage__c ))
Christine MatthewsChristine Matthews
Now it's giving me the following error: 
Error: Field Address_Pain__c is a picklist field. Picklist fields are only supported in certain functions.
bretondevbretondev
OK.
Picklist fields are a bit different.
So you have to nest your field in a TEXT() function before calling your ISBLANK() function.
That gives the follwong, you must do it for each field :

NOT(ISBLANK( Address_Pain__c )),

becomes :

NOT(ISBLANK(   TEXT(Address_Pain__c)   )),

for each field
This was selected as the best answer
Christine MatthewsChristine Matthews
Thank you so much! Perfect.