SharePoint - Reference file in item properties

Question

(Disclaimer: This is the first time I deal with SharePoint)

I am trying to add a file in SharePoint and reference it in a property of a list item. The property type is 'Sparqube Lookup Classic' (I had no idea what that was but a quick search led me to this: http://www.sparqube.com/SharePoint-Lookup-Column/).

What ever I try I seem to fail. I have searched on-line, but no relevant results came up (wrong search terms probably?).

Here is my half-functioning code. The item has no file attached in that (_x03a8__x03b7__x03c6__x03b9__x03) property when the code finishes.

    public void PublishDocToSP()
    {
        var clientContext = GetClient();
        SP.Client.File file;
        var folderName = "DocLib";

        // Upload file - Works OK.
        {
            var fileName = @"C:\Users\user\Desktop\file.pdf";
            var folder = clientContext.Web.Folders.GetByUrl(clientContext.Url + '/' + folderName);

            var info = new FileCreationInformation
            {
                ContentStream = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.Read),
                Overwrite = false,
                Url = clientContext.Url + '/' + folderName + '/' + Path.GetFileName(fileName),
            };
            file = folder.Files.Add(info);
            folder.Update();
            clientContext.ExecuteQuery();
        }

        // Add item in list and reference file in property. Not working!
        {
            var list = clientContext.Web.Lists.GetById(Guid.Parse("{6F15AACD-1132-4BD8-AC7D-36EA1A336D5C}"));

            var itemCreateInfo = new ListItemCreationInformation()
                                     {
                                         //FolderUrl
                                         //LeafName
                                         //UnderlyingObjectType
                                     };
            var li = list.AddItem(itemCreateInfo);
            li["DocmanId"] = 111;
            li["Title"] = "adfadfadfaf";
            li["Email_x0020__x039a__x03b1__x03c4"] = "trehagireue@mailcom"; // Email
            li["_x0391__x0394__x0391_"] = "ΑΗ-ΓΑ...";
            li["_x0391__x03c1__x03b9__x03b8__x03"] = "dfgdfg-sdf";
            li["_x03a8__x03b7__x03c6__x03b9__x03"] = new SP.SPFieldUrlValue(clientContext.Url + '/' + folderName + '/' + "file.pdf"){Description = "Test Desc"};
            li.Update();
            list.Update();
            clientContext.ExecuteQuery();
            var insertedId = li.Id;
        }
    }

Any idea what I am missing?

UPDATE:

Following Vadim Gremyachev's advice I got this:

var lv0 = item["_x03a8__x03b7__x03c6__x03b9__x03"] as Microsoft.SharePoint.Client.FieldLookupValue[];
{Microsoft.SharePoint.Client.FieldLookupValue[1]}
    [0]: {Microsoft.SharePoint.Client.FieldLookupValue}
lv0[0]
{Microsoft.SharePoint.Client.FieldLookupValue}
    base {Microsoft.SharePoint.Client.ClientValueObject}: {Microsoft.SharePoint.Client.FieldLookupValue}
    LookupId: 532
    LookupValue: "σσσ"
    TypeId: "{f1d34cc0-9b50-4a78-be78-d5facfcccfb7}"

Now, I guess I will have to find how to get these data. I know where the LookupValue comes from, but I am not sure about the rest yet... LookupId seems to be the file ID. I wonder how do I get this from the client. I saw no such property being returned back.

UPDATE2:

Finally, I have managed to get the uploaded file ID like that: https://stackoverflow.com/a/22254339/2173353. But when I send any of those in the field, I get either an error or nothing as linked files:

using SP = Microsoft.SharePoint;

string.Format("{0};#{1}", fileID, file.Name);
string.Format("{0};#{1:B}", fileID, listId); //listId is GUID
new Microsoft.SharePoint.Client.FieldLookupValue[] { new Microsoft.SharePoint.Client.FieldLookupValue { LookupId = fileID } };
new SP.SPFieldLookupValueCollection { new SP.SPFieldLookupValue(fileID, file.Name) };

I have also tried with no array, just simple FieldLookupValue. That didn't work either. :(

Answer 1

If i understood the question properly, the actual issue is related with setting value for custom field. If so, i would suggest the following approach since it is not clear what type of Sparqube Lookup Classic column is and therefore what is supported format of its value.

First step would be to determine the format of custom field value by getting existing list item, for example:

var fieldName = "--The name of Sparqube Lookup Classic column goes here--";
var list = ctx.Web.Lists.GetByTitle(listTitle);
var item = list.GetItemById(itemId);
ctx.Load(item);
ctx.ExecuteQuery();
var fieldValue = item[fieldName];  //getting the value of Sparqube Lookup Classic column

Once list item is retrieved, I would try to specify fieldValue while creating list item:

var listItem = list.AddItem(itemCreateInfo);
listItem[fieldName] = fieldValue;
listItem.Update();
clientContext.ExecuteQuery();

If this operation will succeed, all you need to do would be to specify the field value in the same format.

Answer 2

OK. It seems that Sparqube Lookup Classic uses the file title and cannot work with files that have no title. So, first you have to set the title and then everything works:

    public static void PublishDocToSP()
    {
        var clientContext = GetClient();
        SP.Client.File file;
        var folderName = "DocLib";

        // Upload file
        {
            var fileName = @"C:\Users\user\Desktop\file.pdf";
            var folder = clientContext.Web.Folders.GetByUrl(clientContext.Url + '/' + folderName);

            var info = new FileCreationInformation
            {
                ContentStream = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.Read),
                Overwrite = false,
                Url = clientContext.Url + '/' + folderName + '/' + Path.GetFileName(fileName),
            };
            file = folder.Files.Add(info);

            // !!! I set some value for Title field, because in my case, Title column is Display Column of Sparqube Lookup Classic
            ListItem itemFile = file.ListItemAllFields;
            itemFile["Title"] = Path.GetFileName(fileName);
            itemFile.Update();
            clientContext.Load(itemFile);

            clientContext.ExecuteQuery();
        }

        // Add item in list and reference file in property. Not working!
        {
            var list = clientContext.Web.Lists.GetById(Guid.Parse("{F682C057-9715-4F1C-BE1E-D451803FF389}"));
            var itemCreateInfo = new ListItemCreationInformation()
            {
                //FolderUrl
                //LeafName
                //UnderlyingObjectType
            };
            var li = list.AddItem(itemCreateInfo);
            li["Title"] = "adfadfadfaf";

            // Set value for Lookup Classic with single value selection
            li["sqLookupClassic"] = new SP.Client.FieldLookupValue()
            {
                LookupId = file.ListItemAllFields.Id
            };
            // !!! OR
            // li["sqLookupClassic"] = string.Format( "{0};#{1}", file.ListItemAllFields.Id, file.ListItemAllFields["Title"] );

            // !!! If 'Allow multiple values' option is selected for Lookup classic, you should set value in the following way:
            //li["sqLookupClassic"] = string.Format( "{0};#{1};#{2};#{3}", item1.Id, item1["Title"], item2.Id, item2["Title"] );

            li.Update();
            clientContext.ExecuteQuery();
            var insertedId = li.Id;
        }
    }