none
Weird bug on calculated column for a lookup RRS feed

  • Question

  • Hi guys,

    Context : I use SP 2013 Standard. I have 2 lists on my site. I created a lookup from  list_A to list_B, but as I want only the active items of list_A, I created a calculated column [Active Item] with =IF([Status]="Active";[Title];"")

    I've done that many times already (on other servers), and it always worked great.

    Bug : When I update any value in an inactive item from list_A, my calculated column is updated, and the result showing is -214428456 !!  (I will provide a screenshot when my account is verified)

    The first 2 lines are inactive and suffer the bug, the next 4 are inactive and correctly calculated (but have NOT been modified since the [Active Item] column was created, the last 1 is active)

    NB : If I go to list settings, open the [Active Item] column and save without changing anything, the inactive item are calculated back to the <blank> value.

    Does anybody seen that already ?

    Thanks for help !

    Monday, July 8, 2019 1:00 PM

All replies

  • Hi, Spherea,

    I have voted on your post. Try to upload your screenshot. 

    First, in if function you should use comma instead of semicolon. I assume it could be a typo but double check it. 

    https://support.office.com/en-us/article/if-function-7025be14-5665-43d0-af20-8293d1fe9d3a

    I have created several columns with the formula =IF(Status="Active",Title,""). It works pretty fine. What is the data type of your title column and the data type returned from your calculated column? 

    Try to create a new list with the same settings to see if you can re-produce the issue. 

    Best Regards

    Jerry


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Tuesday, July 9, 2019 2:22 AM
  • Hi Jerry,

    thanks for your reply. for some reason I still cant post a picture, message box says "until your account is verified" whatever it means :)

    anyway. I believe my formula is correct (I use ; instead of , because of french regional settings).

    A few tests done :

    - same behaviour on all sites I have rights on

    - And I have the same error if the formula is [Cell]="". (with no IF() )

    By wandering on technet I found I have the same symptoms as an old issue (from 2014) : social.msdn.microsoft.com/Forums/sharepoint/en-US/2381d90e-4055-4204-9537-edacf44262e7/calculated-column-filter-problem?forum=sharepointcustomization

    (you will have to add the https:// before as I can't post links until my account is verified) 

    Could it be linked to my own issue ? Do you know if any fix exist ?

    Thanks again for your help.

    Olivier

    Tuesday, July 9, 2019 3:30 PM
  • Hi, Oliver,

    For verification, you will need to go here:https://social.msdn.microsoft.com/Forums/en-US/dc4002e4-e3de-4b1e-9a97-3702387886cc/verify-account-42?forum=reportabug

    I make some tests with new lists in my SP13 environment. Still got no way to reproduce your issue. For confirmation, " same behaviour on all sites I have rights on" means whatever list you create, a calculated column will get strange number when the formula is ="" ?  Do other users encounter the same issue?

    Best Regards

    Jerry


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Thursday, July 11, 2019 7:03 AM
  • Hi Jerry,

    • whatever list you create, a calculated column will get strange number when the formula is ="" ? >> Yes : Even in my Personal site where I have full rights.
    • Do other users encounter the same issue? >> You mean, if someone else create a new column ? Yes

    I checked on other browsers, same behaviour.

    I exported the list in excel, I get the weird values as well.

    Just to clarify the way the bug appears :
    1. I create a calculated column [Test]="" 
    2. just after creation, the column is correctly calculated, all [Test] values are blank
    3. I update 1 item (e.g. change title or whatever) -- same behaviour if I change through quickedit or via the form
    4. for the updated item only, the [Test] column = -214428456 (always the same value)
    5. If I change more items they all change their [Test] values to -2144284566.
    6. If I go back to list settings and open/save the [Test] column, all [Test] are back to blank

    Friday, July 12, 2019 8:13 AM
  • Also, all calculated columns behave weirdly, even for non blank results.

    For instance, I have a [Status] column with Active/Closed items.
    1. initially, the filter on my column shows 2 options : {Active;Closed}
    2. If I update an active item (e.g. change the title) I now have 3 column filter options {Active;Closed;Active}
    3. If I update a closed item I now have 4 column filter options {Active;Closed;Active,Closed} (in this order)
    4. If I create a view with built-in filter on [Status] is equal to "Active" I get both 'values' on the list {Active;Active}. 

    Did you have a look to the bug report I listed in my first reply ? Do you know how I can check whether it's solved ?

    Thanks again for your time.


    • Edited by OF_SPHEREA Friday, July 12, 2019 9:27 AM
    Friday, July 12, 2019 9:27 AM
  • Hi, Oliver,

    It seems to be a strange bug. Try to update your environment to the latest version. If that does not help. Please open a ticket for it. And we are looking forward to your reply about the fix. 

    Best Regards

    Jerry


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Tuesday, July 16, 2019 8:14 AM
  • Hi Jerry,

    I checked with my admin. He admitted that there are probably (many) updates to pass on the server. Hopefully, he will have time to perform it through the summer.

    I will let you know.

    Cheers

    Olivier

    Wednesday, July 17, 2019 1:20 PM
  • Hi Spherea,

    Calculated columns works crazy in SharePoint, there is no theory why it works and why it doesn't work. In my experience I have seen this issue many times and at last I got rid of that and implemented a CSOM as a content editor Webpart and added it in newform and editform. Please see the below article to achieve the same. In this article he is explaining about validation similarly you can populate the value to required field.

    http://blog.riccardocelesti.it/using-presaveitem-to-add-custom-validation-to-sharepoint-list-form/

    Enjoy the SharePoint !!

    Best Regards,
    Krishna

    Please remember to mark the replies as answers if they helped.

    Thursday, July 18, 2019 3:47 PM
  • Hi Krishna,

    thanks for your inputs. I have the feeling that the issue/solution you are raising is about data validation (as I understand it : making sure that field X is compliant to some rule before saving, either in newform or editform).

    Although validation rules do use formula syntax (just like calculated columns), I don't have validation rules in my list. I am not sure how the article can solve my issue. I keep it somewhere in my bookmarks for when I'll have to deal with validation though.

    Cheers

    Olivier

    Thursday, July 18, 2019 3:55 PM
  • No Oliver,

    What I am proposing here is.... as advised in the article you can also do the formula for your calculation. so that you need to go and edit the calculated column every time. The way how he is validating the field you can calculate and populate on to required field.

    May be I will write that sample code and share it to you.

    would that helps my friend?

    Enjoy the SharePoint !!

    Best Regards,
    Krishna

    Please remember to mark the replies as answers if they helped.


    Thursday, July 18, 2019 5:20 PM
  • Hi Krishna,

    Ah ok I understand. I will kep this in mind if my admin team updates dont solve the issues.

    Thanks for your inputs.

    Tuesday, July 23, 2019 7:48 AM
  • Hi SPherea,

    If the reply helps please mark it as answered.

    Regards,

    Krishna

    Tuesday, August 13, 2019 2:16 PM