Can we define NOT IN inside CAML Query

Question

I have this Power Shell script which work on SharePoint 2013 on-premises:

$NewHireList  = $spSourceWeb.Lists["ONB New Hire"]
$NewHireItem = $NewHireList.Items | Where {$_["Candidate ID"] -eq $CandidateID -and $_["Onboarding Status"] -notin "Canceled","Completed"}

And I want to convert it using PNP PowerShell to work on SharePoint online, currently I got this:

$NewHireItem = Get-PnPListItem -List "ONB New Hire" -PageSize 100 -Query "<View><Query><Where><Eq><FieldRef Name='Candidate ID'/><Value Type='Number'>$CandidateID</Value></Eq></Where></Query></View>" -ScriptBlock { Param($items) $items.Context.ExecuteQuery()} | ForEach-Object {

But not sure how I can presents -notin inside CAML?

Answer 1

Assuming "Onboarding Status" is a choice column in SharePoint list, try using CAML query filter conditions like below:

<Where>
    <And>
        <Neq>
            <FieldRef Name='OnboardingStatus' />
            <Value Type='Choice'>Canceled</Value>
        </Neq>
        <Neq>
            <FieldRef Name='OnboardingStatus' />
            <Value Type='Choice'>Completed</Value>
        </Neq>
    </And>
</Where>

Combining your existing filter condition, you can use:

<Where>
    <And>
        <Eq><FieldRef Name='Candidate ID'/><Value Type='Number'>$CandidateID</Value></Eq>
        <And>
            <Neq>
                <FieldRef Name='ObjectID' />
                <Value Type='Integer'>71</Value>
            </Neq>
            <Neq>
                <FieldRef Name='ObjectID' />
                <Value Type='Integer'>72</Value>
            </Neq>
        </And>
    </And>
</Where>