function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Jeff@TargetX-chgJeff@TargetX-chg 

VLOOKUP Function for data exchange

I am trying to figure out a way to create a lookup that will return a 4 letter code for a description field.
For example, we have a bunch of Majors in a picklist (History, Biology, Chemistry) that have corresponding codes (HIST, BIOL, CHEM). What I would like to do is take the value of the picklist and translate it to the corresponding 4 letter code so we can use the code to do the data exchange. The Salesforce user needs to choose from a picklist of "human readable" descriptions, but the data needs to be manipulated to the 4 letter code before data exchange.
If anyone has any ideas of examples, I would greatly appreciate it.
Thanks,
Jeff
Angieme1Angieme1
   
Can you set up another Formula field as a picklist and say:
(Say the picklist field is called Department and you created a custom Formula field called Dept_four_letter:)

Dept_four_letter=

If Ispickval(Department_c,"History"),"HIST",if(ispickval(Department_c,"Chemistry"),"CHEM",etc..

Then when you do the data exchange, you just transfer the Dept_four_letter field


Or... you could say
Dept_four_letter = Left(Departement_c,4)

Then the new field would always = the left 4 letters of the Department field

I don't know if I fully understood if this is what you were trying to accomplish or not....
Jeff@TargetX-chgJeff@TargetX-chg
Thank you for the response. I used a CASE statement that did the same that you were referring to in the Ispickval example. The only problem is that the value is a read only value in the formula field. If the data exchange is bi-directional, then the value of the formula field needs to be "update-able". I think the translation needs to be done during the data exchange.
Angieme1Angieme1
    How are you doing the data exchange? Is it a download/upload from Excel, or from system to system?
Jeff@TargetX-chgJeff@TargetX-chg
I am using an ETL tool called Boomi. It is a web interface that sits between Salesforce and the client's main database. This is all for higher education, so Salesforce is used for prospective student information. Once they hit a certain student status, they are transferred to their database through Boomi. We want to use descriptive information in the picklist, but have it transferred to their database as a code.
Angieme1Angieme1
    I know vlookups in Excel, but I'm not really familiar with Databases.

The only thing I can think of is this:

Have two fields:
1) Field 1= LongDesc
2) Field 2= ShortDesc

You show the user in Salesforce the LongDec and have it as a picklist. Then you set up a workflow rule that updates the ShortDesc anytime the LongDesc field is edited or created. Then when you transfer data to the DB, you transfer the ShortDesc. Then if the data is coming back into Salesforce, the ShortDesc would be coming back correct? So you would then do the same thing and set up a workflow rule that updates the LongDesc everytime the ShortDesc changes.

Therefore, both Field 1 and Field 2 are picklists, but you set up a workflow rule field update that when one is created, so is the other and when one is edited so is the other. The formula exists in the workflow rule, not in the Fields itself.

Would this work?
Jeff@TargetX-chgJeff@TargetX-chg
Thanks. I think that will work, depending on the syntax of the workflow. I recommended that we do it through the Boomi tool only because you can write procedures to translate the codes. I will test your suggestion as well to see what the best solution would be.
I appreciate your help in my situation.