function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
RatherGeekyRatherGeeky 

Formula that Returns First Sentence of Long Text Field

Hi. Apparently long text fields are not supported in list views or formulas.
Error: You referenced an unsupported field type called "Long Text Area" using the following field: Description
I'm trying to develop a formula that will display the first sentence from a long text field.
I thought that I could use a FIND function to locate the first period, then return the text before that.
But, the long text field that I'm looking for is not shown as a field that I can insert. (I want to use Lead > Description) So, I just typed it in.

Some of my users have leads that are the same except for the description, and I'm trying to figure out a better way for distinguishing them from a list view. Otherwise they look like dupes. I don't want to add an extra custom text field that they have to enter.

Thoughts on how to resolve? Alternatives? Thanks in advance.
BrianWKBrianWK
Have you thought of using a Trigger that keeps a read only text box  up to date whenever the Record with the Long Text Area changes?
 
We've done something similar to help track what the last "description" was that was entered, while retaining the full description history.
RatherGeekyRatherGeeky
Brian:

Thanks for your suggestion. (I haven't gotten to the point of creating triggers yet, but when I do, I will refer to this solution.)
andresperezandresperez

Hi,

I have one idea that you may want to consider...

On the calculation that you are having to find the phrase before the first period, append some field that identifies the record, but that is maintained by Salesforce.com (ID, Name, Owner, Date,...)

Message Edited by andresperez on 01-21-2009 04:03 AM
dev_jhdev_jh

Hi there,

 

It seems odd that you have leads that look the same except for the description and are not dupes. Maybe an alternative is to define more fields as mandatory or have a validation rule that requires either a phone number or an email address (in case you don´t want to make any of them mandatory).

 

After all, if all fields are the same except the description, how are you planning on getting back to the right Joe Black?

 

Regards,

 

J

RatherGeekyRatherGeeky

J:

 

We have a number of different offices with at least one rep in each that are gathering leads from online sources, word of mouth, etc. There are some leads that require contacting the same person, but the description for the work is different. 

 

Example:

Joe Black, Black Enterprises, (111) 222-3333 wants to buy 50 red widgets for his employees

Joe Black, Black Enterprises, (111) 222-3333 wants to buy 75 blue widgets for his subcontractors

 

Same Joe Black, but the leads are for different types of work. Is there a better way to handle leads like this?

 

Thanks for your feedback.

 

JB

Message Edited by J Baze on 01-21-2009 07:29 AM
dev_jhdev_jh

Hi JB,

 

Understood the Business scenario... I think it would make sense to have a Product Interest custom field so that you can differentiate both leads. Having to differentiate them on the basis of a text field is probably not ideal anyway.

 

If you still require this, it can be done using a bit of code. The trigger alternative is perfectly viable but requires coding (not only the trigger but also test cases) and deploying the code from Sandbox to Production.

 

Best of luck,

 

J

RatherGeekyRatherGeeky

J,

 

Good suggestion.

 

Thanks,

 

JB

toddringtoddring

I have a similar problem but for a different use.  My sales people would like to display the first 50-100 chars of the DESCRIPTION field on the main Leads Page.  I wanted to do the same thing by creating a field that would basically just grab the first 50 or so and use that field to display on the front page.  This way my reps don't need to click on every Activity History to get a quick idea of what was discussed.  Any help or suggestions would be appreciated.

 

toddring

RatherGeekyRatherGeeky

toddring:

 

As far as I can tell, you can't access the description (long text field) via a formula to return just a part of it.

 

I'm not exactly sure what you are referring to: a list view or a related list? Do you want this to appear when they click on the Leads tab? or in a related list on another record?

toddringtoddring

J Baze

 

My sales reps would like to see the comments from the Activity History when they are looking at a Lead.  So for instance, when you click on a Lead currently you can only see what Activity History is there by subject but if you want to see what happened on a specific history you have to click on it to go into the detail and see the comment.  Hopefully this describes it better.

 

toddring

RatherGeekyRatherGeeky

toddring:

 

Someone else might have a suggestion, but I am not sure how this can be done.

 

I tell my users to be brief, but descriptive with their subject lines (kind of like email subject lines, or even sf community post subjects) so that it is obvious what the activity is related to.

 

Example: "Call" is not as obvious as "Call re: contract update for 2010" or something similar.

 

But, like I said, hopefully someone else has a suggestion for accomplishing more of what you are looking for.

tfurbertfurber
Ill paypal $50 to the first person with a solution to this problem. I have the same need.
RatherGeekyRatherGeeky

Yay for apex! I created the following trigger in a sandbox that successfully transfers the first 100 characters of a long text field to a standard text field.

 

I created the text field and set the length to 100 characters and added it to my page layout to test.

 

Here's the trigger:

 

trigger PassLongField on Lead (before update) {



for (Lead lead : System.Trigger.new) {


//transfer long text field

Lead.Long_to_Short__c = Lead.description;

}


}

 

Pretty straightforward. The Long_to_Short__c variable is the one that I wanted the first 100 characters to be stored in. (It could be any number of characters though.)

 

When the user clicks 'save', the value is passed.

 

If you'd like to see a screen capture of what this looks like, I can send it to you via email.

Message Edited by J Baze on 08-05-2009 08:47 AM
tfurbertfurber
can you use apex with professional?
tfurbertfurber
im trying to use this to copy the first100 characters of the comments (description) of a call log to a new variable so i can show it on the lead page without clicking on view all.
RatherGeekyRatherGeeky
No. It is only available in Unlimited, Developer and Enterprise editions. Hmm... I'll think about alternatives.
tfurbertfurber
Ill paypal you $50 for the correct workaround. 
RatherGeekyRatherGeeky

Basically, there is no way to reference a long text field in a formula. Verified in another posting.

 

So, here's the only other alternative I can think of. Whether or not you decide to do this might depend on how far along you are in implementation and whether or not your users freak out when you change a process (or if you have the flexibility to make a decision to change the process).

 

Here's my idea:

 

Add a text area field (limit: 255) that will show the main 'subject' that you want to show in your list view. This field will essentially be like the subject line of an email, or a brief overview of the entire conversation. Let's call it "Call Summary." Entries could be something like this: "Client is interested in further discussion" or "Discussed problems client was having with the most recent release."

 

Then, that field could be included in your list view.

 

So, you could tell your users to put the basic summary of the whole conversation in the Call Summary field, and then add additional details in the Comments/Description field. It would make sense to add the Call Summary field before the Description field on your page layout.

 

Then, you can modify your existing data by using the excel connector. Run a query of all leads with a description (where description not equal to blank) and use an excel formula to pass the first 100 characters of the description field into the new Call Summary field.

 

This would involve introducing a new procedure for your users. I'm not sure if that is something you are willing to consider. 

 

But, other than that, this is not possible with existing functionality and your version of Salesforce. Considering an upgrade anytime soon? ;)

Message Edited by J Baze on 08-05-2009 02:41 PM
tfurbertfurber
great idea but they require api access which i dont have.  just professional.
RatherGeekyRatherGeeky
Do you have access to any other data loader? You've got to have something that allows you to mass add data to salesforce, right?
tfurbertfurber
ive used the builting lead importer to import csv files.  i dont know of a way to mass update fields.
Message Edited by tfurber on 08-05-2009 03:29 PM
RatherGeekyRatherGeeky

Turns out you can get the Excel Connector. I recommend it. I use it pretty much every day for verifying data and mass updates.

 

http://blogs.salesforce.com/blogs/2005/07/sforce_connecto.html 

 

Someone posted this question there:  

Does this mean Professional Edition users will have access to the API with the connecter? 

 

and this was the response:

Just through the connector, just as Professional edition customers have access to the API using outlook edition 

 

So, if you can use the Outlook Connector, you can use this too. 

RatherGeekyRatherGeeky

Just discovered you can't filter on description field in a query but that shouldn't be a problem if you just want to pass the characters.

 

After querying to show all the leads, you would use a formula like this to pass the first 50 characters of the description field to your new smaller field: =LEFT(B3,50)

 

That it's just a matter of highlighting the cells you want to update and clicking "Update Selected Cells." 

 

After getting the Connector installed, it is pretty easy to use. You just need to have your security token handy.

 

If you need help figuring it out, just let me know. 

tfurbertfurber
playing with it now, but its not giving me the option to export my custom activity fields
tfurbertfurber
its under event instead of task
RatherGeekyRatherGeeky

With the Excel Connector? They should appear in the list of fields that you can query. You should be able to run a query of tasks that shows your custom fields.

 

If you send me a private msg via the community with your email, I will send you a screen capture.

RatherGeekyRatherGeeky
Right. It splits up tasks and events so that you can't query on just all activities at once. Have you confirmed that your fields are associated with tasks and not events in your data model (or vice versa depending on what you are looking for)?
Message Edited by J Baze on 08-05-2009 03:51 PM
tfurbertfurber
it doesnt seem to export many lines.  the custom field i have is under activities.  in the excel connector it shows me the custom field under tasks.  when i export it out it should be exporting like 10,000 lines but it only does like 17 or 89 or something far less than it should.
tfurbertfurber
i just added my custom field to the task page layout andnow it pops up under tasks in the excel connector
RatherGeekyRatherGeeky

Do you mean it is not exporting all the records? If so, you need to give it some sort of filter criteria. Otherwise it shows you everything created since a certain recent date by default. If you want to show all of them (might take the query a little while to execute), add a criteria like this:

 

id not equals ___ (leave the criteria field blank) 

 

This is a query that returns all records.

tfurbertfurber
got all of my records, but now excel isnt calculating formulas
RatherGeekyRatherGeeky

I have that same problem sometimes. Sometimes it does and sometimes it doesn't.

 

Try this: Change the cell to General in Format-Cells and then delete the formula and re-enter it. 

 

If that doesn't work, try opening another spreadsheet, copying all the data in your query (including the header info) and then paste in a new spreadsheet.

tfurbertfurber
seems like i got it going, thanks for the help.  whats your paypal account, you can msg it to me. on another note, do you know how to make activities searchable?
RatherGeekyRatherGeeky

Activities are already searchable, but they are broken out into tasks and events.

 

You can also install an advanced search in the sidebar (posted on Shamrock CRM). My users absolutely love this. See details here. 

RatherGeekyRatherGeeky

tfurber:

 

The bounty was a nice way of prompting activity on this post. I was really excited about it. I'm kind of disappointed that you haven't responded to my msg yet.

 

But, maybe that's my own fault for being too enthusiastic. 

RatherGeekyRatherGeeky

tfurber:

 

Thanks for your quick response! Now I'm back to being optimistic. 

DevAngelDevAngel
Paypal is not a verb!
RatherGeekyRatherGeeky
It would be if it were up to me. :) It might be someday.... kind of like how Google was eventually added to the dictionary as a verb, right?
Karen DavisonKaren Davison
Your posts were so many years ago that you've probably identified a solution by now, but an option would be to create a workflow rule.

Create a new text field on the Lead, then create a workflow rule that triggers every time a Lead is created.  In the formula editor of the rule either enter the long text field, or a formula that takes the first sentence, or first 50 characters, or whatever is needed.  Basically, put your formula in the workflow rule instead of the field.
Chris LanceChris Lance
Agree with Karen, workflow seems to make more sense and a bit less "heavy" then using APEX to do this.  In fact, I believe it's Salesforce best practice to try to use admin solutions over development solutions when possible.