Problem with Calculated field in Sharepoint
Hi,I have a form where I need to include a calculated field. I made a very simple test using a calculated field where the formula is "=ID". I just want to confirm that the value I'm getting in the view is the same as the ID of the record in the list.I press NEW and create a new record. Then when I see the new record in the view, the first column is the ID (let's say 11), then some proper fields form the form and finally the calculated field that instead of showing me the value 11, it shows 0. Then I follow the following steps:1. I go to Settings, List Settings 2. Click on the calculated field to edit it (only edit it with no modifications) and press on OK (no modifications to the calculated field at all)3. Go back to the list of records and now the calculated value shows me the value 11Anyone can help me to identify with the calculated field does not shows 11 automatically after the record is saved?Thanks!Zek
January 15th, 2010 8:49pm

The reason you are seeing a "0" when you initially save the item is that there is no value for "ID" yet. A quick solution might be to create a workflow that fires on all "NEW" items and assigns the value of ID from the last item created.Jeff DeVerter, MCSERackspaceblog:http://www.social-point.comtwitter: http://www.twitter.com/jdeverter
Free Windows Admin Tool Kit Click here and download it now
January 15th, 2010 9:58pm

I would be willing to bet that the new id is getting evaluated and populated after your calculated field. Thus at the time your calculated field is being evaluated the new id does not yet exist. I doubt that you would be able to use ID in a calculation unless you do it in a procedure which fires AFTER the record has been created.
January 15th, 2010 10:45pm

Thanks!My question there is that actually the original calculated field is a contatenation of several fields from the list and the only field I'm not seeing is the ID.Is a sharepoint limitation that the ID is calculated after any calculated field?Zek
Free Windows Admin Tool Kit Click here and download it now
January 15th, 2010 11:21pm

Its an order of operations thing. One common way to get around this is to use a SP Designer workflow that runs on creation to populate a column. SharePoint MVP | Developer | Administrator | Speaker-- Twitter -- Blog - http://nextconnect.blogspot.com
January 16th, 2010 12:30am

Thanks Jeff, My question about your answer is if there no value for the ID yet why when I save the item I see a value in the ID column and 0 in the calculated value? Thanks! Zek Hi Zek, The reason is that, when you are creating a new item, the ID doesn't exist. It only has an ID value after the item is saved in the database. And when calculation of the calculated column is performed, the ID doesn't exist yet. If you open the list and edit it or just click on OK without making any changes, you'll probably see the ID value in your calculated column, because at this point the ID column has a value already. You'll probably find the following useful. Take a look at the "Using Column references in a formula" section to see what you can/cannot reference to in a calculated column; http://office.microsoft.com/en-us/help/HA101215881033.aspx Pman http://www.pmansLab.com/
Free Windows Admin Tool Kit Click here and download it now
January 16th, 2010 5:26am

Hi Zek, Don't post duplicates into different forums here. You've posted the same question twice: http://social.technet.microsoft.com/Forums/en-US/sharepointgeneral/thread/cc83e093-e62b-454f-800f-483afe7ee49d If you can't find your post later in the forum, usually that means that it's been moved to the appropriate forum. Check the "My Threads" page or your email for an alert in those cases. I posted duplicates unintentionally on my first day here because I couldn't find it and got Warning from Mike Walsh. Haven't forgotten since then ;=) Several people responded to both of these threads. Everyone gave you the same answer. Hopefully one of the moderators can merge these 2 threads. Pman http://www.pmansLab.com/
January 16th, 2010 5:45am

Thanks pl7626 for your help in proposing the good posts of other people as answers. This time I was tempted to remove your mark and mark your post instead, but Jeff *was* first with the reason even if you nicely extended on it (and I rarely mark more than one post in a thread). Mike (Moderator)FAQ sites: (SP 2010) http://wssv4faq.mindsharp.com; (v3) http://wssv3faq.mindsharp.com and (WSS 2.0) http://wssv2faq.mindsharp.com Complete Book Lists (incl. foreign language) on each site.
Free Windows Admin Tool Kit Click here and download it now
January 16th, 2010 2:38pm

Thanks pl7626!!! It is very clearNow...is there any workaround to generate an ID in a calculated field that should not exists before without using workflows? I have no access to SPD and no option to generate workflows.Basically I need to generate an unique ID in a calculated value.Thanks!Zek
January 17th, 2010 5:33pm

Thanks pl7626!!! It is very clear Now...is there any workaround to generate an ID in a calculated field that should not exists before without using workflows? I have no access to SPD and no option to generate workflows. Basically I need to generate an unique ID in a calculated value. Thanks! Zek It depends. There's no option in SharePoint to create unique value, but I believe you can create it using Calculated column. However, you already have an Unique column, the ID column. Why do you need to create another one? Pman http://www.pmansLab.com/
Free Windows Admin Tool Kit Click here and download it now
January 17th, 2010 8:36pm

Good question :)The reason is becasue I need to create a calculated field with a concatenation of several fields including an unique value in the concatenation and I imagen that unique value as the ID autogenerated by the list, but if I can't use the ID becasue I don't get it automtically becasue was not generated at the moment of the generation of the concatenated string I need to find a workaround....that is why I need to use an unique value in the concatenated string.Zek
January 17th, 2010 11:26pm

By the way. All the posts about items not having an ID yet, etc, don't apply to this BUG. I created a list with 3 items. And a calculated column that contained ID = "Item ID =" & ID The calc column shows up fine.. Item ID = 1, Item ID = 2, Item ID = 3.. etc Then I edit the list items and the calculated column stops working after a couple edits. I don't add or delete list items and the item IDs are always there (I added the ID column and I monitored SQL to see if the fields were changed or updated.. they aren't). To fix, I go to list settings, I open my calc column, press OK to save it again and it starts working again. Somehow the formula or definition of the column gets corrupt. This is an all out bug. I wish Microsoft would address this as a bug because it is. Considering that Item ID is one of the best ways for us to access the item itself, it has to be reliable in a calculated column. So..... that being said, anyone else have any ideas about why the calc column just breaks and you can press OK on the definition to get it working again? In your answer you can't talk about new items ;) At this point we are using Nintex, so I am just going to create a simple workflow on new item creation to copy the ID to a new column, but what a crappy workaround. I have confirmed this behavior in MOSS 2007 and in the newest 2010 release.
Free Windows Admin Tool Kit Click here and download it now
January 27th, 2011 12:43am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics