Create List Items (aka EnumerationValues) In Bulk Using EnumCreator.xlsx

Create List Items (aka EnumerationValues) In Bulk Using EnumCreator.xlsx

  • Comments 23
  • Likes

This blog post is intended for developers and people doing advanced customizations in Service Manager.

Not too long ago I shared a blog post called 'Understanding Lists/EnumerationValues: CI Status Example’ which explained how you can make simple, quick modifications to lists using the main console and how you can add list items through Management Pack XML too. Sometimes these list taxonomies can be very large.  Creating each one of them one by one in the console could be pretty time consuming if you have tens or even 100+.

One of our Microsoft Consulting Services consultants, Tomaz Cebul, had a great idea for a list item creator tool in Excel.  He sent me a draft version of this to which I have added some things.  Thanks Tomaz!

This spreadsheet allows you to enter in list item display names.  Behind the scenes, some crazy Excel functions are building the management pack XML for you based on what you enter.  Then all you have to do is copy the Management Pack XML out of the spreadsheet, paste it into a .xml file and import it into Service Manager.

Here is how you use the tool:

  1. Enter the ID of your MP.  Alphanumeric characters only with no spaces.
  2. Enter the Version of your MP.  Unless you have some specific reason not to, I’d suggest using ‘1.0.0.0’.
  3. Enter the Name of your MP.  Alphanumeric characters only.  Spaces are allowed.
  4. Decide if you are going to extend an existing list or create an entirely new list.  If you are customizing something like Incident Classification as I did in this example, you are customizing an existing list.  If you are building a brand new solution or adding a new field which includes a new enum data type property then you are probably creating a new list.  If you are extending an existing list, you’ll need to enter a couple of pieces of information:
    1. The MP ID of the MP that contains the “root” Enumeration value you want to extend from.  Note: on the worksheet called ‘OutOfBoxEnums’ I provided a list of the MP ID and Enum ID of some of the lists you are most likely to want to customize.
    2. The MP Version of that MP.
    3. The MP Public Key Token of that MP.
  5. Now start to fill out the table with your list items.
    1. If you are extending from a root in another MP, enter the root enum ID in the first column.  Otherwise leave the first column blank.
    2. For each enum item, you need to enter a row of data.
  6. When you are done entering your enums, copy the MP XML and paste it into a file. Save the file as the same name as the MP ID + the .xml file extension.

Note: this tool only works for English.  If you need to localize the content into an additional language please see the blog post on Localizing Management Pack Content.

Note: you can extend more than one root in a single custom MP, but both roots must be in the same MP as in the example below.

Note: you can create up to 200 enum values per MP using this tool.  If you need more than that you can do it as two different MPs or if you have some knowledge of Excel you can probably figure out how to unprotect the worksheet and make the table a little larger.

Here are some screen shots of an example I did in the tool:

image

image

This results in a management pack that looks like this:

<ManagementPack  ContentReadable="true" SchemaVersion="1.1" OriginalSchemaVersion="1.1">

  <Manifest>

    <Identity>

      <ID>MyCustomMP</ID>

      <Version>1.0.0.0</Version>

    </Identity>

    <Name>My Custom MP</Name>

    <References>

      <Reference Alias="System.WorkItem.Incident.Library">

        <ID>System.WorkItem.Incident.Library</ID>

        <Version>7.0.5244.0</Version>

        <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>

      </Reference>

    </References>

  </Manifest>

  <TypeDefinitions>

    <EntityTypes>

      <EnumerationTypes>

        <EnumerationValue ID="CoffeeMachine.Enum" Accessibility="Public" Ordinal="0" Parent="System.WorkItem.Incident.Library!IncidentClassificationEnum"/>

        <EnumerationValue ID="CoffeeMachine.Filters.Enum" Accessibility="Public" Ordinal="1" Parent="CoffeeMachine.Enum"/>

        <EnumerationValue ID="CoffeeMachine.Drain.Enum" Accessibility="Public" Ordinal="2" Parent="CoffeeMachine.Enum"/>

        <EnumerationValue ID="CoffeeMachine.Drain.Pipe.Enum" Accessibility="Public" Ordinal="3" Parent="CoffeeMachine.Drain.Enum"/>

        <EnumerationValue ID="CoffeeMachine.Drain.Fitting.Enum" Accessibility="Public" Ordinal="4" Parent="CoffeeMachine.Drain.Enum"/>

        <EnumerationValue ID="CoffeeMachine.Coffee.Enum" Accessibility="Public" Ordinal="5" Parent="CoffeeMachine.Enum"/>

        <EnumerationValue ID="CoffeeMachine.Coffee.OutofCoffee.Enum" Accessibility="Public" Ordinal="6" Parent="CoffeeMachine.Coffee.Enum"/>

        <EnumerationValue ID="CoffeeMachine.Coffee.CoffeeTastesBad.Enum" Accessibility="Public" Ordinal="7" Parent="CoffeeMachine.Coffee.Enum"/>

        <EnumerationValue ID="CoffeeMachine.Coffee.CoffeeTastesBad.TastesWateredDown.Enum" Accessibility="Public" Ordinal="8" Parent="CoffeeMachine.Coffee.CoffeeTastesBad.Enum"/>

        <EnumerationValue ID="CoffeeMachine.Coffee.CoffeeTastesBad.TooCold.Enum" Accessibility="Public" Ordinal="9" Parent="CoffeeMachine.Coffee.CoffeeTastesBad.Enum"/>

        <EnumerationValue ID="CoffeeMachine.Coffee.CoffeeTastesBad.DoesntTasteLikeStarbucks.Enum" Accessibility="Public" Ordinal="10" Parent="CoffeeMachine.Coffee.CoffeeTastesBad.Enum"/>

        <EnumerationValue ID="CalledCoffeeMachineRepairTechnician.Enum" Accessibility="Public" Ordinal="11" Parent="System.WorkItem.Incident.Library!IncidentResolutionCategoryEnum"/>

      </EnumerationTypes>

    </EntityTypes>

  </TypeDefinitions>

  <LanguagePacks>

    <LanguagePack ID="ENU" IsDefault="true">

      <DisplayStrings>

        <DisplayString ElementID="CoffeeMachine.Enum">

          <Name>Coffee Machine</Name>

        </DisplayString>

        <DisplayString ElementID="CoffeeMachine.Filters.Enum">

          <Name>Filters</Name>

        </DisplayString>

        <DisplayString ElementID="CoffeeMachine.Drain.Enum">

          <Name>Drain</Name>

        </DisplayString>

        <DisplayString ElementID="CoffeeMachine.Drain.Pipe.Enum">

          <Name>Pipe</Name>

        </DisplayString>

        <DisplayString ElementID="CoffeeMachine.Drain.Fitting.Enum">

          <Name>Fitting</Name>

        </DisplayString>

        <DisplayString ElementID="CoffeeMachine.Coffee.Enum">

          <Name>Coffee</Name>

        </DisplayString>

        <DisplayString ElementID="CoffeeMachine.Coffee.OutofCoffee.Enum">

          <Name>Out of Coffee</Name>

        </DisplayString>

        <DisplayString ElementID="CoffeeMachine.Coffee.CoffeeTastesBad.Enum">

          <Name>Coffee Tastes Bad</Name>

        </DisplayString>

        <DisplayString ElementID="CoffeeMachine.Coffee.CoffeeTastesBad.TastesWateredDown.Enum">

          <Name>Tastes Watered Down</Name>

        </DisplayString>

        <DisplayString ElementID="CoffeeMachine.Coffee.CoffeeTastesBad.TooCold.Enum">

          <Name>Too Cold</Name>

        </DisplayString>

        <DisplayString ElementID="CoffeeMachine.Coffee.CoffeeTastesBad.DoesntTasteLikeStarbucks.Enum">

          <Name>Doesn’t Taste Like Starbucks&quot;</Name>

        </DisplayString>

        <DisplayString ElementID="CalledCoffeeMachineRepairTechnician.Enum">

          <Name>Called Coffee Machine Repair Technician</Name>

        </DisplayString>

        <DisplayString ElementID="MyCustomMP">

          <Name>My Custom MP</Name>

        </DisplayString>

      </DisplayStrings>

    </LanguagePack>

  </LanguagePacks>

</ManagementPack>

Which is then imported and displayed in the UI like this:

image

image

Once these are imported you can of course manage them in the main console Library/Lists view which will update the management pack.

 

 

 

image

image

 

You can get the tool from here:

http://cid-17faa48294add53f.skydrive.live.com/self.aspx/.Public/Tools/EnumCreator.xlsx

If you have any issues, suggestions, or other comments about the tool, please leave a comment.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Please help!

    I downloaded the EnumCreator excel file and tried to edit it for my organisations purposes. I unprotected the worksheet so that I could add more rows of categories and sub-categories. When I create an xml file it only lists the top ten rows of the excel sheet that were in the origninal protected excel file that I downloaded. Is this due to the worksheet being previously protected? What can I do to resolve this so that I can create one large excel sheet with all of my categories and sub-categories and generate an xml management pack from this excel sheet?

    Thanks,

    Kieran.

  • I'm getting failed validation error when I attempt to import the list copied from the Spread Sheet:

    "XSD verification failed for the management pack. [Line: 10, Position: 4]

    The element 'Manifest' has incomplete content. List of possible elements expected: 'References'.

    I am trying this with a simple list that a few values only have a child element... Any idea what it is I'm missing here?

    The test file:

    <ManagementPack  ContentReadable="true"

    SchemaVersion="1.1"

    OriginalSchemaVersion="1.1">

    <Manifest>

    <Identity>

    <ID>MyCustomMP</ID>

    <Version>1.0.0.0</Version>

    </Identity>

    <Name>My Custom MP</Name>

    </Manifest>

    <TypeDefinitions>

    <EntityTypes>

    <EnumerationTypes>

    <EnumerationValue ID="Fruit.Enum"

     Accessibility="Public"

     Ordinal="0"

     Parent=""/>

    <EnumerationValue ID="Fruit.Apple.Enum"

     Accessibility="Public"

     Ordinal="1"

     Parent="Fruit.Enum"/>

    <EnumerationValue ID="Fruit.Pear.Enum"

     Accessibility="Public"

     Ordinal="2"

     Parent="Fruit.Enum"/>

    <EnumerationValue ID="Fruit.Orange.Enum"

     Accessibility="Public"

     Ordinal="3"

     Parent="Fruit.Enum"/>

    <EnumerationValue ID="Fruit.Bananna.Enum"

     Accessibility="Public"

     Ordinal="4"

     Parent="Fruit.Enum"/>

    <EnumerationValue ID="Fruit.Plum.Enum"

     Accessibility="Public"

     Ordinal="5"

     Parent="Fruit.Enum"/>

    <EnumerationValue ID="Fruit.grape.Enum"

     Accessibility="Public"

     Ordinal="6"

     Parent="Fruit.Enum"/>

    <EnumerationValue ID="Grain.Enum"

     Accessibility="Public"

     Ordinal="7"

     Parent=""/>

    <EnumerationValue ID="Dairy.Enum"

     Accessibility="Public"

     Ordinal="8"

     Parent=""/>

    <EnumerationValue ID="Meat.Enum"

     Accessibility="Public"

     Ordinal="9"

     Parent=""/>

    <EnumerationValue ID="Coffee.Enum"

     Accessibility="Public"

     Ordinal="10"

     Parent=""/>

    <EnumerationValue ID="StrongerCoffee.Enum"

     Accessibility="Public"

     Ordinal="11"

     Parent=""/>

    </EnumerationTypes>

    </EntityTypes>

    </TypeDefinitions>

    <LanguagePacks>

    <LanguagePack ID="ENU"

     IsDefault="true">

    <DisplayStrings>

    <DisplayString ElementID="Fruit.Enum">

    <Name>Fruit</Name>

    </DisplayString>

    <DisplayString ElementID="Fruit.Apple.Enum">

    <Name>Apple</Name>

    </DisplayString>

    <DisplayString ElementID="Fruit.Pear.Enum">

    <Name>Pear</Name>

    </DisplayString>

    <DisplayString ElementID="Fruit.Orange.Enum">

    <Name>Orange</Name>

    </DisplayString>

    <DisplayString ElementID="Fruit.Bananna.Enum">

    <Name>Bananna</Name>

    </DisplayString>

    <DisplayString ElementID="Fruit.Plum.Enum">

    <Name>Plum</Name>

    </DisplayString>

    <DisplayString ElementID="Fruit.grape.Enum">

    <Name>grape</Name>

    </DisplayString>

    <DisplayString ElementID="Grain.Enum">

    <Name>Grain</Name>

    </DisplayString>

    <DisplayString ElementID="Dairy.Enum">

    <Name>Dairy</Name>

    </DisplayString>

    <DisplayString ElementID="Meat.Enum">

    <Name>Meat</Name>

    </DisplayString>

    <DisplayString ElementID="Coffee.Enum">

    <Name>Coffee</Name>

    </DisplayString>

    <DisplayString ElementID="StrongerCoffee.Enum">

    <Name>Stronger Coffee</Name>

    </DisplayString>

    <DisplayString ElementID="MyCustomMP">

    <Name>My Custom MP</Name>

    </DisplayString>

    </DisplayStrings>

    </LanguagePack>

    </LanguagePacks>

    </ManagementPack>

    Thanks!

  • That's interesting.  Try adding a <References/> in there like this:

    <Manifest>

    <Identity>

    <ID>MyCustomMP</ID>

    <Version>1.0.0.0</Version>

    </Identity>

    <Name>My Custom MP</Name>

    <References/>

    </Manifest>

    If that doesnt work let me know - I have another idea.

  • Hello,

    I have created enumeration values for incident classification this way (saved in unsealed MP) and it works well. So, we have plenty of incidents created using those incident classification enumerations. Now we would like to add some additional child values through console, but it gives me an error (when try to add child value under the child enumeration created in custom unsealed MP):

    ---------------------------------------------------------------

    - : Failed to validate item: Enum.e1f1122fedbb40bea3eb9123c79c300a

    Cannot add ManagementPackReference ManagementPack:[Name=CustomIncidentClassifications, KeyToken=, Version=1.0.0.6]. Unsealed management packs cannot be added as references. Specify a valid sealed management pack reference.

    ---------------------------------------------------------------

    or when I try to add child value under the enumeration created in custom unsealed MP:

    ---------------------------------------------------------------

    - Note:  The following information was gathered when the operation was attempted.  The information may appear cryptic but provides context for the error.  The application will continue to run.

    System.NullReferenceException: Object reference not set to an instance of an object.

      at Microsoft.EnterpriseManagement.ServiceManager.UI.Authoring.Enumeration.EnumerationForm.<>c__DisplayClass6.<SetSelectedItem>b__1(Object , EventArgs )

      at System.Windows.Controls.ItemContainerGenerator.SetStatus(GeneratorStatus value)

      at System.Windows.Controls.ItemContainerGenerator.Generator.System.IDisposable.Dispose()

      at System.Windows.Controls.VirtualizingStackPanel.MeasureOverride(Size constraint)

      at System.Windows.FrameworkElement.MeasureCore(Size availableSize)

      at System.Windows.UIElement.Measure(Size availableSize)

      at MS.Internal.Helper.MeasureElementWithSingleChild(UIElement element, Size constraint)

      at System.Windows.Controls.ItemsPresenter.MeasureOverride(Size constraint)

      at System.Windows.FrameworkElement.MeasureCore(Size availableSize)

      at System.Windows.UIElement.Measure(Size availableSize)

      at System.Windows.Controls.Grid.MeasureCell(Int32 cell, Boolean forceInfinityV)

      at System.Windows.Controls.Grid.MeasureCellsGroup(Int32 cellsHead, Size referenceSize, Boolean ignoreDesiredSizeU, Boolean forceInfinityV)

      at System.Windows.Controls.Grid.MeasureOverride(Size constraint)

      at System.Windows.FrameworkElement.MeasureCore(Size availableSize)

      at System.Windows.UIElement.Measure(Size availableSize)

      at System.Windows.ContextLayoutManager.UpdateLayout()

      at System.Windows.ContextLayoutManager.UpdateLayoutCallback(Object arg)

      at System.Windows.Media.MediaContext.FireInvokeOnRenderCallbacks()

      at System.Windows.Media.MediaContext.RenderMessageHandlerCore(Object resizedCompositionTarget)

      at System.Windows.Media.MediaContext.RenderMessageHandler(Object resizedCompositionTarget)

      at System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Boolean isSingleParameter)

      at System.Windows.Threading.ExceptionWrapper.TryCatchWhen(Object source, Delegate callback, Object args, Boolean isSingleParameter, Delegate catchHandler)

      at System.Windows.Threading.DispatcherOperation.InvokeImpl()

      at System.Threading.ExecutionContext.runTryCode(Object userData)

      at System.Runtime.CompilerServices.RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(TryCode code, CleanupCode backoutCode, Object userData)

      at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)

      at System.Windows.Threading.DispatcherOperation.Invoke()

      at System.Windows.Threading.Dispatcher.ProcessQueue()

      at System.Windows.Threading.Dispatcher.WndProcHook(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)

      at MS.Win32.HwndWrapper.WndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)

      at MS.Win32.HwndSubclass.DispatcherCallbackOperation(Object o)

      at System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Boolean isSingleParameter)

      at System.Windows.Threading.ExceptionWrapper.TryCatchWhen(Object source, Delegate callback, Object args, Boolean isSingleParameter, Delegate catchHandler)

      at System.Windows.Threading.Dispatcher.InvokeImpl(DispatcherPriority priority, TimeSpan timeout, Delegate method, Object args, Boolean isSingleParameter)

      at MS.Win32.HwndSubclass.SubclassWndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam)

      at MS.Win32.UnsafeNativeMethods.DispatchMessage(MSG& msg)

      at System.Windows.Threading.Dispatcher.TranslateAndDispatchMessage(MSG& msg)

      at System.Windows.Threading.Dispatcher.PushFrameImpl(DispatcherFrame frame)

      at System.Windows.Window.ShowHelper(Object booleanBox)

      at System.Windows.Window.Show()

      at System.Windows.Window.ShowDialog()

      at Microsoft.EnterpriseManagement.ServiceManager.UI.Authoring.Enumeration.EnumerationTaskHandler.ExecuteCommand(IList`1 nodes, NavigationModelNodeTask task, ICollection`1 parameters)

    ---------------------------------------------------------------

    When I try to add list values, no problem.

    How can I add more child values under the enumeration or child enumeration created in custom unsealed MP?

    Thanks!

  • @Ljubodrag Tišma -

    You'll either need to add them using EnumCreator.xlsx and then re-import the MP with the additional list items or you will need to take the MP XML file and manually add them in XML and then re-import the MP.

  • Thank you Travis.

    I see, the only way is to edit MP adding new enums.

  • Travis,

    Is it possible to enumerate a list based off the findings of a script instead of using this method? I would like to leverage powershell to pull values from Active Directory to enumerate a list of say departments. I would like to populate this list of departments by extracting the data from our user accounts "Department" field in AD.

  • @RDurb -

    Yes, there is a New-SCEnumeration cmdlet in SMLets (smlets.codeplex.com)

  • Awesome! Thanks for your prompt response :)

  • Why do we need to create the list manually in XML ? I found the current UI already allows the creation of the tree like structure of "Coffee Machine" as in the above example in the Incident classification List ?

  • @Wyatt -

    You don't *need* to use this approach to create enumeration values.  This approach is useful when you need to create tens, hundreds, or maybe even up into the thousands of enums.  Trying to that that in the UI would be very tedious and take a long time.

  • Will this work with the new 2012 product?

  • @Chris - yes it should work with 2012 just fine.

  • Hey Travis,

    First off, kudos on the EnumCreator tool.  It saves so much time in generating the XML for you.

    Secondly, I had a suggestion for the tool.  One thing I noticed was when I create a simple Level 1 list, the tool assigns the EnumerationValue ID by just adding “.Enum” to the end of the list item.  I ran into an issue where I was generating multiple lists and the value “BE” came up twice.  Since both list items were assigned the EnumerationValue ID of “BE.Enum”, the Authoring tool would error out and could no longer open the MP.

    It would be cool to see the EnumCreator tool assign the EnumerationValue ID as it is described in your previous post (blogs.technet.com/.../understanding-lists-enumerationvalues-ci-status-example.aspx) where the EnumerationValue ID is given the name of the Parent plus the item.  So my BE example from above would be “Business_Unit.BE.Enum” and “ISO_Country_Code.BE.Enum”.

    Thanks!

  • I am having trouble using the tool to create a new list.

    I downloaded and saved the tool. Then I took the following steps.

    1. Changed MP ID - CustomCMFunctionalDeptList

    2. Left MP version to 1.0.0.0

    3. MP name - Custom CM Functional DeptList

    4. Unprotected Sheet, and cleared the dependant MP info

    5. cleared all data from the table

    6. Added values for my new list starting at Level 1, populated level 1 and 2

    And the MP output remained blank.  But I found at the bottom of the EnumID and EnumParentID columns there is a dropdown, that if I select "none", it then populates the MP output cell.  

    So I copied the cell to notepad, saved as XML, "CustomCMFunctionalDeptList.xml" but when I try to import it I get the following error,

    '(shows a small box here)'is an unexpected token. The expected token is '='. Line 6, position 1.

    Any assistance would be appreciated, I really dont want to manually create this list. :)