To prevent the creation of formulas based on unpredictable data, formulas can only reference a top-level item in a production list, and not to individual list items. This is referred to as formula reference protection.

Formula reference protection for production lists is imposed in both directions.

Formula reference to production lists

When you set a list as a production list, any attempt to create a formula that references an item in the list is blocked.

In this example, the Companies list is set as a production list.

Company Grade module uses the Companies list as a dimension on columns and has a single number formatted Company Grade line item. 

A formula for the Company Grade line item is created. The formula has a conditional that embeds the ITEM function, which references items in the Companies list to ensure only Company 1 gets a grade of 1.

Company Grade formula:

IF ITEM(Companies) = Companies.'Company 1' THEN 1 Else 2

Company Grade module:


Company 1Company 2Company 3Company 4Company 5Company 6Company 7Company 8Total Companies
Company Grade000000000

When you try to save the formula, you are blocked and an error message appears. You must unset the Companies list as a production list to build the module with this formula.

Set lists with formula reference as production lists

If any item in the list you attempt to set as a production list is already referenced in a formula, then you won't be able to set the list as a production list.

In this example, a Company Status module uses the Companies list as a dimension on columns. The Company Class Status line item is list formatted on the Company Class list and uses a conditional formula that embeds an ITEM function to refer to the Companies items. The formula ensures that only Company 1 gets a Class A status.

Company Class Status formula:

IF ITEM(Companies) = Companies.'Company 1' THEN Company Class.Class A Else Company Class.Class B

Company Status module:


Company 1Company 2Company 3Company 4Company 5Company 6Company 7Company 8Total Companies
Company Class StatusClass AClass BClass BClass BClass BClass BClass BClass B
Company Grade1074626856

If you try to set the Companies list as a production list, you are blocked. You must remove all formula references to list items before you set the list as a production list.

Formula reference to list property allowed

Formula reference to a property on a production list, and not to specific items in the list, is allowed. 

With this type of formula reference you can:

  • Create a formula reference to a production list.
  • Set lists that already have this formula reference as production lists.

In this example, a City Stats list has a numeric City Area (sq mi) property.

City Stats list in grid view:


ParentCodeCity Area (sq mi)
New York

18
Boston

15
Chicago

12
Washington

8

City Statistics module uses the City Stats list as a dimension on columns. The module has a City Land Area (sq mi) numeric line item, which uses a formula to obtain the City Area (sq mi) list property values for each list item.

City Land Area (sq mi) formula:

City Stats List.'City Area (sq mls)'

City Statistics module:


New YorkBostonChicagoWashington
City Population (m)8.63.42.75.3
City Land Area (sq mls)1815128

In this sort of case, when you set City Stats list as a production list, you are not blocked.

Disclaimer

We update Anapedia regularly to provide the most up-to-date instructions.