+ Start a Discussion
SfdcASSfdcAS 

Formula for Hyperlink with 0-2 parameters

Have a text field 'Summary' with ids as values and Summary1 with names as values

Summary = a00o0000001Lgmb,a00o0000001LZRN,a00o0000001id76

Summary1=new product,product1,product2

Have created a formula field 'new' which displays the values of the Summary1 field as hyperlink but when clicked it redirects to the record by the record ids in the Summary field. But this formula is not working correctly when there are 0 -2 parameters. 

Now the formula is gettng displayed correctly as the below when there are 3 values:

new= new product,product1,product test 

Now the problem is when there is just one value or 2 values in the summary1 field , the values get displayed twice.

for eg if,
Summary = a00o0000001Lgmb,
Summary1=new product,

then
new=new product, new product.

Wanted to know how to display the values correctly when there are 0-3 parameters.

(HYPERLINK("/"&LEFT(Summary__c,18),LEFT(Summary1__c,  FIND(",", Summary1__c,0))))&(',')&

(HYPERLINK("/"&RIGHT(Summary__c,18),RIGHT(Summary1__c, LEN(Summary1__c) - FIND(",", Summary1__c, FIND(",", Summary1__c,0)+1))))&(',')&

(HYPERLINK("/"&MID(Summary__c,20,18),MID(Summary1__c, FIND(",", Summary1__c), 
FIND(",", Summary1__c, FIND(",", Summary1__c)+1)- FIND(",", Summary1__c))))

I tried using IF(FIND) but not sure how to use it
Something like (IF(FIND(Summary1__c, ",")>0   
IF(FIND(Summary1__c, ",",1+FIND(Summary1__c, ","))>0

Please help
Best Answer chosen by SfdcAS
YuchenYuchen
The basic idea is that "LEN(Summary__c) - LEN(SUBSTITUTE(Summary__c, ",", ""))" will tell you the number of commas in the Summary field, so:
- If no parameter or only 1 parameter, Comma Count will be 0
- If 2 parameters, Comma Count will be 1
- If 3 parameters, Comma Count will be 2

Here, if we want to improve the formula to return the count of parameters, then it will be like this:
IF( (LEN(Summary__c) - LEN(SUBSTITUTE(Summary__c, ",", "")))=0 && LEN(Summary__c)=0, 0, (LEN(Summary__c) - LEN(SUBSTITUTE(Summary__c, ",", "")))+1)

In the above formula, the Count returned will be the actual Parameter Count:
- If 0 parameter, Parameter Count will be 0
- If 1 parameter, Parameter Count will be 1
- If 2 parameters, Parameter Count will be 2
- If 3 parameters, Parameter Count will be 3

You can then combine the above formula with your formula to create the links and stuff.

All Answers

YuchenYuchen
The basic idea is that "LEN(Summary__c) - LEN(SUBSTITUTE(Summary__c, ",", ""))" will tell you the number of commas in the Summary field, so:
- If no parameter or only 1 parameter, Comma Count will be 0
- If 2 parameters, Comma Count will be 1
- If 3 parameters, Comma Count will be 2

Here, if we want to improve the formula to return the count of parameters, then it will be like this:
IF( (LEN(Summary__c) - LEN(SUBSTITUTE(Summary__c, ",", "")))=0 && LEN(Summary__c)=0, 0, (LEN(Summary__c) - LEN(SUBSTITUTE(Summary__c, ",", "")))+1)

In the above formula, the Count returned will be the actual Parameter Count:
- If 0 parameter, Parameter Count will be 0
- If 1 parameter, Parameter Count will be 1
- If 2 parameters, Parameter Count will be 2
- If 3 parameters, Parameter Count will be 3

You can then combine the above formula with your formula to create the links and stuff.
This was selected as the best answer
SfdcASSfdcAS
Hi Yuchen. Thanks for the reply.

I tried combining the formulas and its working now ! :)

IF( (LEN(Summary1__c) - LEN(SUBSTITUTE(Summary1__c, ",", "")))=1 ,

HYPERLINK("https://na17.salesforce.com/"&LEFT(Summary,15),LEFT(Summary1__c,FIND(",", Summary1__c,0)-1)),

(HYPERLINK("https://na17.salesforce.com/"&LEFT(Summary,15),LEFT(Summary1__c,FIND(",", Summary1__c,0)-1))
&' '&

HYPERLINK("https://na17.salesforce.com/"&LEFT(Summary,15),RIGHT(Summary1__c, LEN(Summary1__c) - FIND(",", Summary1__c, FIND(",", Summary1__c)+1)))
&' '&

HYPERLINK("https://na17.salesforce.com/"&LEFT(Summary,15),MID(Summary1__c, FIND(",", Summary1__c),FIND(",", Summary1__c, FIND(",", Summary1__c)+1)- FIND(",", Summary1__c)))))