[SalesForce] Retrieve data from a custom object via lookup formula

I'm trying to populate a field on the leads object with data from a custom object via a formula field. I'm pretty close to getting the right stuff there, but I am getting a little hung up and wondering if anyone out there has a solution.

I'm retrieving the data via VLOOKUP, but I keep getting syntax errors. I have create the relationship with the leads and custom object, which is labeled Market__c. The field i am trying to retrieve data from is a picklist and it keeps telling me that "Error: Field Market_Status__c is a picklist field. Picklist fields are only supported in certain functions." The picklist only has 2 options, open and closed. Thats a pretty obvious explanation as to why it won't work, but I am looking for the value of the field, not the picklist values.

In the code below, Market__c is the field from the lead that I am passing over as the value to search for in the Market Object. It should match the field Name in the Market object and return the value of the field Market_Status__c associated with the matched Name.(For instance Market__c = Miami: FL. it matches the name in the Market object and finds whether the market is open or closed. That data is contained in the Market_Status__c field.)

AND(LEN(Market__c) > 0,
VLOOKUP( Market_Statuses__r.Market_Status__c ,  Market_Statuses__r.Name , Market__c ))

Anyone have any idea what I could be doing wrong?

Best Answer

If I understood correctly, this might help:

AND(LEN(Market__c) > 0, VLOOKUP( TEXT(Market_Statuses__r.Market_Status__c) , Market_Statuses__r.Name , Market__c ))

Related Solutions

[SalesForce] Matching Custom Object’s Data Using Lookup Defined by Multiple Fields

You could create an external ID text field on the Contact object that could be referenced from the Purchases load file (e.g., concatenation of some fields) and then perform an upsert of Purchases, specifying to use the new field to match on the Contacts.

When using the Data Loader Step 2b will be to choose your related Objects. At this point you will have the ability to select the External ID field for the Contact__r (or whatever your lookup to Contact field is named). In the mapping step, make sure that the external ID field is selected as well (e.g., Contact__r.External_ID__c).

To keep the external ID populated on the Contact, you could create a workflow rule that copies the FirstName and LastName (or whatever fields work in your situation) to an external ID field that you create, but don't add it to any page layouts. There'd be an initial update to all existing Contacts you'd have to do to get their external ID set.

If you go this route, just watch out for duplicates. If possible, it would be a good idea to try to select a combination of fields on the Contact that are also available on the Purchases that you could guarantee would always be unique and make the Contact external ID field unique.

To create the external Id field on the Contact, just create a new custom Text field on the Contact and check the External ID checkbox on it while creating it. There's a checkbox called External ID with a description of "Set this field as the unique record identifier from an external system".
Create a Workflow rule on the Contact that fires when at least on of the fields you are interested in changes (see ISCHANGED function). For example, ISCHANGED(FirstName) || ISCHANGED(LastName)
Create a Workflow field update that has a target field of the new external ID field. Set its formula to be the value of FirstName + LastName (or whatever fields you use). For example, formula value of FirstName + LastName.
Make sure to activate the Workflow rule.

It would be a good idea to test out whatever you are doing in a sandbox first, of course.

[SalesForce] Lookup data from a custom object from a formula field

Yes, a formula field is the way to go here. Add a custom field to lead, formula data type, text return type. Select the Advanced Formula Editor, and then click "Insert Field". You should see an item like "Market >" - click that and you can select fields from the Market object. When you find the right field, insert it. You should end up with something like

Market__r.Market_Status__c

If Market Status is a picklist field, you need to wrap the whole thing in a TEXT() for it to work:

TEXT(Market__r.Market_Status__c)

Related Topic