Exception CICONotCheckedOut when removing items from LookupTable using Project Server CSOM API

Question

I have a Project Online solution and need to remove items from a LookupTable (Microsoft.ProjectServer.Client.LookupTable), because I need to clear it before I add new items. Primary use for this is to do unit testing where I create items and then clear everything when the test is done.

Everytime I run this I get an exception with code 0x80131500 (-2146233088) with error message "PJClientCallableException: CICONotCheckedOut\r\nCICONotCheckedOut", the exception is thrown when ExecuteQuery is executed.

I can add new items and change existing items to the LookupTable without checking anything out. And there is no CheckOut method on the LookupTable. So I don't know what to do...

The only option with Project Online is to use the Project Server CSOM API, the PSI cannot be used.

Exception:

Microsoft.SharePoint.Client.ClientRequest.ProcessResponseStream(Stream responseStream)
   at Microsoft.SharePoint.Client.ClientRequest.ProcessResponse()
   at Microsoft.SharePoint.Client.ClientRequest.ExecuteQueryToServer(ChunkStringBuilder sb)
   at Microsoft.SharePoint.Client.ClientRequest.ExecuteQuery()
   at Microsoft.SharePoint.Client.ClientRuntimeContext.ExecuteQuery()
   at Microsoft.SharePoint.Client.ClientContext.ExecuteQuery()
   at <mynamespace>.LookupTableHelper.DeleteAllItems(Guid tableGuid)

Code:

// Create password
SecureString securePassword = new SecureString();
foreach (char c in "qwerty") securePassword.AppendChar(c);
Microsoft.SharePoint.Client.SharePointOnlineCredentials cred = new Microsoft.SharePoint.Client.SharePointOnlineCredentials("[email protected]", securePassword);

// Connect
ProjectContext context = new ProjectContext(urlToProjectOnlineWeb);
context.Credentials = cred;

// Get entries in lookup table
LookupTable lookupTable = context.LookupTables.GetByGuid(tableGuid);
context.Load(lookupTable.Entries);
context.ExecuteQuery();

if (lookupTable.Entries.Count > 0)
{
    // If there are items in the collection, then remove the first item
    LookupEntry e = lookupTable.Entries[0];
    lookupTable.Entries.Remove(e);
}

// Upload the change to cloud
context.LookupTables.Update();
context.ExecuteQuery(); // Always throw PJClientCallableException: CICONotCheckedOut\r\nCICONotCheckedOut

Solution (at least the best so far) is to first do an Add to checkout the table, then remove all existing items. Which leaves me with one item which cannot be removed. But it is better than not being able to remove anything... Thanks Jogeukens!

// Create password
SecureString securePassword = new SecureString();
foreach (char c in Configuration.GetConfig().PASSWORD.ToCharArray()) securePassword.AppendChar(c);
Microsoft.SharePoint.Client.SharePointOnlineCredentials cred = new Microsoft.SharePoint.Client.SharePointOnlineCredentials(Configuration.GetConfig().USERNAME, securePassword);

// Connect
ProjectContext context = new ProjectContext(Configuration.GetConfig().PPM_URL);
context.Credentials = cred;

// Get entries in lookup table
LookupTable lookupTable = context.LookupTables.GetByGuid(tableGuid);
context.Load(lookupTable.Entries);
context.ExecuteQuery();

LookupEntryCreationInformation newEntry = new LookupEntryCreationInformation();
newEntry.Id = Guid.NewGuid();
newEntry.Value = new LookupEntryValue();
newEntry.Value.TextValue = "The one that cannot be removed...";
lookupTable.Entries.Add(newEntry);

while(lookupTable.Entries.Count > 1)
{
    // If there are items in the collection, then remove the first item
    LookupEntry e = lookupTable.Entries[0];
    lookupTable.Entries.Remove(e);
}

// Upload the change to cloud
context.LookupTables.Update();
context.ExecuteQuery();

Answer 1

I don't have enough reputation to make this a comment, but you may get some help out of this:

I had the same problem, finally I was able to work around it by making sure every delete happens in the same update as an add. Apparently "Lookuptable.entries.add()" Checks the Lookup table out automatically, while Lookuptable.entries.Remove() Doesn't.

So by getting them both in one update, you can use the checkout from the add to remove other entries.

Offcourse if you don't need to add new entries, this wont work. If you found any other solutions, that work more reliably, please add them as an answer.

Answer 2

You can use PSI code to delete lookup entry. lutDs.LookupTableTrees.Rows[i].Delete();

                lookupTableWS.CheckOutLookupTables(lutList);
                bool validateOnly = false;
                lookupTableWS.UpdateLookupTables(lutDs, validateOnly, autoCheckOut, 1033);
                bool forceCheckIn = false;
                lookupTableWS.CheckInLookupTables(lutList, forceCheckIn);

for reference Delete Existing Lookup Entry from Lookup Table using PSI

Answer 3

Just wanted to add that I had the same problem and confirmed that CSOM is not able to delete items if something is not added with the same query. Even adding and removing the same item before submitting the query throws an error. The query has to end up adding an item for the deletions to work.

But PSI works just fine. Not sure if Mattias meant that he is not allowed to use PSI due to requirements of the project but I complement CSOM using PSI in my Project Online developments since CSOM still has a lot of shortcomings.

Answer 4

Another solution is to open the lookup table from PWA and then run the code. I do this for huge changes to values in lookup tables. There is no need for an add in this scenario.