SharePoint Online: Working With List Content Types Using PowerShell

In this article we will discuss the configuration of some of the important operations on Content Type for SharePoint Online Lists. Though I have taken only a handful of operations to showcase in this demo, but remember there are lot more to explore.

Operation: How to enable “Content Types” for List

We can see this setting available under “Advanced Settings” for the list as shown below-

1

We can play with this setting using PowerShell as described below-

2

In Step 1 we will get the object reference to the respective list by calling “GetByTitle” method

In Step 2 we will set “ContentTypesEnabled” to True

In Step 3 we will update the list property by calling “Update” method

In Step 4 we will send the batch request to SharePoint Server for processing by calling “ExecuteQuery” method

In Step 5 we will display success message to the Users if Step 4 executes successfully

In Step 6 we will call function that we have explained in Step 1-5

3

Once this script executes successfully we can see list setting updated by navigating “Advanced Settings” of the list as shown below-

4

Operation: How to “Add Existing Content Type To List”

We can see this setting available under “List Settings” for the list as shown below-

5

We can play with this setting using PowerShell as described below-

6

In Step 1 we will get the object reference to the respective Web

In Step 2 we will get the object reference to the respective list by calling “GetByTitle” method

In Step 3 we will get the object reference to the respective list by calling “GetById” method on Content Types collection

In Step 4 we will send the batch request to SharePoint Server for processing by calling “ExecuteQuery” method

In Step 5 we will display success message to the Users if Step 4 executes successfully

In Step 6 we will call function that we have explained in Step 1-5

7

Once this script executes successfully we can see a new content type added by navigating “List Settings” of the list as shown below-

We can notice the new content type added to the List Content Types Collection

8

Operation: How to “Get List of All Content Types”

We can see this setting available under “List Settings” for the list as shown below-

9

We can play with this setting using PowerShell as described below-

10

In Step 1 we will get the object reference to the respective Web in context of which this code is executing

In Step 2 we will get the object reference to the respective list by calling “GetByTitle” method

In Step 3 we will get List Content Types Collection

In Step 4 we call the “Load” function to retrieve List Content Types Collection properties from server

In Step 5 we will send the batch request to SharePoint Server for processing by calling “ExecuteQuery” method

In Step 6 & 7 we will loop through the Content Types Collection and display relevant properties (ex. Name, ID and so on) from the collection

In Step 8 we will call function that we have explained in Step 1-7

11

Once this script executes successfully we can see list of content types by navigating “List Settings” of the list as shown below-

12

Operation: How to “Delete Existing Content Types” From List

We can see the List of Content Types already added to List under “List Settings” as shown below-

12

We can delete any Content Type from list by using PowerShell by using PowerShell as described below-

13

In Step 1 we will get the object reference to the respective Web in context of which this code is executing

In Step 2 we will get the object reference to the respective list by calling “GetByTitle” method

In Step 3 we will get List Content Types Collection

In Step 4 we call the “Load” function to retrieve List Content Types Collection properties from server

In Step 5 we will send the batch request to SharePoint Server for processing by calling “ExecuteQuery” method

In Step 6 we will loop through the Content Types Collection

In Step 7 we will look for required content types that are intended to delete. In this example we will compare the Content Type Name to ensure that only intended content type are deleted

In Step 8 we will call “DeleteObject” method to delete the respective content type from Content Types Collection of List

In Step 9 we will send the batch request to SharePoint Server for processing by calling “ExecuteQuery” method

In Step 10 we will display success message after Step 9 got executed successfully

In Step 11 we will call function that we have explained in Step 1-10

14

Once this script is executed successfully the respective content type will be deleted from Content Types List and this can we verified from the UI by navigating through “List Settings”

15

That is all for this demo.

Hope you find it helpful.

Advertisements

SharePoint Online: Working With List Fields Using PowerShell

In this article we will discuss the operations on List Fields (Columns), which involves Getting All Columns, Adding New Columns, Updating Existing Columns and so on.

To start with this demo we will start with a list called “Products” and perform all operations on this list.

1

Operation: How to Add New Columns To List

We can add a new column to the list by making use of the following code.

2

In Step 1 we will get the object reference to the current Web using Client Context properties

In Step 2 we will get the object reference to the respective list by calling “GetByTitle” method

In Step 3 we will define the xml of the List Column schema. You can get this XML by prototyping the list using SharePoint UI and then by using SharePoint Client Browser to look for Schema XML for Lists & Fields.

In Step 4 we will call “AddFieldAsXml” method to add field as xml schema to the list

In Step 5 we will send the batch request to SharePoint Server for processing by calling “ExecuteQuery” method

In Step 6 we will display the success message to the users

In Step 7 we will call function that we have explained in Step 1-5

We can see this field added to the list by browsing the list

4

We can see the details of the new column (Datatype and others) by browsing List Settings

5

Operation: How Get All Columns of List

We can get all columns used in a list by making use of the following code.

6

In Step 1 we will get the object reference to the current Web using Client Context properties

In Step 2 we will get the object reference to the respective list by calling “GetByTitle” method

In Step 3 we will get the object reference to Fields collection of the list

In Step 4 we call the “Load” function to retrieve Fields collection properties from server

In Step 5 we will send the batch request to SharePoint Server for processing by calling “ExecuteQuery” method

In Step 6 we will loop through the collection and display Field details to the users

In Step 7 we will call function that we have explained in Step 1-6

We can see the fields collection to the list by browsing the list

7

Operation: How to Update column of List

Let’s consider that we have to add little description the field Title, which is blank currently

8

We can update an existing column to the list by making use of the following code.

9

In Step 1 we will get the object reference to the current Web using Client Context properties

In Step 2 we will get the object reference to the respective list by calling “GetByTitle” method

In Step 3 we will get the object reference to the respective field by calling “GetByTitle” method

In Step 4 we will set the Description property of List object with the required value

In Step 5 we will call “Update” method to save these changes back to SharePoint List

In Step 6 we will call “Load” method to retrieve updated properties (Description) of the field from Server

In Step 7 we will send the batch request to SharePoint Server for processing by calling “ExecuteQuery” method

In Step 8 we will display Field details to the users to the users

In Step 7 we will call function that we have explained in Step 1-8

10

We can see description of Title field is update to the list by browsing the field properties under list settings

11

Operation: How To Add Existing Site Columns To List

Since we are planning to add an existing Site Column to the list, it is necessary to ensure the existence of Site Column. We can verify this by navigating “Site Settings > Site Columns”.

For this demo I already have added a Site Column “ProductOwner” that we can see under “Custom Columns” group as shown below-

12

And we can also verify the list settings to ensure that “ProductOwner” Column is not added to the list earlier

13

Now we will look into code to add existing Site Column to the list as explained below-

14

In Step 1 we will get the object reference to the current Web using Client Context properties

In Step 2 we will get the object reference to the respective list by calling “GetByTitle” method

In Step 3 we will get the object reference to Fields collection of the Web. It is important to note that Site Columns are the part of Web Fields Collection not List Fields Collection. So we have to make use of Web object reference to look for existing Site Columns.

In Step 4 we call the “Add” function on “Fields” Collection of the list to add the reference of the Site Column from Step 3

In Step 5 we will send the batch request to SharePoint Server for processing by calling “ExecuteQuery” method

In Step 6 we will call function that we have explained in Step 1-5

We can see the field collection to the list by browsing the list settings-

16

We can further look into column details by clicking it

17

Operation: How Set Default Value For Field

We can set default values to SharePoint List Fields programmatically by using the following code as explained below-

18

In Step 1 we will get the object reference to the current Web using Client Context properties

In Step 2 we will get the object reference to the respective list by calling “GetByTitle” method

In Step 3 we will get the object reference to Fields collection of the list

In Step 4 we will call “DefaultValue” property of Field Object and assign it a value of our choice

In Step 5 we will call “Update” method of Field Object, which will update the “DefaultValue” property back into database

In Step 6 we will send the batch request to SharePoint Server for processing by calling “ExecuteQuery” method

In Step 7 we will display the a success message which is informing users about the status of operation

In Step 8 we will call function that we have explained in Step 1-7

19

We can see this value shown as default value whenever a new Item has been created (programmatically or using browser) as shown below-

20

That is all for this demo.

Hope you find it helpful.

SharePoint Online: Working with Lists Using PowerShell

In this article we will discuss SharePoint List operations using PowerShell for SharePoint Online.

To start with this demo, I have created a demo SharePoint Online Site that I will use to perform SharePoint operations in this and few of the upcoming articles in this series.

1

In case you are not aware of on how to start with PowerShell development for SharePoint Online, I would rather recommend you to first go through one of my previous articles. This article will help you to setup development environment and get you started.

SharePoint Online: How To Install SharePoint Online Management Shell

Provide SharePoint Online Site credential when it is asked for

2

Operation: How To Get Lists Collection

3

In Step 1 we will initialize Web Object using SharePoint Client Context

In Step 2 we initialize SharePoint Lists Collection

In Step 3 we call the “Load” function to retrieve SharePoint List Collection properties from server

In Step 4 we call “ExecuteQuery” method to send the request to SharePoint Server

In Step 5 we iterate the lists collection returned back from the server and display required list properties as output

In Step 6 we call function that we have explained in Step 1-5

And here is the output for this operation

4.1

Operation: How To Add New List

4

In Step 1 we will initialize “ListCreationInformation” object which allows to specify the metadata details for this new list

In Step 2 we will specify Title for new list

In Step 3 we will specify Description for new list

In Step 4 we will specify List Template for new list. In this example we will use Custom List Template

In Step 5 we will call “Add” method of “Lists” collection for SharePoint Web

In Step 6 we will call “Load” method, to retrieve the properties of list object from Server

In Step 7 we call “ExecuteQuery” method to send the request to SharePoint Server

In Step 8 we call function that we have explained in Step 1-7

On successful execution of Step 1-7 we will get list added to SharePoint and Internal ID of list as Output

And here is the output for this operation

5

We can also see to the SharePoint Site to ensure that a new list has been added

67

Operation: How To Update Existing List

8

In Step 1 we will initialize “List” object which allows to modify the metadata details for this existing list

In Step 2 we will update the description of the existing list

In Step 3 we will call “Update” function of list object and then call “Load” method, to retrieve the properties of list object from Server

In Step 4 we call “ExecuteQuery” method to send the request to SharePoint Server

In Step 5 we will retrieve the list description to ensure that it is updated successfully

In Step 6 we call function that we have explained in Step 1-6

And here is the output for this operation

9

We can also see to the SharePoint Site to ensure that the list description has been updated

10

Operation: How To Delete Existing List

11

In Step 1 we will initialize “List” object by calling a method “GetByTitle” on the Web Object

In Step 2 we will call “DeleteObject” method on List Object that will delete the reference of respective list from lists collection

In Step 3 we call “ExecuteQuery” method to send the request to SharePoint Server

In Step 4 we will display success message to inform successfully deletion of the list

In Step 5 we call function that we have explained in Step 1-4

And here is the output for this operation

12

We can also see to the SharePoint Site to ensure that the list has been deleted successfully from SharePoint

13

That is all for this demo.

Stay tuned for the upcoming articles in this series.

Hope you find it helpful.

SharePoint Online: How Develop Analytics for SharePoint Online using POWER BI

In this article we discuss to use data stored in SharePoint Online Site to develop analytics using PowerBI.

Here is the architecture diagram to understand it in a better way:

1

For details on this architecture you can visit: POWER BI ANALYTICS POWERED BY R INTEGRATION

In order to start with this demo, we will start with two lists namely Products & Product Category having some test data to work with as shown below:

12

Now launch the PowerBI desktop

3

Click on Get Data -> More…

4

From “Online Services” category select “SharePoint Online List”

Click “Connect”

5

Specify the URL of SharePoint Online Site

Click OK

6

Select “Product” & “Product Categories” from Navigator

Click Load to load the data in the memory of report designer

7

Let the designer query the list metadata and build cache

8

Under Fields section we can “Product” & “Product Categories” added to the designer

9

Right Click on Product List and Select “Edit Query” to modify the query for fetching the data from lists

10

In the Query Editor, click on “Choose Columns” Menu -> Choose Columns

11

Select the desired columns and from the list to be included into the report. This step is useful from performance point of view as well so always try to include only relevant columns in the query

Click OK

12

We get the new result set updated based on the query modifications

13

Now modify any of the available lookup fields or Composite fields like URL in SharePoint to include the required property into the final result set

Click on the icon next to “Product Url” as indicated below

14

Select required properties like I am selecting “Url” and deselecting “Description”

Make sure you select “Use original column name as prefix” so that new property name generate based on the original column name

Click OK

15

Once we are done we are done with the modification we can see a new column appears with the name “Product Url.Url”

16

Once all the changes have been made click “Close & Apply” command button in the ribbon

17

Wait till the query changes have been saved successfully

18

Once all the changes have been saved we can see the selected query fields under “Fields” section

19

Now we establish Parent Child Relationship between “Products” & “Product Categories” list

Click “Manage Relationships” option from the Ribbon

20

On the “Manage relationships” screen Click “New” to add a new relationship between “Products” & “Product Categories” list

21

Select Primary Key & Foreign Key Columns on the “Create Relationship” screen as shown below

Select “ProductCategoryId” column from “Products” List as Foreign Key and “Id” column from “Product Categories” List as Primary Key

Also select “Cardinality” as required as “Many to one”

Once done click “Ok” to complete the process

22

And we can be able to see the new relationships created as shown below.

Click “Close” to close the “Manage Relationship” dialog

23

Once this is done we can develop reports using any available visualizations as highlighted below-

24

Here is the sample dashboard that I have developed based on the data available in SharePoint Lists

25

Hope you find it helpful.

SHAREPOINT ONLINE/OFFICE 365: HOW TO COPY EMAIL ATTACHMENTS TO SHAREPOINT DOCUMENT LIBRARY USING MICROSOFT FLOWS

In this article we will see how we can utilize Microsoft Flows to copy email attachments from Outlook Emails to SharePoint Document Library.

To continue with this demo we can start with adding a document library to SharePoint site, let’s call it “Outlook Attachments”

1

Now go to Microsoft Flow designer and start with adding a workflow

As I explained in my earlier articles we need to start with selecting a trigger for this workflow

Since we need to copy the email attachments to document library so we have two Cloud Services “Office 365 Outlook” and “SharePoint Online” to deal with

Select “Office 365 Outlook” as connector and “Office 365 Outlook – When a new email arrives” as trigger to kick off this workflow

2

Choose the Outlook folder or subfolder that you want to monitor. Here I am selecting “Inbox” as target folder. This means workflow will trigger only if the email arrives in Inbox folder and rest of the emails will be ignored.

3

Set “Has Attachment” = Yes, this settings will further filter out the incoming emails based on if they have attachments or not.

We are only monitoring only emails with attachments and that’s what these filter will achieve for us.

Set “Include Attachments” = Yes, this is required so that Workflow runtime include the binary information of each the attachments and make it available to be written to SharePoint Library.

4

Add another action with “SharePoint” as connector

Select “SharePoint – Create file” as trigger, this action will be responsible to write down the attachment binaries to SharePoint Library

5

Enter Site URL where we have “Outlook Attachments” document library sitting

Select document library name from the lookup in the Folder Path Field

6

Select “Name” property in the File Name field, this property represents the name of the attachment

7

Select “Content” in the File Content Field, this property will hold the binary data for the attachment being written to the SharePoint Document Libraries.

Here it is important to note that if we does not set the “Include Attachments” = Yes, then Content property will be null and will cause not write operations takes place

8

Add another action to the workflow, which will be responsible to send email after the attachment has been copied to the SharePoint Library

9

Select “Office 365 Outlook” as connector

Select “Office 365 Outlook – Send an email” as trigger, this trigger is responsible for sending emails

10

Choose or any valid email id in “To”, here I am just selecting the email of me

Specify Subject as applicable

Specify the Body content as required. This content can consist of dynamic tokens provided by Workflow runtime as shown below-

11

Saves the Workflow and complete it.

In the following screens we can see the complete workflow steps in one go-

Step-1

12

Step-2

13

Step-3

14

Now let’s test the workflow by sending an email with attachments to myself.

15

Once I received the email containing attachments in Inbox Folder, Workflow gets triggered

16

Just after that we can see workflow entered into the debug mode as shown below-

17

And as soon as it is completed we can see the completion notification email as shown below-

18

And we can also see the attachments copied to the “Outlook Attachments” SharePoint Library as shown below-

19

Hope you find it helpful.

SHAREPOINT ONLINE/OFFICE 365: HOW TO DEVELOP APPROVAL WORKFLOWS ON LIST ITEM UPDATE EVENT USING MICROSOFT FLOWS

In this article we will see how to deal with SharePoint Online List Item update event.

For the demo in this article I will consider a business scenario where on update of any Product in the Product list, an approval process will execute and an email is send to approver for the selection of Price Range for the Product just updated.

To start with a demo I have a Product List in SharePoint Online Site as shown below-

2

Go to “Flow” Menu and Click on “Create a Flow”

3

Here we have two choices either we can use any available workflow template that suites our requirements or we can create a new one from scratch

4

For this demo we will create a Flow using blank template

Go to Flow Site by clicking “See you flow” links

5

Click on “Create from blank” this will launch Workflow Designer

6

On the Workflow Designer, Select the Trigger

We can select trigger from the any of the available services as shown highlighted below-

7

Select SharePoint Online as target service & select for the Trigger “SharePoint-When an existing item is modified”

8

Provide the Title to the Workflow “Product Details Modification Approval Request”

9

Create SharePoint context by providing Site URL and List Name as shown-

10

You can optionally rename the Step by selecting “Rename” option as shown below-

11

Here I have changed the name of Step to “Get SharePoint Context”

12

Add another action to the workflow by clicking “Add an action” option

13

Send email by selecting “Office 365 Outlook” Service and “Office 365 Outlook-Send approval email” action

14

Rename the name of Step to “Request Approval”

15

Choose or approver’s email in “To”, here I am just selecting the email of the person who has originally created the Product in list by choosing “Created By Email” field

Specify Subject as applicable

Specify User Options that allows approver to select the approval options from within the mail

Specify the Body content as required. This content can consist of dynamic tokens provided by Workflow runtime as shown below-

16

Update List Item by selecting “SharePoint Online” Service and “SharePoint-Update item” action

17

Rename the step to “Update item”

Specify Site URL & List Name

In Id field select “ID” token provided by workflow runtime. This token represents the List item Id of the item that triggered this workflow to execute.

It is important to note that values to all mandatory fields in the list must be provided. If you don’t want to change the values you can choose same values again.

18

For Title field we have selected “Title” token that represent current value present in list for the Title field of this item

19

In “Product Price Range” field choose “SelectedOption” token that will hold the value selected by the approver from within the mail

20

Once all the Steps are configure as required we can save the workflow by clicking “Save flow”

21

Click “Done” to return back to the workflow dashboard

22

On the workflow dashboard we can see the details of services used with in this workflow

23

Now modify and the Product in Product List and notice the “Product Price Range” field which is currently set to “Low”

24

Once the Product details has been changed, this will trigger the workflow that we have developed

25

On execution the workflow we can see a new email hitting the inbox of the approver

26

Let’s consider the approver submitted “High” as approval option

27

This action will update the “Product Price Range” field for the list item to “High” as shown below-

28

Hope you find it helpful.

SharePoint Online/Office 365: How To Develop Approval Workflows On List Item Add Event Using Microsoft Flows

Microsoft introduces Flows that enables development of Workflows for SharePoint Online and other more than 50 services over the cloud.

In this article we will see the demonstration of Microsoft Flow around SharePoint Online and will deal with List Item Add Event.

For this demo let’s consider we have a Product Catalog List which required Approval to happen with each new item added.

We have a Product List as shown below-

2

Go to “Flow” Menu and Click on “Create a Flow”

3

Here we have two choices either we can use any available workflow template that suites our requirements or we can create a new one from scratch

4

For this demo we will create a Flow using blank template

Go to Flow Site by clicking “See you flow” links

5

Click on “Create from blank” this will launch Workflow Designer

6

On the Workflow Designer, Select the Trigger

We can select trigger from the any of the available services as shown highlighted below-

7

Provide the title of the Workflow

Select SharePoint Trigger “SharePoint-When a new item is created” that will fire when a new item is added to SharePoint Online List

8

Provide Site Url and List name to setup SharePoint context

Add an action to the Workflow by clicking “Add an action”

9

Select approval action by selecting “Outlook.com – Send approval email”

10

Select “Approver Email” in “To”, since this “Approver” is a People Field on SharePoint,

Workflow designer is intelligent enough to represent each property as a separate field

11

Add a condition to the workflow by clicking “Add a condition”

12

In the condition we are check if Approver Approved the Production or not.

If Approved then send approval email to requestor else delete the Product and send Email to requestor

13

Choose “Created By Email” field in “To” as it will hold the Email Id of the Requestor (Person added the Product)

Choose “Approver Email” field in “CC” to send a copy of the mail to Approver also

14

If not approved, delete the Product by choosing action “SharePoint – Delete item”

15

 

In the “Delete item” action specify SharePoint Site Url and List Name

Select “ID” token for the field “Id”. This token will hold the ID of the List Item that trigged the Workflow

16

After deleting the item we, send email to Requestor by choosing “Created By Email” in “To” field
Specify the body of email as desired
Choose “Approver Email” field in “CC” to send a copy of the mail to Approver also

17

Once all the actions are configured as required save the workflow by clicking “Save flow”

18

With this we are all done and now it is time to test the workflow

To test the Workflow, add a new item in the Product List. Specify Title and Approver and Save the Product.

Save action will trigger the Workflow that we just created

19

As a result of Workflow execution Approve will get the Email with “Approve” and “Reject” options

20

Beauty of this implementation is that Email contains clickable options for approver to submit the approver’s choices from within the email

Here Approver rejects the Product

21

As a result of this Rejection, the workflow will delete the Product from the List and send an Email Notification to Requestor and Approver informing same.

22

Product has been deleted from the list

23

Now again add a new Product to the list that will again trigger the event to execute the workflow

24

Product has been added

25

As a result of Workflow execution Approve will get the Email with “Approve” and “Reject” options

26

This time Approver approved the Product

27

As a result of this Approval, the workflow sends an Email Notification to Requestor and Approver informing same.

28

Hope you find it helpful.