2

I have a standard lookup table database table "LOOKUP_TABLE_VALUES" in a SharePoint 2013 solution with columns like:

  • LT_UID - for the Lookup table GUID
  • LT_VALUE_FULL - storing the values with the lookup table levels (delimited with '.')
  • LT_VALUE_SORT_INDEX - assigning index to each value from the above column, such as '1', '2' and so on.

For some reason the values in the LT_VALUE_SORT_INDEX column are not correct and I want to change them. But I cannot use MSSQL and execute a query against that column. The only option is to do it via PSI - either with a PowerShell script, or some Visual Studio Solution.

Ideally, I would have a query something like this:

SELECT [LT_UID],[LCID],[LT_VALUE_SORT_INDEX],[LT_VALUE_FULL]
FROM [xxx].[xxx].[MSP_LOOKUP_TABLE_VALUES]
WHERE LT_UID = 'GUID HERE'
ORDER BY LT_VALUE_FULL

So the values in the LT_VALUE_SORT_INDEX have to be incremented (starting from 1) for each row returned by LT_VALUE_FULL in that order (ascending).

I am a newbie with PowerShell and I cannot come up myself with a solution. At the same time, I cannot find out how to use the PSI to fetch the values and update the lookup table for the sorted index column.

Any help is appreciated! i.e. links to resources, ideas, partial solutions, etc.

4
+50

Instead of PSI I suggest you to work with the managed object model of Project Server. That is much more developer friendly. Last year I wrote a post about how can you list lookup table entries:

using (var projectContext = new ProjectContext(pwaUrl))
{

    projectContext.Load(projectContext.LookupTables,
        lts => lts.Include(
                    lt => lt.Name,
                    lt => lt.Id,
                    lt => lt.FieldType,
                    lt => lt.Entries.Include(
                            lte => lte.FullValue,
                            lte => lte.Id)));
    projectContext.ExecuteQuery();

    projectContext.LookupTables.ToList().ForEach(lt =>
        {
            Console.WriteLine("LT: Name [{0}], Id [{1}], FieldType [{2}]", lt.Name, lt.Id, lt.FieldType);
            lt.Entries.ToList().ForEach(lte => Console.WriteLine("LTE: Name [{0}], Id [{1}]", lte.FullValue, lte.Id));

        });
}

or how can you remove existing and add new lookup table entries:

using (var projectContext = new ProjectContext(pwaUrl))
{

    projectContext.Load(projectContext.LookupTables, lts => lts.Include(lt => lt.Name, lt => lt.Entries));
    projectContext.ExecuteQuery();

    var lookupTable = projectContext.LookupTables.First(lt => lt.Name == "YourLookupTable");

    // this value should be removed
    lookupTable.Entries.Remove(lookupTable.Entries.First(pe => pe.FullValue == "ValueToRemove"));

    // and a new value has to be created
    lookupTable.Entries.Add(new LookupEntryCreationInformation
    {
        Id = Guid.NewGuid(),
        Value = new LookupEntryValue { TextValue = "ValueToCreate" },
        SortIndex = 165
    });

    projectContext.LookupTables.Update();
    projectContext.ExecuteQuery();
}

I suggest you to query the entries as in the first example, order them by the FullValue property on the client side, iterate through the ordered list, assign an incremental SortIndex to the entries and invoke projectContext.LookupTables.Update().

You should add the following references to the project:

  • Microsoftr.ProjectServer.Client
  • Microsoft.SharePoint.Client
  • Microsoft.SharePoint.Client.Runtime

and using directives to the code:

using System;
using System.Linq;
using Microsoft.SharePoint.Client;
using Microsoft.ProjectServer.Client;
  • And does the solution need to be a sandboxed one? Or do I don't need to create a SharePoint solution at all? It's a bit confusing to me. – lapadets Feb 16 '15 at 9:49
  • It is client code (for example, an .exe) you run on the client side. – pholpar Feb 16 '15 at 10:24
  • In the first snippet I get the error Cannot convert lambda expression to type 'System.Linq.Expressions.Expression<System.Func<Microsoft.ProjectServer.Client.LookupTableCollection,object>>[]' because it is not a delegate type. Is this related to some missing references? Any ideas? – lapadets Feb 16 '15 at 10:59
  • Ok, I was missing the SP.Client reference! This is fine, but I don't want to add new entries to a lookup table - rather update just one column from the table (so all rows in that single column). Apart from the 'Remove' and 'Add' methods, I don't see 'Edit' (or something similar). – lapadets Feb 16 '15 at 14:09
  • Also, as explicitly mentioned in the Title, I really want to use a PSI call, no other option is acceptable. – lapadets Feb 16 '15 at 14:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.