Input mask on textbox in SharePoint list form

Input mask on textbox in SharePoint list form

Yesterday on LinkedIn I saw one discussion where one of group member asked a query about how to create Input mask on text box in SharePoint List form.

While searching on internet I found this post. In this post Author opened the new form in SharePoint designer, added custom new form and then added jQuery code on page.

But what if I do not have SharePoint designer.

So instead opening page in SharePoint designer, I added CEWP on NewForm.aspx

And then I did some changes in original code, Instead of custom style I used Title of the field. And I inserted below code in Source editor of the CEWP.

$(“input[title=’Phone’]”).mask(“(999) 999-9999? Ext.99999”);

And it worked for me. Have a look at below screenshot.


How to create count related lookup column in SharePoint custom lists?

A lookup count column is a special type of computed field that computes the number of items in the target list that point to the current item.

Let’s say I have two lists Departments and Employee Data. Now in Department list I want to add new column which will calculate how many employees are there in that department.

Here’s how we need to setup our lists:

1) Create a Department list. And add your department values in a title column.

2) Create an Employee Data list. In “Employee Data” list I renamed ‘Title’ column to ‘Employee Name’. And then add column ‘Department’ which will be lookup column pointing to the title column in the Department list.

3) After adding some test values in “Employee Data” list the list will look like this


4) Now go to “Department” list, add a column of type “Lookup” referring to the “Employee Data” list and you’ll notice that in the drop-down area where you define the lookup, you’ll have a new option called “Count Related”. This is here automatically because it recognizes that the “Department” list has a lookup pointing back to this one. Select that Count Related option.


5) Now your Department list will have a column counting how many employees are associated with that Department.


This doesn’t require any coding or JavaScript. A hidden SharePoint magic.

Add Comments And Replies To SharePoint Online Page Layout

Step 1

We created 2 lists. One for comments and another for Replies. In the comments list, we created two columns,

  • An ID column to identify to which page the comments belongs to. In our case, we used the page URL itself as the ID.
  • Comments column – to save the comments

In Replies list, we created 2 columns,

  • Comment ID – to identify to which comment the reply belongs to. This is a foreign key reference to ID column of the comments list
  • Replies column – To save the replies to the comment

Step 2

We created a custom page layout for publishing news articles. (Or, alternatively, we can make a copy of any of the existing page layout and customise it.) Edit this page layout using SharePoint designer in Advanced mode to add the required HTML and JavaScript code.

Step 3

The following HTML code was added to the page layout.

  1. class=“container”>
  2. class=“form-group col-md-6”>

  3. for=“comment”>Enter Comments: class=“form-control “ rows=“3” id=“comment”“” ID=“btn_CommentsSave” class=“cbs-Line1Link ms-noWrap ms-displayBlock” data-bind=“click: createListItem”>

  4.  class=“ms-Icon ms-Icon–post”> Post Comment
  • </div>
  • class=“container”>
  • class=“row”>
  • class=“col-md-8”>
  • class=“page-header”>“text: commentsCount”> Comments

  • class=“comment-list”>
  • class=“row” data-bind=“foreach: commentsfromUsers” style=“padding: 5px !important”>
  • class=“col-md-2 col-sm-2 hidden-xs” style=“padding: 5px !important”>
  • class=“thumbnail”class=“img-responsive” data-bind=“attr:{src: photo}” />
  • class=“text-center” data-bind=“text: userName”>
  • class=“col-md-10 col-sm-10”>
  • class=“panel panel-default arrow left”>
  • class=“panel-body” style=“border:thin #C0C0C0 solid !important;”>
  • class=“text-left”class=“ms-Icon ms-Icon–person” aria-hidden=“true”> “text: userName”>  class=“ms-Icon ms-Icon–event” aria-hidden=“true”>  
  • class=“comment-post” data-bind=“text: Comments”>
  • class=“text-right” align=“right”>“text: repliesCount”> reply(s) “#” data-bind=“click: $root.ShowHideItems”>class=“ms-Icon ms-Icon–reply”> Reply
  •                             </div>
  •                         </div>
  •                     </div>
  • class=“row” data-bind=“attr:{id: ‘divReply’+Id}” style=“display:none !important;padding: 5px !important;” align=“right”>
  • class=“col-md-8 col-sm-8 col-md-offset-3 col-sm-offset-0 hidden-xs”>
  • class=“form-group col-md-8”> class=“form-control “ rows=“3” data-bind=“attr:{id: ‘addReply’+Id}”“btn_addReplySave” href=“” data-bind=“click: $root.createReplyListItem”>
  • class=“ms-Icon ms-Icon–post”> Post Reply
  •                         </div>
  •                     </div>
  • class=“row”>
  •                     <!– Second Comment Reply –>
  • “foreach: replies “>
  • class=“row”>
  • class=“col-md-2 col-sm-2 col-md-offset-1 col-sm-offset-0 hidden-xs”>
  • class=“thumbnail”class=“img-responsive” data-bind=“attr:{src: photo}” />
  • class=“text-center” data-bind=“text: userName”>
  • class=“col-md-8 col-sm-8”>
  • class=“panel panel-default arrow left”>
  • class=“panel-heading right”> Reply
  • class=“panel-body” style=“border:thin #C0C0C0 solid !important;”>
  • class=“text-left”class=“ms-Icon ms-Icon–person” aria-hidden=“true”> “text: userName”class=“ms-Icon ms-Icon–event” aria-hidden=“true”>  
  • class=“comment-post” data-bind=“text: reply”>
  •                                     </div>
  •                                 </div>
  •                             </div>
  •                         </div>
  •                     </div>
  •                 </div>
  •             </section>
  •         </div>
  •     </div>
  • </div>

Step 4

The following JavaScript was added to the page layout.

  1. var siteCollectionURL = _spPageContextInfo.webAbsoluteUrl;
  2. var pageurl = window.location.href.replace(“%20”“”).replace(“%27”“”).replace(/[&$%‘’’]/g, “”);
  3. function ShowHideItems() {
  4.     document.getElementById(‘replyToComments’).style.display = ‘block’;
  5. }
  6. var Comment = function(comment, Id, Created, CreatedById, replies) {
  7.     this.Comments = comment;
  8.     this.Id = Id;
  9.     var num = parseInt(Created.replace(/[^0-9]/g, “”));
  10.     var date = new Date(num);
  11.     this.Created = date.toLocaleString();
  12.     this.CreatedById = CreatedById;
  13.     this.replies = ko.observableArray(replies);
  14. = ko.observable();
  15.     this.userName = ko.observable();
  16.     this.repliesCount = ko.observable(replies.length);
  17.     var self = this;
  18.     $.ajax({
  19.         url: _spPageContextInfo.webServerRelativeUrl + ‘/_vti_bin/listdata.svc/UserInformationList?$filter=Id%20eq%20’ + CreatedById,
  20.         method: “GET”,
  21.         headers: {
  22.             “Accept”“application/json; odata=verbose”
  23.         },
  24.         success: function(data) {
  25.   <TenantURL>/_layouts/15/userphoto.aspx?size=S&accountname=&#8221; + data.d.results[0].WorkEmail.toString());
  26.             self.userName(data.d.results[0].Name);
  27.         },
  28.         error: function(data) {
  29.             alert(‘Error’ + data.error);
  30.         }
  31.     });
  32. }
  33. var Reply = function(reply, Id, ParentId, Created, CreatedById) {
  34.     this.reply = reply;
  35.     this.Id = Id;
  36.     this.ParentId = ParentId;
  37.     var num = parseInt(Created.replace(/[^0-9]/g, “”));
  38.     var date = new Date(num);
  39.     this.Created = date.toLocaleString();
  40.     this.CreatedById = CreatedById;
  41. = ko.observable();
  42.     this.userName = ko.observable();
  43.     var self = this;
  44.     $.ajax({
  45.         url: _spPageContextInfo.webServerRelativeUrl + ‘/_vti_bin/listdata.svc/UserInformationList?$filter=Id%20eq%20’ + CreatedById,
  46.         method: “GET”,
  47.         headers: {
  48.             “Accept”“application/json; odata=verbose”
  49.         },
  50.         success: function(data) {
  51.   <TenantURL>/_layouts/15/userphoto.aspx?size=S&accountname=&#8221; + data.d.results[0].WorkEmail.toString());
  52.             self.userName(data.d.results[0].Name);
  53.         },
  54.         error: function(data) {
  55.             alert(‘Error’ + data.error);
  56.         }
  57.     });
  58. }
  59. function getReplies(ID) {
  60.     var commentsurl = siteCollectionURL + ‘/_vti_bin/listdata.svc/Replies?$filter=%20ParentID%20eq%20%27’ + ID + ‘%27’;
  61.     var replies = new Array();
  62.     $.ajax({
  63.         url: siteCollectionURL + ‘/_vti_bin/listdata.svc/Replies?$filter=%20ParentID%20eq%20%27’ + ID + ‘%27’,
  64.         method: “GET”,
  65.         headers: {
  66.             “Accept”“application/json; odata=verbose”
  67.         },
  68.         success: function(data2) {
  69.             for (var i = 0; i < data2.d.results.length; i++) {
  70.                 replies.push(new Reply(data2.d.results[i].Reply, data2.d.results[i].Id, ID, data2.d.results[i].Created, data2.d.results[i].CreatedById));
  71.             }
  72.         },
  73.         error: function(data2) {
  74.             replies = null;
  75.         },
  76.         async: false
  77.     });
  78.     return replies;
  79. }
  80. function ClearFields() {
  81.     document.getElementById(‘comment’).value = “”;
  82. }
  83. function getParameterByName(name, url) {
  84.     if (!url) url = window.location.href;
  85.     name = name.replace(/[\[\]]/g, “\\$&”);
  86.     var regex = new RegExp(“[?&]” + name + “(=([^&#]*)|&|#|$)”),
  87.         results = regex.exec(url);
  88.     if (!results) return null;
  89.     if (!results[2]) return ;
  90.     return decodeURIComponent(results[2].replace(/\+/g, ” “));
  91. }
  92. function CommentReplyViewModel() {
  93.     var self = this;
  94.     self.userID = ko.observable();
  95.     self.commentsfromUsers = ko.observableArray();
  96.     self.RepliesfromUsers = ko.observableArray();
  97.     self.commentsCount = ko.observable();
  98.     self.ShowHideItems = function(data) {
  99.         $(‘#divReply’ + data.Id).slideToggle();
  100.     }
  101.     self.getCommentsListItem = function() {
  102.         var commentsurl = siteCollectionURL + ‘/_vti_bin/listdata.svc/Comments?$filter=%20Title%20eq%20%27’ + pageurl + ‘%27’;
  103.         $.ajax({
  104.             url: siteCollectionURL + ‘/_vti_bin/listdata.svc/Comments?$filter=%20Title%20eq%20%27’ + pageurl + ‘%27’,
  105.             method: “GET”,
  106.             headers: {
  107.                 “Accept”“application/json; odata=verbose”
  108.             },
  109.             success: function(data2) {
  110.                 self.commentsfromUsers.removeAll();
  111.                 self.commentsCount(data2.d.results.length);
  112.                 for (var i = 0; i < span data – mce – type = “bookmark”
  113.                     id = “mce_SELREST_start”
  114.                     data – mce – style = “overflow:hidden;line-height:0”
  115.                     style = “overflow:hidden;line-height:0” > < /span><span data-mce-type=“bookmark” id=“mce_SELREST_start” data-mce-style=“overflow:hidden;line-height:0” style=“overflow:hidden;line-height:0” ></span > < data2.d.results.length; i++) {
  116.                     self.commentsfromUsers.push(new Comment(data2.d.results[i].Comments, data2.d.results[i].Id, data2.d.results[i].Created, data2.d.results[i].CreatedById, getReplies(data2.d.results[i].Id)));
  117.                 }
  118.             },
  119.             error: function(data2) {
  120.                 alert(‘Error’ + data.error);
  121.             }
  122.         });
  123.     }
  124.     self.createListItem = function() {
  125.         var commentsValue = document.getElementById(‘comment’).value;
  126.         var url = siteCollectionURL + “/_vti_bin/ListData.svc/Comments”;
  127.         var Comments = {
  128.             Comments: commentsValue,
  129.             SiteURL: pageurl,
  130.             Title: pageurl
  131.         };
  132.         var body = JSON.stringify(Comments);
  133.         $.ajax({
  134.             type: “POST”,
  135.             url: url,
  136.             contentType: ‘application/json’,
  137.             processData: false,
  138.             data: body,
  139.             success: function() {
  140.                 alert(‘Comments Saved Successfully.’);
  141.                 document.getElementById(‘comment’).value = “”;
  142.                 self.getCommentsListItem();
  143.             }
  144.         });
  145.     }
  146.     self.createReplyListItem = function(data) {
  147.         var RepliesValue = document.getElementById(‘addReply’ + data.Id).value;
  148.         var pageurl = window.location.href;
  149.         var allReplies;
  150.         var url = siteCollectionURL + “/_vti_bin/ListData.svc/Replies”;
  151.         var Replies = {
  152.             Reply: RepliesValue,
  153.             ParentID: data.Id.toString(),
  154.             Title: pageurl
  155.         };
  156.         var body = JSON.stringify(Replies);
  157.         $.ajax({
  158.             type: “POST”,
  159.             url: url,
  160.             contentType: ‘application/json’,
  161.             processData: false,
  162.             data: body,
  163.             success: function() {
  164.                 alert(‘Reply Saved Successfully.’);
  165.                 document.getElementById(‘addReply’ + data.Id).value = “”;
  166.                 $(‘#divReply’ + data.Id).slideToggle();
  167.                 allReplies = getReplies(data.Id);
  168.                 data.replies(allReplies);
  169.                 data.repliesCount(allReplies.length);
  170.             },
  171.             error: function(error) {
  172.                 alert(error.error);
  173.             },
  174.             async: false
  175.         });
  176.     }
  177.     $.ajax({
  178.         url: url,
  179.         method: “GET”,
  180.         headers: {
  181.             “Accept”“application/json; odata=verbose”
  182.         },
  183.         success: function(data) {
  184.             self.getCommentsListItem();
  185.         },
  186.         error: function(data) {
  187.             alert(data.error);
  188.         }
  189.     });
  190. }
  191. $(document).ready(function() {
  192.     ko.applyBindings(new CommentReplyViewModel());
  193. });


Step 5

Check-In and publish the page layout as a major version.

Step 6

Create a SharePoint page using the new page layout and you will see the following at the bottom of the article (if that is where you have placed the HTML)


In case if there are comments and replies, you will see –


As you can see, it shows the photo of the user who has commented and replied and also it will show “number of replies” for a comment. When you click on “Reply”, the reply comment box would appear.

Hope this is useful for someone.

Working with the Page Comments REST API in SharePoint Communication sites

There is a Comments REST API available which is used to get and post comments for a particular page. This endpoint is an addition to the SharePoint REST API, which means you will already be familiar with using it.

There are a few interesting things about how the commenting solution is implemented:
  1. Comments are not stored in the page list item (which makes sense in terms of scalability). They appear to be stored in a separate data store.
  2. Comments are stored with references to list guids and item ids. This means that if you move or copy a page, comments for that page will be lost.
  3. Only a single level of replies is allowed. Which in my opinion is a good thing as this will prevent long winding conversations and force users to keep their comments brief.
Now lets have a look at the SharePoint Comments REST API:
1) Get comments for a page:


This will bring back all the top level comments for a page which has the id 1 and lives in a site pages library with guid “1aaec881-7f5b-4f82-b1f7-9e02cc116098”.
2) You can also use the list title to get the list and the comments:
/_api/web/lists/GetByTitle(‘Site Pages’)/GetItemById(1)/Comments

3) Get replies for each comment:


4) Get replies for a specific comment:


Where 2 is the id of the comment.

5) Post a new comment on a page by making a POST request to:


6) To Delete a comment or a reply, Make a DELETE request to:


Since each comment or reply gets a unique id, the method is same for deleting both.

Implementing SharePoint Operations Using React.js – Part One

In this article series, you will learn how to implement basic SharePoint operations using React JavaScript libraries.
In this article, you will learn the React JavaScript library basics required for implementing SharePoint operations.
  • React is a front-end library developed and licensed by Facebook.
  • React is a declarative, efficient, and flexible JavaScript library for building user interfaces.
  • It is famous for ‘V’ in MVC, which handles view layer on the web.
  • Using ReactJS, we can build reusable components.
There are multiple ways of implementing the React JavaScript logic into SharePoint. In this article, I will use the React JavaScript libraries stored on the web. Additionally, Babel compiler is required for compiling the JSX content.
The library files referenced are,
  • react.js
  • react-dom.js
  • babel.min.js
The core functionality is written on JSX. Here, Babel compiler is used to transform the JSX content to JS.
  • JSX content is embedded with in the script tag, which is of the type text/babel.
  • JSX contains JavaScript with HTML/XML in it.
  • JSX is faster, type safe and object-oriented programming language.
  • JSX is not the globally accepted standard, so it is not supported on browsers.
Some of the React JavaScript basics used for our implementation are explained below.
  1. Components
    Components help us split the code/UI in multiple pieces. The component contains a constructor and methods.
  2. State
    State stores the data. The data can be in multiple forms. The data can be a string, number, array, etc.
  3. Props
    Props are attributes defined in the components. These are immutable and passed as arguments for rendering the HTML. propTypes are used for validating the props.
  4. Render
    It is a method, which returns the HTML content that needs to be displayed on the page.
  5. Constructor
    Constructor declares the variables, handlers and the methods.
  6. Component Life cycle
    There are predefined set of methods executed when the component is executed. The life cycle methods are componentWillMount, componentDidMount, componentWillReceiveProps, shouldComponentUpdate, componentWillUpdate, componentDidUpdate, and componentWillUnmount.
The React JavaScript library basics can be found on the official site
In the sample, I will refer the React JavaScript and Babel plugins directly on the content editor webpart.
The sample JSX content will look like below.
  1. class Sample extends React.Component {
  2.     constructor(){
  3.         super();
  4.         this.state = {
  5.             data:
  6.         }
  7.     }
  8.     componentWillMount(){
  9.     }
  10.     componentDidMount() {
  11.     }
  12.     // Other Life Cycle Methods
  13.     render(){
  14.         return(
  16.                 HTML
  •         );
  •     }
  • }
  • ReactDOM.render(<Sample />, document.getElementById(‘divid’));
  • Introducing Microsoft Flow integration in Excel

    The Microsoft Flow for Excel add-in enables you to connect your data to a wide range of services such as SharePoint, Outlook 365, Dynamics 365, Teams, Visual Studio Online, Twitter, Approvals, etc. In this post, we’ll walk you through this new capability with a hands-on example.

    Install the Flow Add-in

    To get started, in Excel, go to the Insert tab in the ribbon and select Store. Then, in the dialog, search for Microsoft Flow. Then, click Add.


    Let’s imagine that you work for Cronus Energy, a multi-national energy production company, which generates energy through wind turbines and hydroelectric power plants. Cronus is on the lookout for better ways to streamline and standardize internal processes to make things easier for their employees. They’ve identified a key process they want to modernize:

    Transfer market data entered by Commercial Analysts (minimum energy to generate, maximum energy, and target energy based on revenue goals) to SharePoint so that the Operations team can decide which turbines to use for the week. After moving the data to SharePoint, they also want to send an alert to the team on Microsoft Teams and facilitate a discussion if needed. The Operations team uses a SharePoint list called Turbine Energy Distributions with the columns shown below.

    Their development team is already short on resources, so they want to be able to stand up the solution quickly while avoiding as much custom development as possible. Let’s see how Flow can help.

    Create a flow

    The Commercial Analysts at Cronus Energy enter market data in a spreadsheet hosted in SharePoint. To follow along, download this spreadsheet and upload it to SharePoint or OneDrive for Business.

    To get started, click the Flow menu from under the Data tab in the ribbon.

    This will open the Flow launch panel in Excel where you will be prompted to Sign in and consent to the permissions requested by the add-in. Click Accept.

    Once you’ve signed in, you can explore several templates to quickly connect to a wide variety of services with minimal set up. Scroll down the screen and choose Create an item in SharePoint for a selected row.

    Selecting the template will prompt you for your credentials and provide additional details about the template. Click Continue.

    In the trigger (For a selected row), click the drop-down next to the Table field and select your table, e.g. Table1. The trigger may be collapsed; if so, click on Edit and confirm that the Table field is set to Table1.
    The For a selected row trigger is similar to other manual triggers like the Flow button for mobile or SharePoint’s For a selected item – users can be prompted for inputs when they run the flow (Text, Yes/No, File, Email, or Number) and all flows run with the credentials of the invoker. For this flow, add a Text input called Message with the hint text of “Enter a message for the team.”

    In the SharePoint-Create item action, enter the Site Address and List Name for Cronus Energy’s Turbine Energy Distributions List.

    Click the Week field and select Week from the Dynamic content pane.

    The parameters in the Dynamic content pane consist of your table’s columns – Week, Min Energy (mWh), Target Energy (mWh), Max Energy (mWh), Price ($/mWh), Revenue, and Profit, information about the person invoking the flow – Timestamp, User email, User id, and User name, and lastly any “manual” Outputs you add to the trigger like Message.
    Repeat this for the Energy Target, Min Energy, and Max Energy.

    Now, add a Microsoft Teams – Post message action. Choose a Team and Channel to post your message to. In the Message field, enter a link to the newly created item along with the Message populated by the flow invoker.

    Save the flow by clicking the Save button.

    Run flow

    Select a row in the table and then click Run flow in the Flow launch panel.

    The first time you run this flow, you’ll be asked to confirm your credentials. You can also learn more about what this flow does. Click Continue.

    Now enter a message to send to your team, requesting feedback. Click Run flow.

    Voila! An item is created in SharePoint with details from the row you selected in Excel and a message is posted on your behalf including your note asking for feedback and a link to the item.

    Share a flow with run-only permissions

    Now that you’ve created the flow, you can share it with colleagues either by adding them as an owner of the flow or as a run-only user. The latter allows you to maintain ownership of the flow, while enabling your colleagues to run it. In Flow, head over to My Flows and choose the Create an item in SharePoint for a selected row flow. Note – To run the flow, they must have access to the spreadsheet.

    Under Manage Run-Only Users, click Add another person.

    Here you can enter individuals, AD security groups, O365 groups, or even anyone that has access to the SharePoint list.  For each connector used in the flow, you can decide whether the invoker should bring their own credentials (“Provided by run-only user”) or use your credentials (“Use this connection”). Click Save to add the user as a run-only user.

    Create smarter flows using Microsoft Cognitive Services LUIS

    The LUIS integration in Flow enables you to make predictions on a query string. You can design your Flow to execute a set of actions when the desired intent is detected. This post walks you through setting up a Flow to automatically create a calendar entry when your team requests a meeting on Yammer.

    Create your LUIS app

    If you are already using  LUIS, sign in. You can also sign up here. Once logged in, click on New App and then select New Application.

    In the window that appears, provide more information about your app and click on Add App.

    You will be navigated to the application dashboard where you can define new models and train your app. Click on the plus sign next to Intents in order to add a new intent.

    In the dialog that follows, provide the intent  name and an example stringSave your intent.

    Similarly, click on the plus sign next to Entities to add a new entity. Provide a suitable name and save.

    Similarly, you can create multiple intents and entities. You are now ready to train your app.

    Provide examples of utterances that contain the models you defined in the previous steps. Figure below shows an example of one such utterance. Train your app by marking the models on the example string.

    Repeat this for several utterances until your app starts to make the right predictions. You’re now ready to use your app in Flow.

    Make a connection

    The first step to build your Flow is to make a connection to your cognitive services account.

    Click on the settings menu on the top right corner of the Flow website and pick Connections. On the page that opens, click on Create connection.

    From the list that opens, click on the plus sign next to LUIS.

    Provide your LUIS API Key and Create connection.

    You are now ready to build your Flow.

    Set up your flow

    1. When a new message is posted on Yammer, detect intent of the message using LUIS Get prediction action. To specify the expected intent, pick a pre-defined value from the Desired Intent dropdown.
    2. Add a condition to check if the desired intent is found. In this case, output  Is Desired Intent equals True if  ‘Create Calendar Entry’ is detected.
    3. Use the Get entity by type action to fetch ‘Start Time’ from the query string.
    4. Similarly, fetch ‘End Time’.
    5. Using the entity values from #3 and #4, create a new event in Google Calendar.

    You Can Do Everything In SharePoint