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 called 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.
A 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 1 | Company 2 | Company 3 | Company 4 | Company 5 | Company 6 | Company 7 | Company 8 | Total Companies | |
| Company Grade | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
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 1 | Company 2 | Company 3 | Company 4 | Company 5 | Company 6 | Company 7 | Company 8 | Total Companies | |
| Company Class Status | Class A | Class B | Class B | Class B | Class B | Class B | Class B | Class B | |
| Company Grade | 10 | 7 | 4 | 6 | 2 | 6 | 8 | 5 | 6 |
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:
| Parent | Code | City Area (sq mi) | |
| New York | 18 | ||
| Boston | 15 | ||
| Chicago | 12 | ||
| Washington | 8 |
A 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 York | Boston | Chicago | Washington | |
| City Population (m) | 8.6 | 3.4 | 2.7 | 5.3 |
| City Land Area (sq mls) | 18 | 15 | 12 | 8 |
In this sort of case, when you set City Stats list as a production list, you are not blocked.
