Unable to run update query on linked Sharepoint list

[Isaac]

I have linked a sharepoint list. It has a field MissingDataPoints, which is a formula of essentially the mathematic addition of other fields, like field1+field2+field3 (about 9 fields). Works fine on Sharepoint.

However, it only works for new items. So I have linked the sharepoint list to an Access database, along with a linked Excel spreadsheet (where I have a list of the ID's, along with a backfill of all of the MissingDataPoints values that I want sharepoint to reflect).

When joining my Sharepoint linked table, and my Backfill table on the ID, and trying to run an update query to update Sharepoint.MissingDataItems, to Backfill.MissingDataItems, I get this message: "You cannot update this field because the value you're trying to apply is not valid or would break a data integrity rule. Please correct and try again".

Is this basically saying that I cannot update the sharepoint list field, period, because that field is a formula? Is there a way around it? I am trying to do a one time backfill of some information.

 

[theDBguy]

Hi. If you're using a calculated column in a SharePoint List, then you cannot enter or change the data in that column. If the information in the column is outdated, try modifying one of the columns involved in the formula, and it should update the result of the formula.

 

[Isaac]

I think that each of the 9 columns involved in the calculation is also a calculation, i.e., "if some other field is blank, 1, else 0". (and there are 9 of these that then get added up).

So sharepoint will only update these formulas on pre-existing records if those pre-existing records are edited and saved?

 

[theDBguy]

I think that each of the 9 columns involved in the calculation is also a calculation, i.e., "if some other field is blank, 1, else 0". (and there are 9 of these that then get added up).

So sharepoint will only update these formulas on pre-existing records if those pre-existing records are edited and saved?

That's correct. SharePoint will only update a calculated column when the List item is updated.

 

[Isaac]

ok I found out that the Missing Data Points field is just a text box - no calc in it. there are rules in other fields that set that field to a value, though.

 

[theDBguy]

ok I found out that the Missing Data Points field is just a text box - no calc in it. there are rules in other fields that set that field to a value, though.

What do you mean by "rules?" Where did you find them?