Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Sarah Smith 15Sarah Smith 15 

How can I compare two fields and if a > b, check the box, where a=3.56.0.0, b=3.55.12.1. Here is what I have tired.

VALUE( 
IF( 
LEN(SUBSTITUTE(Account.Software_Version__c, '.', ''))=5, 
SUBSTITUTE(RPAD(Account.Software_Version__c,6,'0'), '.', ''), 
SUBSTITUTE(Account.Software_Version__c, '.', '') 
)) 

>= 

VALUE( 
IF( 
LEN(SUBSTITUTE(Target_Release__c, '.', ''))=5, 
SUBSTITUTE(RPAD(Target_Release__c,6,'0'), '.', ''), 
SUBSTITUTE(Target_Release__c, '.', '') 
))
Best Answer chosen by Sarah Smith 15
Sarah Smith 15Sarah Smith 15
A dev here at my company helped...
I think you were super close.  All I did below is check the length of the complete string first and then pad the zero on the end with the decimals still in the string (total expected length then should be 9 instead of 6).  From there, I strip the decimals out just like you were and do the compare.  We just need to agree that we’ll never do a 3.56.1.0 and then a 3.56.10.0.  As long as we never do that type of versioning, this should work:
 

VALUE(
IF(
LEN(Account.Software_Version__c)=8,
SUBSTITUTE(RPAD(Account.Software_Version__c,9,'0'), '.', ''),
SUBSTITUTE(Account.Software_Version__c, '.', '')
))
 
>=
 
VALUE(
IF(
LEN(Target_Release__c)=8,
SUBSTITUTE(RPAD(Target_Release__c,9,'0'), '.', ''),
SUBSTITUTE(Target_Release__c, '.', '')
))
 

All Answers

Alain CabonAlain Cabon
Hi,

a=3.56.0.0, b=3.55.12.1.    It is very difficult to compare these concatenation of four numbers with Salesforce because there is no split function.

You can just try with SUBSTITUTE and VALUE indeed.

a1= 3
a2=56
a3=0
a4=0

b1=3
b2=55
b3=12
b4=1

if a1>b1 then a > b else if a2 > b2 then a > b else if a3 > b3 then a > b .... but you cannot get the four numbers a1, a2, a3 and a4 easily.
 
Sarah Smith 15Sarah Smith 15
Thanks @Alain Cabon.  What is the error in my thinking with my formula?
 
Alain CabonAlain Cabon
You should state how your numbers work at first. I imagined a sort of rule but it is at least a series of four numbers?

A string with four numbers delimited by periods will be very difficult to compare with a formula.
That could be possible but the formula will be huge.
 
Sarah Smith 15Sarah Smith 15
Thanks...I give up.  :0)
Sarah Smith 15Sarah Smith 15
A dev here at my company helped...
I think you were super close.  All I did below is check the length of the complete string first and then pad the zero on the end with the decimals still in the string (total expected length then should be 9 instead of 6).  From there, I strip the decimals out just like you were and do the compare.  We just need to agree that we’ll never do a 3.56.1.0 and then a 3.56.10.0.  As long as we never do that type of versioning, this should work:
 

VALUE(
IF(
LEN(Account.Software_Version__c)=8,
SUBSTITUTE(RPAD(Account.Software_Version__c,9,'0'), '.', ''),
SUBSTITUTE(Account.Software_Version__c, '.', '')
))
 
>=
 
VALUE(
IF(
LEN(Target_Release__c)=8,
SUBSTITUTE(RPAD(Target_Release__c,9,'0'), '.', ''),
SUBSTITUTE(Target_Release__c, '.', '')
))
 
This was selected as the best answer