How to use REST API call to query SharePoint list over 5000 threshold?

We have a SharePoint SPFx project that is using SPFx JS and REST API to query few large SharePoint lists with over 5000 items. We got the following error:

Microsoft.SharePoint.SPQueryThrottledException
The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.

The two calls for SPFx JS and REST call are listed below.

https://mycompany.sharepoint.com/sites/pubs-stg/_api/web/lists/getbytitle('Journals')/Items?$select=ID&$filter=(ISDNNumber eq '0160-6999')

let spRecs = await web.lists.getByTitle(listName).items.filter("Publication_x0020_Record_x0020_I eq " + recordId).getAll();

After some testing, it seems like we could resolved this issue by adding the index. We looked at the two calls and both are using filter on the field "Publication_x0020_Record_x0020_I" lookup column to another list. The solution is to add the index to this field!

After index column "Publication_x0020_Record_x0020_I" , the calls will be successful. You could also add other fields to the filter as long as the first filter returns less than 5000 items. Here is the example:

https://mycompany.sharepoint.com/sites/pubs-stg/_api/web/lists/getbytitle('Journals')/Items?$select=ID&$filter=(ISDNNumber eq '0160-6999') and (Abbreviation eq 'AADE Ed J')

How to resolve Nintex forms custom button submit AND redirect issue

We have a Nintex forms with one custom button submit then redirect to a confirmation page. When user click the button, it was redirecting to something strange. See screenshot below.

After debugging this issue, it seems like Nintex form button redirect could not resolve the link you copied from browser directly. See the screenshot below.

The solution is to copy the redirect URL from browser to notepad first, then copy to the Nintex form. This seems like trivial but took me an hour to figured out from Nintex support site.

Hope this will remind me in the future.

How to resolve Nintex workflow email action 'The specified string is not in the form required for a subject.' error

We have a running Nintex workflow in production failed for few email actions with the error message “The specified string is not in the form required for a subject.”

 After debugging this, it seems like there is an limitation for O365 outlook that subject line cannot be folded across multiple lines. It does not seem to have the character limitation for the subject. However, no matter how long of a subject you submit, exchange will ultimately truncate the unencoded version of that subject down to 255 characters and append "...".

So in order to resolve the error listed above, we have implemented in two different palaces for our SharePoint framework solution as UI with Nintex workflow.

1. Inside SharePoint framework solution, trim and leading and trailing spaces and returns. Then also replace inside returns with space.

    let trimedTitle: string = title;

    if(title !=null){

//This javascript trim leading and trailing spaces and returns

      trimedTitle = title.trim(); 

      if (trimedTitle != null){

//This javascript replaces all 3 types of line breaks with a space 

        trimedTitle = trimedTitle.replace(/(\r\n|\n|\r)/gm," "); 

    }

//The trimedTitle will be the good subject that could be used in exchange email

2. In Nintex workflow, add a “Trim String” activity on the subject before send email.

This will ultimately resolve the email action error 'The specified string is not in the form required for a subject.'.

Easy to detect device from SharePoint SPFX React application

We have a SharePoint framework application need to behavior differently when invoked from mobile device. Here is the quick way to implement this.

1. Install react-device-detect package

npm install react-device-detect --save

2. Import the package

import {

  BrowserView,

  MobileView,

  isBrowser,

  isMobile

} from "react-device-detect";

3. Use it inside your application

  if(!isSubmitDisabled && !recordIsReadOnly && isMobile){

      submitMenuProps = {

        items: [

          {

            key: 'saveOnly',

            name:'Save only',

            secondaryText: 'Do not submit to approval workflow',

            text: 'Save as draft',

            title: 'Save without submitting to a workflow',

            iconProps: { iconName: 'Save'},

            onClick: this.props.onSaveOnly

          },                       

          {

            key: 'Submit',

            name:'Submit',

            secondaryText: 'Submit to approval workflow',

            text: 'Submit from Mobile',

            title: 'Submitting to a workflow',

            iconProps: { iconName: 'SaveToMobile'},

            onClick: this.props.onSubmit

          }                     

        ]

      };

    }

Procedure to resolve the issue - cannot add SharePoint online list item with form does not exist error

We have found an issue that we could add new item to a SharePoint online list. The error is the form does not found. After debugging the issue, we identified that display item, edit item, and add item all have the same issue. Here is the details how to debug and fix the issue.

1. Verify three forms. If you understand how SharePoint works, you will know immediately that this should be the issue on SharePoint three form issue (NewForm.aspx, DidForm.aspx, and EditForm.aspx).  You can verify after open the site with SharePoint designer. 

2. Try to recreate the missing forms. The next step is to use SharePoint designer to add these three forms as described here. However, we got server error.

3. The next step is to migrate the same list from another place using Sharegate. The migration failed and has the following message.

"Unable to create web part 'Microsoft.SharePoint.WebPartPages.ListFormWebPart': A Web Part or Web Form Control on this Page cannot be displayed or imported. You don't have Add and Customize Pages permissions required to perform this action.. In Office 365, this is mainly caused by the farm setting "Custom Script" being deactivated."

Now we identified the root cause.

4. Enable the "Custom Script" on the site using the script described here. 

Connect-SPOService -Url https://mycompany-admin.sharepoint.com

Set-SPOSite -Identity https://mycompany.sharepoint.com -DenyAddAndCustomizePages 0 

5. The final step is either recreate the three forms from SharePoint designer or migrate content from another environment.

6. You might want to disable the "Custom Script" again suing Powershell.

Connect-SPOService -Url https://mycompany-admin.sharepoint.com

Set-SPOSite -Identity https://mycompany.sharepoint.com -DenyAddAndCustomizePages 1

We are not sure why the three forms lost in the first place but this blog will give you the procedure to fix the issue. 

How to display Url object in reatctable

If you need to display Url object with link and descriptions inside reatctable, here are the steps.

1. Create a Url interfance and add to the object.

export interface Url {

    Url: string;

    Description: string;

}

export interface ApproverWithLink {

  name?: string;

  email: string;

  id: number;

  role: string;

  status: string;

  lastmodified: string;

  taskLink: Url;

}

2. Create the object and fill in Url object.

  public static _getApproverWIthUrl(workflowUsers: IWorkflowUsers): ApproverWithLink[] 

    let authors: ApproverWithLink[] = [] as ApproverWithLink[];

    if(workflowUsers != null && workflowUsers != undefined && 

workflowUsers.approvers != null &&  workflowUsers.approvers != undefined){

      let localApprovals: Approver[] = workflowUsers.approvers;  

      for (let i = 0; i < localApprovals.length; i++) {

        let localUrl: Url = {Url: 'https://www.google.com', Description:'Google'};

        let returnRec: ApproverWithLink = {name: localApprovals[i].name, 

email: localApprovals[i].email, id: 0,  role: localApprovals[i].role, 

status: localApprovals[i].status, 

lastmodified: localApprovals[i].lastmodified, taskLink: localUrl  };

        authors.push(returnRec);

      }

    }

    

    return authors;

  }

3. Display on the webpart

let localApprovals: ApproverWithLink[] = 

SPFacade._getApproverWIthUrl(this.props.wfApprovalReviewerList);

<ReactTable

                                  data={localApprovals}

                                  columns={[

                                    {

                                      

                                      columns: [

                                        {

                                          Header: "Name",

                                          accessor: "name"

                                          

                                        },

 

                                        {

                                          Header: "Role",

                                          accessor: "role"

                                        },

                                        {

                                          Header: "Status",

                                          accessor: "status"

                                        },

                                        {

                                          Header: "Last Modified",

                                          accessor: "lastmodified"

                                        },

                                        {

                                          Header: "Link",

                                          accessor: "taskLink",

                                          Cell: e=><a href={e.value.Url}> {e.value.Description} </a>

                                          

                                        }

                                      ]

                                    }

                                  ]}

                                  defaultPageSize={5}

                                  showPagination={false}

                                  noDataText="No approvals assigned!"

                                  style={{

                                    height: "200px" // This will force the table body to overflow and scroll, since there is not enough room

                                  }}

                                  className="-striped -highlight"

                                />

                          </div>

Procedure to hide SharePoint online list public views

We have a SharePoint site that is used by two different type of users. One type is admin who should be able to see list item. The other type is regular user who should see some list item based on the column value. We do not want to have item level permissions that will need additional development effort. Here is the quick solution.

1. Create public view to exclude list item based on the column value. 
2. Create another master view to display all items. 
3. Add a page that is only be accessed by Admin group that has the link to the master view and include the master view.
4. Use Powershell to hide the view from view selections.

#Install-Module SharePointPnPPowerShellOnline

#Change this to the URL of your SharePoint site
$sharePointUrl = "https://yourcompany.sharepoint.com/sites/sitename"

#Connect-PnPOnline –Url $sharePointUrl –Credentials (Get-Credential)

# open learner list
$listUrl = "Lists/listname"
$list = Get-PnPList -Identity $listUrl

$list.Context.Load($list.Views)
$list.Context.ExecuteQuery()

ForEach($v in $list.Views){

    if($v.Title -eq 'ListViewName'){ 
    $v.Hidden = $true;
    #$v.Hidden = $false;
    $v.Update()
    }
}

$list.Context.ExecuteQuery()