locked
More than one record in MSP_PROJ_CUSTOM_FIELD_VALUES table for a lookup field that allows only one selection RRS feed

  • Question

  • I have a strange situation.  I have a lookup field and supporting table, the field is designed to receive only one value, not a multiple selection.  In the table MSP_PROJ_CUSTOM_FIELD_VALUES, for a specific project, for that custom field, for the value selected, I have two records, each with its own Custom Value UID and date entered.

    I am aware for a field that does allow for multiple selection the table MSP_PROJ_CUSTOM_FIELD_VALUES will have rows for each selection. However, I do not believe this table is designed to store revisions of selections for a single selection field.

    I'm not sure how this happened, but I need to get rid of one of the rows.

    My question is, can I delete one of the rows from the table, or are there other related tables to contend with?



    • Edited by bg_farmer Thursday, January 12, 2012 2:44 PM
    Thursday, January 12, 2012 2:43 PM

Answers

  • I did contact Microsoft about this, and I was told that it IS a bug from the December update, and there is not a definitive time when it will get fixed.  They did send me a SQL script to find, and remove, the duplicate values.  I'm not comfortable posting the whole script, but here are the two primary queries used to identify duplicate values and how they make a determination of which value to keep:

    Here’s the portion that checks for duplicates.

    SELECT top 1 COUNT (*) TOTALCOUNT FROM MSP_PROJ_CUSTOM_FIELD_VALUES AS CFV

    INNER JOIN MSP_PROJECTS AS MP ON CFV.PROJ_UID=MP.PROJ_UID

    INNER JOIN MSP_CUSTOM_FIELDS_PUBLISHED_VIEW AS  CFPV

    ON CFV.MD_PROP_UID=CFPV.MD_PROP_UID

    INNER JOIN MSP_CUSTOM_FIELDS AS CF

    ON CFV.MD_PROP_UID = CF.MD_PROP_UID

    WHERE CF.MD_PROP_MAX_VALUES=1

     

    GROUP BY CFV.PROJ_UID,MP.PROJ_NAME, CFV.MD_PROP_UID, CFPV.MD_PROP_NAME HAVING COUNT (*)  >1

    ORDER BY TOTALCOUNT DESC

    And this one decides which value to retain.

    -- Return only duplicate records and return only 1 of the records with the oldest date (min)

    select PROJ_UID, MD_PROP_UID, min(MOD_DATE) as MOD_DATE from MSP_PROJ_CUSTOM_FIELD_VALUES  where proj_uid in

    (SELECT CFV.PROJ_UID FROM MSP_PROJ_CUSTOM_FIELD_VALUES AS CFV

    INNER JOIN MSP_PROJECTS AS MP ON CFV.PROJ_UID=MP.PROJ_UID

    INNER JOIN MSP_CUSTOM_FIELDS_PUBLISHED_VIEW AS  CFPV

    ON CFV.MD_PROP_UID=CFPV.MD_PROP_UID

    INNER JOIN MSP_CUSTOM_FIELDS AS CF

    ON CFV.MD_PROP_UID = CF.MD_PROP_UID

    WHERE CF.MD_PROP_MAX_VALUES=1

    GROUP BY CFV.PROJ_UID,MP.PROJ_NAME, CFV.MD_PROP_UID, CFPV.MD_PROP_NAME HAVING COUNT (*)  >1

    )

    and MD_PROP_UID in

    (SELECT CFV.MD_PROP_UID FROM MSP_PROJ_CUSTOM_FIELD_VALUES AS CFV

    INNER JOIN MSP_PROJECTS AS MP ON CFV.PROJ_UID=MP.PROJ_UID

    INNER JOIN MSP_CUSTOM_FIELDS_PUBLISHED_VIEW AS  CFPV

    ON CFV.MD_PROP_UID=CFPV.MD_PROP_UID

    INNER JOIN MSP_CUSTOM_FIELDS AS CF

    ON CFV.MD_PROP_UID = CF.MD_PROP_UID

    WHERE CF.MD_PROP_MAX_VALUES=1

    GROUP BY CFV.PROJ_UID,MP.PROJ_NAME, CFV.MD_PROP_UID, CFPV.MD_PROP_NAME HAVING COUNT (*)  >1

    )

    group by PROJ_UID, MD_PROP_UID

    order by PROJ_UID

    • Marked as answer by bg_farmer Monday, February 27, 2012 8:51 PM
    Monday, February 27, 2012 8:51 PM

All replies

  • Hi,

    It is not supported to delete rows directly in the database.

    What do you see, when you look on that project's fields in PWA or in Project Pro?

    Regards,
    Stefan

    Friday, January 13, 2012 1:42 PM
  • Here's what it looks like:

     


    Friday, January 13, 2012 2:10 PM
  • To remove the field, you must be certain it is not being used in any of the projects.  There must not be any references to it.  Once you have cleared any reference to the table entry, then you can delete the row from the lookup table.

    Cheers!


    Michael Wharton, MBA, PMP, MCT, MCSD, MCSE+I, MCDBA
    www.WhartonComputer.com
    Saturday, January 14, 2012 4:14 AM
  • Just edit it and deselect the field and then save and publish. This way you're 100% safe.

     


    Thanks, Kashif
    Saturday, January 14, 2012 12:41 PM
  • We had the same problem with a customer. This is a bug and I strongly recommend you call Microsoft support, since resolving the problem involves messing with the database and you'll lose support if you do that on your own.

    Don't forget to make a backup before you start trying to repair that one.

     

    Livia

    Monday, January 16, 2012 9:36 AM
  • I thought I had it solved.  I just deleted the field, but not the lookup table.  I made a new field and referenced the lookup table, then went through all the projects and reset the value.  

    That's when I discovered that in any project, the probability that any usage of a lookup table, not just the one I was working on, has the possibility of being duplicated.  Argh, what happened that I started get value, value on a single selection lookup field?

    I agree with Livia, I'm going to call in the professionals and let them figure it out.  I give up.

    • Marked as answer by bg_farmer Wednesday, January 18, 2012 9:00 PM
    • Unmarked as answer by bg_farmer Monday, February 27, 2012 8:51 PM
    Wednesday, January 18, 2012 8:59 PM
  • I did contact Microsoft about this, and I was told that it IS a bug from the December update, and there is not a definitive time when it will get fixed.  They did send me a SQL script to find, and remove, the duplicate values.  I'm not comfortable posting the whole script, but here are the two primary queries used to identify duplicate values and how they make a determination of which value to keep:

    Here’s the portion that checks for duplicates.

    SELECT top 1 COUNT (*) TOTALCOUNT FROM MSP_PROJ_CUSTOM_FIELD_VALUES AS CFV

    INNER JOIN MSP_PROJECTS AS MP ON CFV.PROJ_UID=MP.PROJ_UID

    INNER JOIN MSP_CUSTOM_FIELDS_PUBLISHED_VIEW AS  CFPV

    ON CFV.MD_PROP_UID=CFPV.MD_PROP_UID

    INNER JOIN MSP_CUSTOM_FIELDS AS CF

    ON CFV.MD_PROP_UID = CF.MD_PROP_UID

    WHERE CF.MD_PROP_MAX_VALUES=1

     

    GROUP BY CFV.PROJ_UID,MP.PROJ_NAME, CFV.MD_PROP_UID, CFPV.MD_PROP_NAME HAVING COUNT (*)  >1

    ORDER BY TOTALCOUNT DESC

    And this one decides which value to retain.

    -- Return only duplicate records and return only 1 of the records with the oldest date (min)

    select PROJ_UID, MD_PROP_UID, min(MOD_DATE) as MOD_DATE from MSP_PROJ_CUSTOM_FIELD_VALUES  where proj_uid in

    (SELECT CFV.PROJ_UID FROM MSP_PROJ_CUSTOM_FIELD_VALUES AS CFV

    INNER JOIN MSP_PROJECTS AS MP ON CFV.PROJ_UID=MP.PROJ_UID

    INNER JOIN MSP_CUSTOM_FIELDS_PUBLISHED_VIEW AS  CFPV

    ON CFV.MD_PROP_UID=CFPV.MD_PROP_UID

    INNER JOIN MSP_CUSTOM_FIELDS AS CF

    ON CFV.MD_PROP_UID = CF.MD_PROP_UID

    WHERE CF.MD_PROP_MAX_VALUES=1

    GROUP BY CFV.PROJ_UID,MP.PROJ_NAME, CFV.MD_PROP_UID, CFPV.MD_PROP_NAME HAVING COUNT (*)  >1

    )

    and MD_PROP_UID in

    (SELECT CFV.MD_PROP_UID FROM MSP_PROJ_CUSTOM_FIELD_VALUES AS CFV

    INNER JOIN MSP_PROJECTS AS MP ON CFV.PROJ_UID=MP.PROJ_UID

    INNER JOIN MSP_CUSTOM_FIELDS_PUBLISHED_VIEW AS  CFPV

    ON CFV.MD_PROP_UID=CFPV.MD_PROP_UID

    INNER JOIN MSP_CUSTOM_FIELDS AS CF

    ON CFV.MD_PROP_UID = CF.MD_PROP_UID

    WHERE CF.MD_PROP_MAX_VALUES=1

    GROUP BY CFV.PROJ_UID,MP.PROJ_NAME, CFV.MD_PROP_UID, CFPV.MD_PROP_NAME HAVING COUNT (*)  >1

    )

    group by PROJ_UID, MD_PROP_UID

    order by PROJ_UID

    • Marked as answer by bg_farmer Monday, February 27, 2012 8:51 PM
    Monday, February 27, 2012 8:51 PM