4 Replies Latest reply on Sep 29, 2016 6:15 AM by florian1

    How to avoid multiple entries in picklists ?

    m.kasper Apprentice

      Hello all,


      I have a picklist in which multiple entries are listed. How can I avoid to list multiple entries in the list displayed? QWith an SQL query, I would use a "distinct" keyword, but within the picklist configuration, I could not find anything.

      Is there a hdden trick?

      Do I need to go via constraints? If so, which functions?

      Help is highly appreciated, a newbe is calling

        • 1. Re: How to avoid multiple entries in picklists ?
          florian1 Expert

          I have had the same issue. The only (easiest) solution for me:

          Create/Use validation business objects and use validation constraints for the fields.


          Constrain your picklist with the source object and you're fine.

          • 2. Re: How to avoid multiple entries in picklists ?
            monomack Rookie

            Hei Florian,

            what definition of validation constraints you have used (Syntax=?





            • 3. Re: How to avoid multiple entries in picklists ?
              SusanJS Specialist

              What is the source of the pick list?  If the source is a pure validation object, you control it in the object by making the field that the pick list uses to display and save, unique.


              If the source is another object where the display field cannot be unique, I created a unique field that combines two fields together that will make the record unique.  For example, locations.  We identify locations by function as well as by address and one location may serve multiple functions.  So, to identify and prevent duplicate entries, I created a field that stores the concatenated information of the location name plus the function code (by business rule) and forced it to be unique.  So, while the location name could be the same as another record, the combination of the name + function must be unique and this is the field that the pick list uses in other objects.  (i.e. ABC Hospital functions as a hospital, but there is a data center located inside.  We wanted to be able to identify it also as a data center so the two unique listings would be "ABC - Hospital" and "ABC - DataCenter".


              Not sure if this helps your issue, but here are two options.

              1 of 1 people found this helpful
              • 4. Re: How to avoid multiple entries in picklists ?
                florian1 Expert

                Hi Rainer,


                for example:

                In an LDAP import you have the "roomNumber" attribute.

                I would like to list all the employees in a specific room in a request offering.


                The Employee object has the field "Room", which is like this out of the box:


                Now, if you were to create a mapping like this

                you will essentially have multiple room numbers in free text.

                If you create a picklist pointing to the Employee's room attribute, you will have multiple values.


                In SQL you could now use a "distinct" or "group by" option to show all the rooms, true.

                But when looking at database design best practices (second normal form), as many of your candidate keys (dimensions) as possible should be foreign keys.

                I think that's why it's not possible to use a distinct in pick lists (even though this could technically work).


                So instead of having a free text field only, you do the following:

                1) Create a validation business object for your rooms (e.g. XTN_RoomNumber):

                - add the field "Room" to this business object:

                - create a simple form containing the room number:

                - set the default form:

                - import all your existing rooms in the Employee table once (CSV import, WebService upload or similar)

                After running this import verify if all rooms were successfully imported:

                2) Create a PickList to this business object:

                3) Change the "room" field in Employee to validated:

                (Please ensure that both fields have the same type and size!)

                This will basically add a new field to Employee called "Room_Valid".

                Every time you update the "room" attribute for an employee, HEAT will do a lookup in the validation business object and save the foreign key's RecId in Room_Valid.


                4) Define a process to have your validation business object updated

                You can either manually add new values or have them automatically imported (in this case during my LDAP import).


                5) See if it works.

                - The room attribute is now validated:

                (user1 is in room 1

                user2 is in room 2a

                user3 is in room 1)



                Depending on the type of entries, you can apply Susan's approach here as well.




                2 of 2 people found this helpful