+ Start a Discussion
venkat bojjavenkat bojja 

Explain the formula

Hi Team,
Please help me on below formula as each and every line with expaination.

IF( 
ISPICKVAL( Severity__c , "Critical")&& ISBLANK(Plan_Due_By_Date__c ), CASE( MOD(Today()-DATE(1900, 1, 7),7),0,Plan_Due_By_Date___c+2+10, 
1,Plan_Due_By_Date___c+4+10,  
2,Plan_Due_By_Date___c+4+10, 
3,Plan_Due_By_Date___c +4+10, 
4,Plan_Due_By_Date___c +4+10, 
5,Plan_Due_By_Date___c+4+10, 
Plan_Due_By_Date___c+3+10), 
IF( 
ISPICKVAL( Severity__c , "High")&&ISBLANK(Plan_Due_By_Date__c ), CASE( MOD(Today()-DATE(1900, 1, 7),7),0,Plan_Due_By_Date___c+2+10, 
1,Plan_Due_By_Date___c+4+10, 
2,Plan_Due_By_Date___c+4+10, 
3,Plan_Due_By_Date___c +4+10, 
4,Plan_Due_By_Date___c +4+10, 
5,Plan_Due_By_Date___c+4+10, 
Plan_Due_By_Date___c+3+10), 
IF( 
ISPICKVAL( Severity__c , "Medium")&&ISBLANK(Plan_Due_By_Date__c ), CASE( MOD(Today()-DATE(1900, 1, 7),7),0,Plan_Due_By_Date___c+6+20, 
1,Plan_Due_By_Date___c+8+20, 
2,Plan_Due_By_Date___c+8+20, 
3,Plan_Due_By_Date___c +8+20, 
4,Plan_Due_By_Date___c +8+20, 
5,Plan_Due_By_Date___c+8+20, 
Plan_Due_By_Date___c+7+20), 
IF( 
ISPICKVAL( Severity__c , "Low")&&ISBLANK(Plan_Due_By_Date__c ),CASE( MOD(Today()-DATE(1900, 1, 7),7),0,Plan_Due_By_Date___c+10+30, 
1,Plan_Due_By_Date___c+12+30, 
2,Plan_Due_By_Date___c+12+30, 
3,Plan_Due_By_Date___c+12+30, 
4,Plan_Due_By_Date___c+12+30, 
5,Plan_Due_By_Date___c+12+30, 
Plan_Due_By_Date___c+41),Plan_Due_By_Date__c 



)

Thanks in advance

Thanks,
Venkat.
Best Answer chosen by venkat bojja
Durai SaravananDurai Saravanan
Hi Venkat,

So, the values that they have given is based on the requirement or 'Business Logic' as they say.

That is not specific to code understanding. You have understood the code. 

But the values are based on who wrote this code and what requirement they had at that point of time. 

Again you can add any values you wish there. But if your requirement states that, on this case, Plan_Due_By_Date___c will get this value.
on another case, Plan_Due_By_Date___c will get another different values, so on and so forth

Any further queries or clarification on any other concepts of salesforce, fell free to mail me duraithemaster@gmail.com

Please mark this as 'Best Answer' if my reply was helpful. It will make it available for other as the proper solution.
                                             
Best Regards
Durairaj Saravanan

All Answers

Durai SaravananDurai Saravanan
Hi Venkat,

So, before you understand the code, let us see the fields in your code, 

You have
'Severity__c' as a picklist field
'Plan_Due_By_Date__c ' as a date field.

And now, let us see the functions/methods in your code

You have
ISPICKVAL
ISBLANK
MOD

And now, coming to the explaination, 

Your code had three if blocks, which specify, if the  'Severity__c' picklist field is having 'Critical' as value, and 'Plan_Due_By_Date__c' is blank, we must populate the 'Plan_Due_By_Date__c ' based on certain conditions.To handle that conditions, we use 'CASE' statement.

Now, MOD(Today()-DATE(1900, 1, 7),7) means "MODULUS OPERATOR" or "%", which gives the "Remainder" when we divide variable1 by variable2. 

here, "Today()-DATE(1900, 1, 7)" is the variable1 and "7" is variable 2,

variable1, returns some number. which is number of days which lies between, (1900, 1, 7) and Today. Today() is a function which returns current date.

variable 2 is 7

While performing MOD operator, we get a particular value, and here we use CASE statement, to handle the possible cases, i.e, 0,1,2,3,4,5, and default.

So, in case of "MOD(Today()-DATE(1900, 1, 7),7)" returning 0, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+2+10,"
in case of "MOD(Today()-DATE(1900, 1, 7),7)" returning 1, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+4+10"

So on.



Hope this helps you get some better picture.

Please mark this answer best, if this solves your purpose.
venkat bojjavenkat bojja
Hi Durai Saravanan,

Thanks for your quick responce and good explaination...

But i am unable to understand this area.

So, in case of "MOD(Today()-DATE(1900, 1, 7),7)" returning 0, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+2+10,"
in case of "MOD(Today()-DATE(1900, 1, 7),7)" returning 1, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+4+10"

Can you please brief me on how it returning 0 and how will get value "Plan_Due_By_Date___c+2+10," again returning 1  "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+4+10"
1)how it is 0 and how it is 2+10
1)how it is 1 and how it is 4+10

I am new to this kind of formulas

Thanks,
Venkat.
Durai SaravananDurai Saravanan
Alright, So what that means is

In simple terms, your code is ensuring the 'Plan_Due_By_Date___c' NOT to be blank.

If the picklist value of 'Severity__c' is 'Critical' and 'Plan_Due_By_Date___c' is blank., they want to fill 'Plan_Due_By_Date___c' with some value(Which I have explained below)
If the picklist value of 'Severity__c' is 'High' and 'Plan_Due_By_Date___c' is blank., they want to fill 'Plan_Due_By_Date___c' with some value.
If the picklist value of 'Severity__c' is 'Medium' and 'Plan_Due_By_Date___c' is blank., they want to fill 'Plan_Due_By_Date___c' with some value.
If the picklist value of 'Severity__c' is 'Low' and 'Plan_Due_By_Date___c' is blank., they want to fill 'Plan_Due_By_Date___c' with some value.

So what they do is, they use CASE statement, so that in every particular scenario, they assign 'Plan_Due_By_Date___c'  to a value.

CASE statement is similar to SWITCH, which you might have come across in other programming languages

So now, If the picklist value of 'Severity__c' is 'Critical' and 'Plan_Due_By_Date___c' is blank., they want to fill 'Plan_Due_By_Date___c' with :

In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 0, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+2+10,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 1, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+4+10,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 2, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+4+10,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 3, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+4+10,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 4, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+4+10,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 5, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+4+10,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 6, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+3+10,"

If the picklist value of 'Severity__c' is ' High' and 'Plan_Due_By_Date___c' is blank., they want to fill 'Plan_Due_By_Date___c' with :

In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 0, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+2+10,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 1, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+4+10,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 2, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+4+10,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 3, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+4+10,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 4, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+4+10,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 5, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+4+10,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 6, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+3+10,"

If the picklist value of 'Severity__c' is ' Medium' and 'Plan_Due_By_Date___c' is blank., they want to fill 'Plan_Due_By_Date___c' with :

In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 0, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+6+20,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 1, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+8+20,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 2, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+8+20,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 3, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+8+20,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 4, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+8+20,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 5, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+8+20,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 6, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+7+20,"

If the picklist value of 'Severity__c' is ' Low' and 'Plan_Due_By_Date___c' is blank., they want to fill 'Plan_Due_By_Date___c' with :

In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 0, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+10+30,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 1, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+12+30,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 2, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+12+30,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 3, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+12+30,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 4, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+12+30,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 5, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+12+30,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 6, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+41,"


Hope this helps you get the exact picture.

Please mark this as 'Best Answer' if my reply was helpful. It will make it available for other as the proper solution.
                                             
Best Regards
Durairaj Saravanan
venkat bojjavenkat bojja
Hi Durairaj Saravanan,

I good with your explaination, but only thing is ...
 i don't understad the why he is given as 

again why here 2+10 ,4+10,4+10,4+10,4+10,4+10,3+10  based on which logic the values changing like  2,4,3 and why added only +10 to every field.

In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 0, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+2+10," --why 2+10 here.
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 1, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+4+10," -- after above one why 4+10
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 2, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+4+10," --why 4+10 here
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 3, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+4+10,"  --
why 4+10 here
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 4, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+4+10," ----why 4+10 here
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 5, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+4+10,"  --why 4+10 here
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 6, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+3+10," --why 3+10 here


=========================================

and again why here 2+10 ,4+10,4+10,4+10,4+10,4+10,3+10  based on which logic the values changing like  2,4,3 and why added only +10 to every field.


In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 0, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+2+10," --why 2+10 here.
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 1, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+4+10," --why 4+10
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 2, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+4+10,"  --why 4+10
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 3, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+4+10,"  --why 4+10
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 4, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+4+10,"  --why 4+10
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 5, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+4+10," --why 4+10
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 6, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+3+10,"  --why 4+10

======================================
and again why here 6+20 ,8+20,8+20,8+20,8+20,8+20,7+20  based on which logic the values changing like  6,8,7 and why added only +20 to every field.
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 0, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+6+20,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 1, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+8+20,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 2, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+8+20,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 3, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+8+20,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 4, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+8+20,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 5, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+8+20,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 6, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+7+20,

===============================================================
and again why here  10+30,12+30,12+30,12+30,12+30,12+30,41  based on which logic the values changing like 10,12 and 41 ,why adding +30 to evvery field and why not added to last field.

In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 0, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+10+30,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 1, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+12+30,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 2, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+12+30,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 3, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+12+30,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 4, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+12+30,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 5, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+12+30,"
In case, "MOD(Today()-DATE(1900, 1, 7),7)" is returning value 6, "Plan_Due_By_Date___c" will get value "Plan_Due_By_Date___c+41,"


=========================Thanks for your help==================

Thanks,
Venkat.
akash_dev__cakash_dev__c
hi Venkat,

So, let's start with the fields in your formula and you have used : 

'Severity__c' which is a picklist field
'Plan_Due_By_Date__c ' is a date field.

and here are the functions used in the formulas: 

ISPICKVAL (To check for the values in the picklist field)
ISBLANK (to check for the null values)
MOD (to calculate the mod of the number)

so the explanation is that you are executing the conditions based on the picklist values under Severity__c field and then checking whether Plan_Due_By_Date__c is blank and then calculate the value from the function by subtracting the today() function value from the date function value.

Now understand the mod formula :
Today() => return the today's date
the expression (Today()-DATE(1900, 1,7) will return you the numeric value : 43,501 )

then,
the expression MOD(Today()-DATE(1900, 1, 7),7) => Mod(43,501, 7) which will yield 3 as the mod value

then, 
the case formula : CASE( MOD(Today()-DATE(1900, 1, 7),7),0,Plan_Due_By_Date___c+2+10, 
1,Plan_Due_By_Date___c+4+10,  
2,Plan_Due_By_Date___c+4+10, 
3,Plan_Due_By_Date___c +4+10, 
4,Plan_Due_By_Date___c +4+10, 
5,Plan_Due_By_Date___c+4+10, 
Plan_Due_By_Date___c+3+10)

case works like a condtional if-else-if =>

if mod is 0 then Plan_Due_By_Date___c+2+10
else if mod is either in (1,2,3,4,5) then Plan_Due_By_Date___c+4+10
else value is Plan_Due_By_Date___c+3+10


so now the mod value is 3 so it will come to the case(3,Plan_Due_By_Date___c +4+10) and execute aor put the value in field as (Plan_Due_By_Date___c +4+10)

I hope that now you will be able to get a clear picture of your formula. if you are still confused, feel free to ask or comment on it.

Please mark it as the best answer if I will be bale to help you out.

Good Luck

Regards,
Akash garg
Durai SaravananDurai Saravanan
Hi Venkat,

So, the values that they have given is based on the requirement or 'Business Logic' as they say.

That is not specific to code understanding. You have understood the code. 

But the values are based on who wrote this code and what requirement they had at that point of time. 

Again you can add any values you wish there. But if your requirement states that, on this case, Plan_Due_By_Date___c will get this value.
on another case, Plan_Due_By_Date___c will get another different values, so on and so forth

Any further queries or clarification on any other concepts of salesforce, fell free to mail me duraithemaster@gmail.com

Please mark this as 'Best Answer' if my reply was helpful. It will make it available for other as the proper solution.
                                             
Best Regards
Durairaj Saravanan
This was selected as the best answer