CODEWITHSUNDEEP
powershellsharepointcsom
IAmKale
IAmKale

Reputation: 3426

How do I properly perform a subquery for an ID when adding items to a list using Sharepoint CSOM?

I'm using Powershell to add items to a list using Sharepoint CSOM. Since some fields are Microsoft.Sharepoint.Client.FieldLookupValues, I have to query other lists to get corresponding ID values that are then added to the recently added item. Here's how I'm querying for ID's:

function getLookupValueID($_ctx, $_lookupListName, $_colName, $_value)
{
    $lookupList = $_ctx.Web.Lists.GetByTitle($_lookupListName)
    $_ctx.Load($lookupList)
    $query = New-Object Microsoft.SharePoint.Client.CamlQuery
    $query.ViewXML = 
        '<View>
            <Query>
                <Where>
                    <Eq>
                        <FieldRef Name="{0}"/>
                        <Value Type="Text">{1}</Value>
                    </Eq>
                </Where>
            </Query>
        </View>' -f $_colName, $_value
    $col = $lookupList.GetItems($query)
    $_ctx.Load($col)
    $_ctx.ExecuteQuery()

    if($col.Count -gt 0)
    {
        return $col[0].ID
    }
    else
    {
        return $null
    }
}

Here's an exploded view of how I'm adding items:

$listCreateItem = $ctx.Web.Lists.GetByTitle("ListTitle")
$itemCreateInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
$newItem = $listCreateItem.AddItem($itemCreateInfo)
$newItem['Title'] = "An actual title goes here"
$newItem['PjCode'] = "000-C00001CA"
$newItem['GroupCode'] = getLookupValueID $ctx "Group" "GroupCode" "LA"
$newItem['CustomerCode'] = getLookupValueID $ctx "Customer" "CustomerCode" "C00001"
$newItem['TermStart'] = "1/1/2013 8:00"
$newItem['TermEnd'] = "12/31/2015 8:00"
$newItem['ChargeFlag'] = "TRUE"
$newItem['AllDayFlag'] = "FALSE"
$newItem['PjColor'] = "Red"
$newItem.Update()
$ctx.ExecuteQuery()

The problem I'm having is that only the value of the second call to getLookupValueID gets committed to the database. Here's the results of my running the above query four times: in the second and fourth lines I moved $newItem['CustomerCode'] = ... to just above $newItem['GroupCode'] = ...:

Problematic Inserts

As a matter of fact, if I make the calls to getLookupValueID(), then $newItem['Title'] and $newItem['PjCode'] show up empty when the new item is added to the list. Compare the above screenshot to this screenshot where I add the item in Sharepoint but comment out those function calls:

An almost correct insert

What am I doing wrong? I'm really new to Powershell, let alone Sharepoint and CSOM, so I'm sure I'm making some really fundamental mistakes.

Upvotes: 1

Views: 1045

Answers (1)

Yevgeniy.Chernobrivets
Yevgeniy.Chernobrivets

Reputation: 3194

In Client Object Model order in which you call Load and ExecuteQuery methods is important. Every API call like Load or AddItem will be executed during next call to ExecuteQuery. For example you call AddItem at very top of your function and you expect it to be executed during ExecuteQuery method at the bottom but it is not the case. AddItem call will be executed during first getLookupValueID call because it calls ExecuteQuery. Try this:

$groupCode = getLookupValueID $ctx "Group" "GroupCode" "LA"
$customerCode =  getLookupValueID $ctx "Customer" "CustomerCode" "C00001"
$listCreateItem = $ctx.Web.Lists.GetByTitle("ListTitle")
$itemCreateInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
$newItem = $listCreateItem.AddItem($itemCreateInfo)
$newItem['Title'] = "An actual title goes here"
$newItem['PjCode'] = "000-C00001CA"
$newItem['GroupCode'] = $groupCode
$newItem['CustomerCode'] = $customerCode
$newItem['TermStart'] = "1/1/2013 8:00"
$newItem['TermEnd'] = "12/31/2015 8:00"
$newItem['ChargeFlag'] = "TRUE"
$newItem['AllDayFlag'] = "FALSE"
$newItem['PjColor'] = "Red"
$newItem.Update()
$ctx.ExecuteQuery()

Upvotes: 1

Related Questions