To prevent the creation of formulas based on unpredictable data, formulas can only make reference to the top-level item in a production list, 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 Existing Production Lists
When you have set a list as a production list, any attempt to create a formula that makes reference to an item in the list will be 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 Grading line item. If you try to create a formula for the Company Grading value—here a conditional that embeds the ITEM function which references items in the Companies list to ensure only Company 1 gets a grading of 1:
When you try to save the formula, you'll be blocked and an error message appears. You must unset the Companies list as a production list, if you want to build the module using this formula.
Setting Existing Lists with Formula Reference as Production Lists
If any item in the list you're attempting 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:
If you try to set the Companies list as a production list, you'll be blocked. You must remove all existing formula references to list items before setting the list as a production list.
Formula Reference to List Property Allowed
Formula reference to a property on a production list, i.e. not to specific items in the list, is allowed:
- You can create this kind of formula reference to existing production lists.
- You can set lists that already have this sort of formula reference as production lists.
In this example, a City Stats List module has a numeric City Area (sq mi) property:
A City Statistics module that uses the City Stats List as a dimension on columns has a City Land Area (sq mi) numeric line item that uses a formula to obtain the City Area (sq mi) list property values for each list item:
In this sort of case, when you set City Stats List as a production list, you won't be blocked.