+ Start a Discussion

Change picklist values to match content

We are loading external data via a nightly job from SQL Server using DBAmp.


Some of the fields we populate need to be picklists so that users can easily select filter criteria in reports.


Is there a way that I can change the pick list values automatically when new values get added in the source system?




Hi Stefan,


Have you taken a look at the metadata api yet?


Here is something on picklist creation:




Let me know if this helps.






Hi Kevin, I have very little knowledge of Apex, so my approach up to now has been to use DBAmp and SQL Server to manipulate Salesforce data.


Would appreciate some hints as to the best way forward?


Would I use a trigger:


- Check for value in table not existing in picklist

- Add picklist value to picklist


Are there any examples you could point me to that do something similar on a custom object?


I am working through Apex examples both using Eclipse and within Salesforce Sandbox but struggle a bit as the concepts are new to me not having Java experience.

Can anyone help me with this?
Can anyone help me with this?

Hi Stefan,


There is no way for you to create picklist values using Apex or a Trigger. At least I have not seen any way to do this yet. You have to use the MetaData API. The link I posted earlier shows an example of how to do this in JAVA. You can create new picklist values but you cannot change any values that are in use by existing records.


Whatever server you are using to push data into force.com will also need to check to see whether a picklist value exists or not. If it does not exist, it should run some code which connects to force.com and creates new picklist values.


Alternatively you can use an object to store custom picklist values. This is actually a very flexible way to create and remove picklist values on the fly. If you have a lot of picklist values that will continue to grow, this is a good way to manage them.


Instead of having a picklist field on your object, use a lookup reference to another object. We can call this object PicklistValues__c.


Look here for more info: http://community.salesforce.com/sforce/board/message?board.id=Visualforce&message.id=9656


Kevin, thanks for your response.


It would be very easy to maintain a table containing the values using DBAmp.


However a picklist gives users a dropdown when creating filters in reports. A lookup gives users no intuitive way of finding allowable values.


Saying that, I may well go for a lookup.


This is annoyingly limited. Isn't it?  :smileymad:


I would like to also be able to create a picklist that has ID number values that are associated with each picklist item. But the system can only support text string values in the picklist that cannot be changed once those values have been used in records. 


I see your point about having to use a lookup for this situation. It'll work, but is less than ideal. I'm also looking at this for a picklist with multi-select and that seems to be impossible.  :-(