You need to sign in to do that
Don't have an account?
RatherGeeky
Formula that Returns First Sentence of Long Text Field
Hi. Apparently long text fields are not supported in list views or formulas.
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.
Error: You referenced an unsupported field type called "Long Text Area" using the following field: DescriptionI'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.
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.)
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,...)
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
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
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
J,
Good suggestion.
Thanks,
JB
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
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?
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
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.
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.
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? ;)
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.
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.
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.
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.
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.
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.
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.
tfurber:
Thanks for your quick response! Now I'm back to being optimistic.
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.