trouble with calculated field

Question

I am experiencing a couple of problems with calculated field in document library.

Problem one: Calculated field gives me this error: The formula refers to a column that does not exist. Check the formula for spelling mistakes or change the non-existing column to an existing column.

What's the reason for this? I changed the name of the columns 4 days ago, can this be the reason for the problem i am experiencing? For example because of cache? If yes: how to flush cache in SharePoint online?

Problem two: One of the calculated field are suppose to look like this: =[metadata1]&"-"&[Metadata2]&"-"&[ID], but... for some reason the brackets disappears sporadically! Anyone have a clue what the reason for this is - and how to resolve?

Answer 1

ID is not a valid columnvalue to use in Calculated Formulas, it is not displayed in the available columns for a reason.

• It is 0 for newly created items as it gets its value from the database after all Formulas are evaluated

• You get the correct values (in all existing items) if you re-edit the Formula

• It is reset to 0 on Item update (I never investigated why)

Only way to get hold of that ID is to use a Workflow and save its value to another Column

---

The [fieldname] notation is only required for fieldnames containing spaces.

Answer 2

Problem one: Use Field static name instead of display name, you could get field static name by going to list settings(or rest api /sites/Developer/_api/web/lists/getbytitle('library')/fields), click the field, you can get field static name in URL.(I could reproduce the issue when updating a field display name in SharePoint online)

Problem two:

The brackets is not necessary to identify a field when using static name, you can remove it.

Answer 3

I solved the document id issue by enabling the Document ID service. The DIS gives us a "three part value" which looks like this: [Prefix Text]-[Library Number]-[Item ID]. To extract the document id, just make a calculated field that contains the following:

=MID([Document ID Value],SEARCH("-",[Document ID Value],SEARCH("-",[Document ID Value])+1)+1,4)