using calculated field in calculated field

Question

I have field1, field2, field3, calcfield1, calcfield2, complexCalcField1

Calcfield1 and Calcfield2 use field1, field2 and field3 for its values. ComplexCalcField1 uses Calcfield1 and Calcfield2 for its value.

I know that calcfield1 and calcfield2 in complexcalcfield1 can be replaced with the formulas for calcfield1 and calcfield2 but this just makes complexcalcfield1 formula difficult to read and maintain. Instead using calculated fields, ie calcfield1 and calcfield2, in complexcalcfield1 makes the formula easier to read, understand, and maintain.

This so called nested calculated fields works fine in list fields. But when I try to use complexcalcfield1 in a Content Type Hub or at the Site Collection level, it is saved, but later on when I see them, calcfield1 and calcfield2 are changed and replaced with #NAME?-#NAME?=.

Does anyone have any idea why this does work in list level and not in Content Type Hub level or Site Collection level?

My guess is somehow for complexcalcfield1 to know/calculate its value, it must know value for calcfield1 and calcfield2. However, the actual order in which these formulas should be calculated is first to calculate calcfield1 and calcfield2 and then go to calculate complexcalcfield1. But if the order is reverse as in trying to first calculate complexcalcfield1 before calculating calcfield1 calcfield2 then there will be an error.

Have you had such problem before? How can I solve this?

Answer 1

There are at least 2 more things that you need to take into consideration when using calculated columns with content types:

1. Each column referred to in the calculated column must also be a site column added to the content type - The point here is that you cannot do things like reference a list level column in a content type level column because once you use that content type on another list the formula will break because it does not have the list level column from the original list. That also means that you cannot reference a site column that is not in the content type because that site column may not exist when you add the content type to a new list

2. Each column referred to in the calculated column must have its original display name as when it was created as a site column

I've tested your problem by creating 6 site columns for the fields that you mention as well as creating a single content type entitled Test Content Type. I then created a Custom List entitled Test List, added the Test Content Type to the list, and then added a couple rows with test values. All of the calculated columns worked and when I went to check their formulas every one was correct. For all of the columns I selected "Single Line of Text" including as the "data type returned" for the calculated columns. By following the 2 rules list above I was able to get your example working.