Work with Numbered Lists
In this section, you can learn how to:
- Create a numbered list
- Set up display names for numbered lists
- Use duplicate names in numbered lists
- Insert items in a numbered list
- Delete items in a numbered list
- Use numbered lists in composite hierarchies
- Import a numbered list based on codes or names
- Import Codes into a Numbered List
- Import into a module containing a numbered list
- Import into a module containing a numbered list - map by name workaround
- Use SELECT, ITEM, FINDITEM, and NAME functions with numbered lists
- Use LOOKUP and SUM functions with numbered lists
Numbered lists have Codes and Properties, just like any other list.
When you create a numbered list, each item you add to it is automatically assigned a unique ID number. The system-generated ID number is totally independent of any Code manually assigned to the item.
- On the Model Settings tab, create a list.
- In Model Settings click General Lists.
- Select a list.
- Select the Numbered checkbox. A confirmation dialog displays, reminding you that you that numbered lists cannot contain totals.
- Click Cancel if you don't want to proceed with the conversion to a numbered list.
- Click OK to complete the conversion.
If the list you converted contained items, the names of items will have been replaced with numbers and you can now insert items in a numbered list. If the list you converted was empty, you can now open the new numbered list and add items to it.
Configure a numbered list
- Select a numbered list in General Lists on the Model Settings tab.
- On the Configure tab, enter attributes for the numbered list.
- Click Apply.
- Select the Tree View tab and click Insert. An Insert dialog displays — you can add between 1 and 1,000 items
- Click OK. The items are added to the numbered list.
Although items in a numbered list are assigned a unique number, they can also have a display name to make identification of the item easier. Names for each item can be displayed by adding a Property to the list and then designating that list property as the Display Name Text
- If you use a Text-formatted list property, enter the text to display as the name for each item.
- If you use a Text-formatted list property, you can apply a formula to the property using functions that yield text results. This means you can concatenate the text from line items and other property values to derive a composite name that will display for each item.
- If you use a List-formatted list property, you can select property values to display as the name for each item.
Here, you can learn how to:
To define a Display Name Property you must first create the Property and then designate it as the Display Name Property.
Create a Display Name Property
- Select the list on the General Listspage in Model Settings.
- Select the Properties tab.
- Click Insert. The Insert into Properties dialog displays.
- Enter a display name property.
- Click OK. The display name property is added to the Properties tab.
- To format the display name property, click in the Format field.
- Select the format to use for the display name property.
Designate the Property as the Display Name Text for numbered list items
- On the Model Settings tab, inGeneral Lists, select the numbered list.
- Use the dropdown list in the relevant field in the Display Name Property column to select the property to use for the display name. (You may have to scroll the General Lists grid to the right to see the Display Name Property column.)
- Open the numbered list again and select the Grid View tab. All properties configured for the list are displayed.
- Under the property designated as the Display Name Property, enter the text you want to display for each item in the list.
- Open the Tree View tab. The text is shown here for each list item instead of the system generated unique number.
You can use a List-formatted Property as a Display Name Property for a numbered list. To do this, select the display name text from the available values from the list used for the list formatted property.
The Display Name Property will appear as the label for rows, columns, pages and in List-formats. When you use a Text-fomatted Display Name Property, you can create a formula to generate the display name text for list items. The display name could be a concatenation of line items or other Properties, provided they are converted to Text format using text manipulation functions — such as NAME, LEFT, RIGHT, etc.
Here are some examples of how to generate composite display names for numbered lists using formulas for display name properties.
In this example, the display name uses a combination of the unique ID number and the customer name:
This formula builds a composite display name text:
- "Opportunity" a hard-coded text string, concatenated with...
- NAME(ITEM(#Opportunities) which yields the numbered list unique ID number as text string by using the ITEM function nested under the NAME function, concatenated with....
- " " a hard-coded white space, concatenated with...
- The value of Which Customer?, itself a text formatted property configured for the #Opportunities list that uses a formula—its value is what has been entered for the text formatted Customer line item in the Opportunities module:
When the #Opportunities numbered list is used as a dimension in a module—here as rows on the Opportunities module — the appropriate display name for each item is built according to the formula applied to the text formatted display name property:
Example 2 — Combine Key Properties
In this second example, the display name uses a combination of key properties by combining the product name and customer name:
This formula builds a composite display name text.
- NAME(Which Product?) which yields a text string from the value of this list formatted property using the Product list configured for the #Opportunities numbered list:
...is concatenated with...
- " " a hard-coded white space, concatenated with...
- The value of Which Customer?, itself a text formatted property configured for the #Opportunities list that uses a formula — its value is what has been entered for the text formatted Customer line item in the Opportunities module — see Example 1 above.
When the #Opportunities numbered list is used as a dimension in a module — here as rows on the Opportunities module — the appropriate display name for each item is built according to the formula applied to the text formatted display name property:
Although names cannot generally be repeated within an Anaplan list, there is an exception in the case of display names in numbered lists — the display name does not have to be unique and can appear in more than one place, even within the same subtotal. The hidden unique ID number ensures it has unique identifier. The Code, if there is one, must also be unique for each item in a numbered list. The Code will be used for import and export routines.
In this project planning example, the display name is the name of the people assigned to each project. A numbered list named #Resources has a parent hierarchy of #Projects. Both the child and the parent hierarchy are numbered lists.
In the example, Emily Ruby is working on 3 different projects at once so her name needs to appear within each project subtotal. Where there are duplicate display names in a numbered list, the unique #ID number for each item is shown in the Tree View tab of the list.
The system stores #ID numbers that have been created in the past and assigns the next unused #ID number. If an item has been deleted from a numbered list, the #ID number is not reused. This means that #ID numbers are unique within a numbered list and stay with an item for life.
- On the Edit menu, click Insert.
- Select how many items to insert. The system will generate a unique auto-incrementing #ID number for each.
- Choose where to insert the new items, by selecting either Before, After, Start or End.
- Click OK. The new items are inserted.
- Select the Properties used in the display name, by entering the Properties of the list.
See also:Create Action
- On the Edit menu, click Delete.
- Select the items that you want to delete.
This is the most common use of numbered lists. A composite hierarchy can be used with a numbered list rolling up into a parent. The parent can be daisy-chained, so that a parent can have a parent that is also a numbered list. As a rule, you should try to design the model such that the numbered lists are used only in the lower levels of a composite hierarchy.
See Model Building for more information on good practice when building hierarchies out of Numbered Lists.
- In General Lists select the list you want to work with and then click the Configure tab.
- Select a Parent Hierarchy from the dropdown list. This can be a normal list or another numbered list.
In the above example, a numbered list named #Resources has a parent hierarchy numbered list named #Projects, which has a parent hierarchy normal list called Organization. (The # prefix is merely a convention to identify numbered lists. You could use your own prefix to show that it is a numbered list or omit it entirely.)
It's best to map the primary key to the code, and to leave the name unmapped. For numbered lists, the ‘name' is actually the system-generated #ID number; as you cannot control this, it's not a good idea to use it for mapping. The display name can be ambiguous, so can't be used for mapping on import.
- Go to Model Settings > General Lists and open the numbered list.
- Click Import and upload the source file, or connect to a list or module within Anaplan.
- Map the primary key to the code.
Codes must not start with # to prevent conflict with system-generated IDs.
If you want to generate new items, then you must match on Code only (and not the Name). For a numbered list, the Name is the unique system generated #ID number, not the display name. If you have mapped on Code and not the Name and the system finds a new Code, then it will insert a new item, assign it the next new #ID number, and import any properties and parents that have been mapped in the import definition.
- If you want to update properties or parents of existing items, you can match on the Code, the Name (ID number), or both.
- In the import definition, map the #ID in the source file to the Name. The name of a numbered list is the ID number.
- Map the code in the source file to the Code of the numbered list. The code and the list will update, provided that the code in the numbered list was blank in the first place.
It's good practice to import the code when initially creating the new item, although codes can be imported at a later date. Codes can only be imported into items that have blank codes, so any codes must be cleared out first. Once this has been done, codes can be updated using an import.
The source file must contain the correct #ID number; you can't have the Anaplan-generated #ID number in an external system, so you may need to export the numbered list and all the #IDs and properties first, and then use an external tool to get the source file in the correct format, containing both the external codes and the corresponding Anaplan #ID numbers.
- With the grid open, on the Data menu, click Import. Choose a file to upload or another Anaplan module and go through the import wizard in the usual way.
- When mapping into a dimension that's a numbered list, #IDs or codes must be used. Codes are the most reliable method for importing from external systems. You can't map on the display name directly, however you can set things up to use a workaround. See next section.
Set up a line item in a source module which will enable the mapping of numbered list items when importing into a module containing a numbered list:
- Create a new line item in your source module containing the code of each item in the numbered list.
- Populate this new line item using the formula CODE(ITEM('List')).
- Pivot the module view so that line items are columns.
- Save this as a view.
- In the target or import destination module, select Data>Import>Connect to Anaplan Model>Saved View and select your saved view in the source module from the list. Now, in the import dialog box you will be able to map the new line item containing the code to the numbered list items.
When working with numbered lists, the SELECT and ITEM functions require the # ID number. The display name could be ambiguous, so the name (# ID number) must be used instead. For this reason, when you are building a formula using these functions, it is recommended that you use point-and-click on specific Numbered List items. The system will then automatically enter the unique #ID number of the Numbered List item into the formula.
Sales.Gross Sales[SELECT: Products.'#12345']
Sales Details.Amount[SELECT: Transactions.'#2']
Similarly, ITEM function refers to the underlying unique name (the #ID) and not the display name.
IF ITEM(Transactions) = Transactions.'#3' THEN 1 ELSE 0
The FINDITEM function is used to find an item in a list based on a text input. For Numbered Lists, the text must be either the code or the # ID number. You cannot find an item based on the display name as this may be ambiguous.
Result = FINDITEM(Transactions, Transaction ID)
The NAME function will return the unique name (the #ID) and not the display name.
Result = NAME(ITEM(Transactions))
Here, the display name has been set to be prefixed by the word ‘Transaction'; the NAME function always returns the #ID and not the display name.
If you have a module containing a numbered list, you will need a LOOKUP function to pull data in, and a SUM function to pull data out. For example, suppose you have a numbered list named #Resources that has a list-formatted property of People; to pull data into the Project Days module, which has a dimension of #Resources, use a LOOKUP function.
Pull Data into Modules with Numbered Lists
Use the formula:
Days Available = People Details.Days Available[LOOKUP: #Resources.People]
Pull Data out of Modules with Numbered Lists
To pull data out of the Project Days module and into a module that has People as a normal dimension, use a SUM function:
Days Booked = Project Days.Days[SUM: #Resources.People]
List formatted Property for use with SUM and LOOKUP
The numbered list will typically have a list-formatted property used for both the SUM and LOOKUP functions. The same property can also be used as the display name.