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.
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.
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 a property on a production list, and not to specific items in the list, is allowed.
With this type of formula reference you can:
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.
Disclaimer
We update Anapedia content regularly to provide the most up-to-date instructions.