Get Items Action Fetching Few Records from SharePoint Online

Introduction

When working with SPO lists and trying to use ‘Get Items’ in Power Automate fetching not all the records but only a few records. For instance, I have created a list with 3 columns

Column name	Data Type
ReminderStage	Choice
FristDue	DateTime
SecondDue	Date Time

When trying to get these values using Get Items I am not able to get all the values. Below is the screenshot for reference.

Create a column

Below are the 5 custom columns that have been created.

Columns

Issue: The idea is to get a list of users from the list, who did not complete the survey. I am trying to get by Boolean data type ‘Yes/No’.

From the list, I have the following 6 users who did not complete the survey

SurveyComplete

When using PowerAutomate I am only getting the below users. Below is the screenshot of the PowerAutomate query action.

Get items

When using the Select operation, to get the selected fields (Title, SurveyCompleted)from the output, I am only getting 3 records out of 6. Below is the screen capture of the Select operation.

Select operation

From: The ‘value’ which is the output from the ‘Get Items’ action.

Map: created the fields that need to be extracted. In this case item()?[‘Title’]. This means from the record item select only ‘Title’ where ‘Title’ is the internal name of the column in SharePoint.

Similarly, for other values ‘SurveyCompleted’.

Body

Ideally, it should get all the records from the SPO list as none of the records has the ‘SurveyCompleted’ field set to true.

Explanation

By default, Get items fetches only the first 100 records. To get more than that you need to set up the pagination in settings. The reason for the issue here is the column ‘SurveyCompleted’ which is of data type ‘Boolean’ is created after some existing data. In this case, the records that existed before this column creation in SharePoint online will have a null value. Though you have set the default data type value as ‘No’ this won’t be applicable to existing data. Only new records will have this value. Hence the reason, in this case, I am only getting a few records.

Fix

To fix this issue, we need to get the items that have the Boolean value ‘Null’ and update those items with default values. I am using PnP PowerShell and CAML query to get the items that have null values. Below is what the query looks like.

$Query = "
<View>
    <Query>
        <Where>
            <IsNull><FieldRef Name='SurveyCompleted' /></IsNull>
        </Where>
    </Query>
</View>"

Below is the PS script, that gets the records with null values. Here I am checking for Boolean data type. The same script can be used to check the null values for any other data type.

#Connecting to SPO site
Connect-PnPOnline -Url "https://5wrdjv.sharepoint.com/sites/ContosoDev" -Interactive
$ListName = "Delegates"
#$ListItems = Get-PnPListItem -List $ListName -PageSize 500
#CAML Query to Filter List Items
$Query = "
<View>
    <Query>
        <Where>
            <IsNull><FieldRef Name='SurveyCompleted' /></IsNull>
        </Where>
    </Query>
</View>"
  
#Get All List Items matching given query
$ListItems = Get-PnPListItem -List $ListName -Query $Query
#Loop thorugh list items and update the null value to default
foreach($item in $ListItems) {
    try {
    #Get the current item
    $ListItem = Get-PnPListItem -List $ListName -Id $item.Id -ErrorAction Stop
    $ListItem.title
    Write-Host "Setting the value for the list item with title $($item.Id)" -ForegroundColor Yellow
    Set-PnPListItem -List $ListName -Identity $ListItem -Values @{
        "SurveyCompleted"="false"
    }
    Write-Host "update completed for item with title $($item.Id)" -ForegroundColor Green
    }
    catch{
        Write-Host "Error occurted while updating the list item with title $($item.Id)" -ForegroundColor Red
        Write-Host $_.Exception.Message -ForegroundColor Red
    }
}

Validation

After running the PS script that sets the null records, now I am able to get all the records that have condition satisfied. In this case, I am getting where the ‘SurveyCompleted’ field is set to false.

Validation

Conclusion

Thus, in this article we have learned about null values for SharePoint list items and how they can exist and learned how to set default values, using PnP PowerShell.

References