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
Swapnil Patne 20Swapnil Patne 20 

Need help with formula to calculate a amount value

Hi there,

I trying to write a formula which looks at 2 conditions - Blank value & Picklist values and then calculates the amount

Formula statement is - 
If Capped Days = 'Null'  And
Charge Rate Type = 'Daily' then Contract_value__c = WD__c * DM__c
OR
Charge Rate Type = 'Hourly' then Contract_value__c = WD__C * 8 * DM__c
OR
Charge Rate Type = 'Null' then Contract_value__c = WD__C * DM__c

If Capped Days = 'Not Null' And
Charge Rate Type = 'Daily' then Contract value = Capped_Days__c * DM__c
OR
Charge Rate Type = 'Hourly' then Contract value = Capped_Days__c * 8 * DM__c
OR
Charge Rate Type = 'Null' then Contract_value__c = Capped_Days__C * DM__c

I tried many times but failed to come up a working formula, please can someone help me with it? Any questions please post.

Thanks in advance. 

Swapnil
Shawn Reichner 29Shawn Reichner 29
Can you try...
If(
ISNULL(Capped_Days__c) && ISPICKVAL(Charge_Rate_Type__c, "Daily"),
Contract_value__c = WD__c * DM__c,
If(
ISNULL(Capped_Days__c) && ISPICKVAL(Charge_Rate_Type__c, "Hourly"),
Contract_value__c = WD__C * 8 * DM__c,
If(
ISNULL(Capped_Days__c) && ISPICKVAL(Charge_Rate_Type__c, ""),
Contract_value__c = WD__C * DM__c,
If(
!ISNULL(Capped_Days__c) && ISPICKVAL(Charge_Rate_Type__c, "Daily"),
Contract_value__c = Capped_Days__c * DM__c,
If(
!ISNULL(Capped_Days__c) && ISPICKVAL(Charge_Rate_Type__c, "Hourly"),
Contract_value__c = Capped_Days__c * 8 * DM__c,
If(
!ISNULL(Capped_Days__c) && ISPICKVAL(Charge_Rate_Type__c, ""),
Contract_value__c = Capped_Days__c * DM__c,
))))))


Please mark as best answer if this helps...

Shawn
Swapnil Patne 20Swapnil Patne 20
Hi Shawn, I tried and got this error- Compiled formula is too big to execute (6,140 characters). Maximum size is 5,000 characters
Swapnil Patne 20Swapnil Patne 20
 Hi shawn, I got this error- Compiled formula is too big to execute (6,140 characters). Maximum size is 5,000 characters
Shawn Reichner 29Shawn Reichner 29
Hmm...  Looks like the formula is too complex for a formula field.  I woudl now try to accomplish this via the Process builder and I am sure you will be successful there.  
Swapnil Patne 20Swapnil Patne 20
Will try, thanks Shawn!
Shawn Reichner 29Shawn Reichner 29
No problem, if the process works for you please select as best answer when you have a moment. If this does nto work, come back and we will look at another option. 
Malni Chandrasekaran 2Malni Chandrasekaran 2
Swapnil,
Please try,

CASE(1,
If(ISNULL(Capped_Days__c) && (ISPICKVAL(Charge_Rate_Type__c, "Daily" || ISPICKVAL(Charge_Rate_Type__c, "") ), 1, 0), Contract_value__c = WD__c * DM__c),
if(ISNULL(Capped_Days__c) && ISPICKVAL(Charge_Rate_Type__c, "Hourly"), 1, 0),  Contract_value__c = WD__C * 8 * DM__c,
If(!ISNULL(Capped_Days__c) && (ISPICKVAL(Charge_Rate_Type__c, "Daily")  || ISPICKVAL(Charge_Rate_Type__c, "")), 1, 0), Contract_value__c = Capped_Days__c * DM__c,
If(!ISNULL(Capped_Days__c) && ISPICKVAL(Charge_Rate_Type__c, "Hourly"), 1, 0),  Contract_value__c = Capped_Days__c * 8 * DM__c
)

Please mark it as solved if this solves your problem

,
Swapnil Patne 20Swapnil Patne 20
Hi, Malni I tried your formula but no luck.. I modified a bit 

IF 
( AND 
  (Capped_Days__c = null, 
     TEXT(Charge_Rate_Type__c)== 'Daily'),(Daily_Margin__c *  Working_Days__c) ,

IF( 
ISPICKVAL(Charge_Rate_Type__c, 'Hourly'), Duration_in_Hrs__c * Daily_Margin__c,

IF ( 
ISBLANK(TEXT(Charge_Rate_Type__c)),Daily_Margin__c * Working_Days__c,(Daily_Margin__c * 8 * Working_Days__c)
   )
  )
)

I get the following Error: Compiled formula is too big to execute (5,494 characters). Maximum size is 5,000 characters
Swapnil Patne 20Swapnil Patne 20
sorry here is the updated formula.. almost works but one missing condition.. Charge Rate Type = null and Capped Days = null then value = Working_Days__c * Daily_Margin__c  (how to include this condition in below formula) 

IF
( AND
  (TEXT(Charge_Rate_Type__c)== 'Daily', Capped_Days__c != null), (Daily_Margin__c * Capped_Days__c),
 
IF
( AND
  (TEXT(Charge_Rate_Type__c)== 'Daily', Capped_Days__c = null), (Daily_Margin__c * Working_Days__c ),
 
IF
( AND
  (TEXT(Charge_Rate_Type__c)== 'Hourly', Duration_in_Hrs__c != null), (Daily_Margin__c * Duration_in_Hrs__c),
 
IF (
ISBLANK(TEXT(Charge_Rate_Type__c)), Working_Days__c * Daily_Margin__c, Daily_Margin__c *  Capped_Days__c
    )
 
   )
  )
 )
 
Malni Chandrasekaran 2Malni Chandrasekaran 2
Swapnil,
You may combine the condition with OR || operator like,

(ISPICKVAL(Charge_Rate_Type__c, "Daily" || ISPICKVAL(Charge_Rate_Type__c, "")

Hope this helps!
Shamsi 110Shamsi 110
Similarly, add one more if condition at end  
IF
( AND
  (TEXT(Charge_Rate_Type__c)=null, Capped_Days__c = null), (Working_Days__c *Daily_Margin__c ),

Please mark it as solved if this solves your problem