+ Start a Discussion
CherylCheryl 

Passing field data with an Ampersand (&) into a formula field that feeds a report filter

I've spent hours on this today (trial & error and searching help/forums) and hope one of you has an answer to this!

 

1.  I have a basic custom object where we've built many reports that hyperlink from that object - and feed a report formula automatically with data from a field on the object called "End Markets for Report Filters"  (benefit being we just clone the object page, change the name of the  End Markets for Report filters - for each end market team-- and then each report hyperlinked from that object works for all end market teams - they just go to their own page)

 

2.  The data in that field is passed through via a formula text hyperlink to a report with filter: 

 "End Market Group" (which is a custom field on opportunities) contains  [the data in the "end markets for report filters" field]

 

3.  Until recently, data in the "End Markets for Report Filters" field was pure text, such as "automotive", "communications", etc.  - and it worked fine

 

4.  But now I need to pass "I&I"through to the formula below-- just like I do today with "automotive"

 

HYPERLINK("00O60000001tGct?pv0=" & End_Markets_for_Report_Filters__c, "New Opportunities created Last 90 Days" )   ** <-- space between Days" and ) just to the left isn't there, but it keeps making a winking smiley face so I added a space

 

Question:  How can I pass a field that has an ampersand into the formula above?  My preference is to do it on the custom object - because I'm not sure if changing the formula above will impact the plain text pages.

 

What I've tried already:  On the custom object, I've tried changing the I&I with "I&I" or I"&"I or I'&'I  etc. 

 

Note:  If I edit the underlying report to....  contains... I&I (no quotes, nothing special), the report works fine.  It's just passing it through via hyperlinks on the custom object that's a problem (it comes through as contains  ,I    for example)

 

This impacts thousands of records that have "I&I" in that field name, so changing the core data so that I don't have to search on I&I isn't a good option.

 

Thoughts?

 

THANK YOU!!!

Best Answer chosen by Admin (Salesforce Developers) 
CherylCheryl

Posting an answer to my own question (my husband, who isn't familiar with SFDC, but programs in other languages, gave me the suggestion.  I tried it and it worked)

 

I&I  needed to be written as:   I%26I

 

In other words, the ampersand (&) in a field that gets passed to a hyperlink formula, must be replaced with %26

All Answers

CherylCheryl

Posting an answer to my own question (my husband, who isn't familiar with SFDC, but programs in other languages, gave me the suggestion.  I tried it and it worked)

 

I&I  needed to be written as:   I%26I

 

In other words, the ampersand (&) in a field that gets passed to a hyperlink formula, must be replaced with %26

This was selected as the best answer
Bart BoersBart Boers
Posting a thank you for your 10 year old answer to your own question. I was trying to escape ampersands... :D 
anglina voltan 19anglina voltan 19
CVS is one of the largest drugstore chains in the United States that provides high-quality healthcare and wellness solutions. visit here : cvssurvey