Please find below a transcription of the audio portion of Jeff Lynch’s webinar, Automating Information between Microsoft Project and SharePoint, being provided by MPUG for the convenience of our members. You may wish to use this transcript for the purposes of self-paced learning, searching for specific information, and/or performing a quick review of webinar content. There may be exclusions, such as those steps included in product demonstrations. You may watch the recording of this webinar at your convenience.
Melanie: Hello and welcome, [Melanie 00:00:06] here with team MPUG. Welcome to Automating Information between Microsoft and SharePoint. We will be taking breaks today for Q&A throughout the session, we invite you to join in using the chat feature. You can see it here up on the slide in front of you, but you can pull it right out of your GoToWebinar control panel and type in your questions and we will present them to Jeff if he takes break and again, at the end. I’ll also be sending out some thank yous for getting involved today. Now, I’d like to introduce our expert Jeff Lynch. Jeff started Lynch Interactive in 1999. Over the years, they have developed many solutions for a wide range of clients from E-commerce to custom learning management and business automation systems. Since 2012, Jeff has been primarily focused on SharePoint and Microsoft Project, building a wide range of solutions for clients ranging from CRM and project management to human resources facilities and many others. Jeff, a very big MPUG welcome and I will-
Jeff Lynch: Thank you.
Melanie: … hand the presentation over to you.
Jeff Lynch: Good morning everybody and thank you for joining in today. Looking forward to having a bunch of like minded people that understand what we’re talking about today and when I tell them what I do, they don’t get that glazed over look in their eyes. I’m going to go ahead and kill the webcam so that we have more bandwidth to part of my webcam. There we go. So getting started, Automated Information between Microsoft Project and SharePoint, the contents of what we’re going to cover today, the first thing we’re going to do is talk a little bit about a few different reasons why you might want to connect SharePoint with Project.
Melanie: Jeff, I’m not seeing your slides, yes?
Jeff Lynch: Okay. Let me show my screen. Link to monitor. How about now?
Melanie: You are up now. Thank you.
Jeff Lynch: Okay. So we’re going to go through and the first thing we’re going to do is talk real quickly about some reasons that you might want to connect with SharePoint and project. The next thing we’re going to do is we’re going to go into the connecting Project Online and Project for the web to SharePoint. And I have examples of how we’re going to do both of those because they are different between Online and P4W. Next, we’re going to set some dynamic item level permissions on the SharePoint list that we’ve developed so the only people that need to see things or should see things can see them, but they can still get in there and do their work. Then finally, we’re going to update that SharePoint list when data changes in the project. Basically, in our example today, we’re going to say when the project reaches 100%, then we’re going to close it in our SharePoint list so that our list stays clean and we’re not constantly looking at everything we’ve ever done forever.
Jeff Lynch: So let’s get started. First, just to throw out a couple of objectives and a couple of licensing considerations. First of all, my objective for this is so that you guys can learn the basic tools and understand the flow of how you should put things together for your particular business. Everybody’s different, I’ve been doing this for a lot of years now and I’ve never come across two people or two businesses that have the exact same situation of information that they need to track. So really, at the end of the day what you’re going to do is you’re going to sit down and think about what information you need to track and then how are we going to do it. And hopefully give you enough tools today to be able to put together a plan that’s going to make that reliable and effective for you.
Jeff Lynch: A couple of licensing things is that you do need at least a Project Plan 1 to do this stuff, you may require Project Plan 3. The example there is if you’re using Project for the web obviously, it’d be Project Plan 1, but if you’d like to use the Project desktop application, you have to upgrade to Plan 3. And I’m assuming that everybody’s familiar with Project at this point. There is an additional license for Power Automate per user plan that you might be required. If you’re going to be using Project for the web, it uses the Dataverse and the Dataverse requires a premium connector, which is this $15 a month license for your developer who puts together the flows. Every individual who uses the system doesn’t need the Power Automate license, only the person who’s developing the flows, and a lot of cases that’s a system user.
Jeff Lynch: So let’s get into it and let’s talk about some use cases for SharePoint integration. The first one is for sharing information with non-project users. We’re building this thing and we have other people at our company or organization that they need to know what’s going on but they don’t need to know the details, so we want to give them a high level overview. Certainly, there’s some Power BI dashboards that we can use for this. But this gives us a cleaner set of data that we can share exactly the information we want to share with those people, specifically executive sponsors, possibly some external customers, understand with the external users or customers that you have to do it in Project Online because project on the web it still would require a different level of permission. Maybe its board members, anybody that has a SharePoint ID can get in there and look at things.
Jeff Lynch: Another example would be the ability of PMs to add additional information to the project. So the example here that I didn’t do in this demo today just because of time constraints would be, if we wanted to add status reports, there’s not really a status report feature in Project Online. So what we would do is we would create a list of project status reports, tie those to a list of projects and then each week the project manager would be required to fill out that project status report. And then the one we did decide to do today is where we have non-project related user information that is private.
Jeff Lynch: In this scenario, we have a whole bunch of contractors, and this is an actual scenario that I developed for clients, they have a whole list of contractors and those are all independent contractors, and they give them work assignments. And each contractor submits time logs against that assignment. In our example, we’re not getting into the time logs, we’re going to stop at the work assignments. But needless to say, they don’t want the contractors to see what the other contractors had been assigned. They do want them to collaborate in project on the various tasks that are involved in achieving the end goal of the project, but they want their work assignments which is a billing issue to be kept separate and private. And so, that’s the example that we’re going to really pursue today.
Jeff Lynch: So here’s the process overview and I’ll tell you when we’re going to stop for questions as well. So in between the two big sections, there’s going to be as a section where I’m going to go over what I did to set up the SharePoint site, how I created some groups, how I created two lists, and then how I connected those lists so that you can view things all on one screen, and get an idea for a specific project all of the assignments that you given for that particular project. We’re going to take a quick break then for a few questions hopefully, it’ll be minimal because we do have a lot of information today. But then we’re going to set up the Power Automate part of the presentation. We’re going to create a flow that’s going to create the Project in SharePoint and then we’re going to create another one that’s going to do the permissions. And finally, we’re going to show you a really simple one that updates the Project. And by the time we get to the final flow, everybody’s going to be a pro at it, will be able to chime in and tell me how to do that.
Jeff Lynch: So the first thing we have to do is we have to set up groups in SharePoint. I’m assuming at this point that we already had a SharePoint site that we’re working from. So we need these groups to assign basic permission to allow them to see the list they have individual permissions for. In other words, if I don’t have at least Read permission to a list, I won’t be able to click on that list at all to be able to see which things I have permission for. Cascading is the key thing that you have to understand about permissions in SharePoint. The site has a permission set, and then that can be inherited or broken per list and then per item, you can further break that inheritance. We want to be able to divvy out our permissions on an individual item basis. And for that, I recommend generally that we create some new groups. However, when you create a SharePoint site, it does generate some automatic groups, specifically the owners, the members and the visitors.
Jeff Lynch: If you decide that all PMs can see all of the assignments, then you’re going to assign the group to each assignment. If you decide that only the PM in charge of that project should see the assignments, then you’re going to assign the PM of that particular project to that assignment. And I have an example where I use the individual PM in this case, but I created a group to show you how that works. We added members-
Melanie: Jeff-
Jeff Lynch: … for each group accordingly, give the contractors Read permission but we’re going to remove that contractor group when we assign the individual permission. Well-
Melanie: Sorry, just a quick interruption. I’m getting some notes that the voice, the volume is breaking up a little bit, do you want to shut off Your webcam perhaps?
Jeff Lynch: How’s that? Does that make it better?
Melanie: It sounds good to me, but your webcam is off now maybe we’ll get a little better connection.
Jeff Lynch: Okay. And then the other thing to do is that if you’re going to be using the group for assignment, then you want to remember this ID, and I have it circled down here in the corner. When you’re in the adding group members screen, go ahead and remember that ID otherwise, it’s very easy to go back and look it back up again but we’re going to use that ID when we set the permissions. And I also have a little tip that use Contribute permissions because edit permission is way too powerful for most users, it gives them the ability to delete entire lists and that’s something that you just don’t want to have to recreate and dig out of the recycle bin when they make that mistake. Contribute is better, it gives them the ability to fill out all of the forms and do all of the things that they need to do without changing the structure of your SharePoint site.
Jeff Lynch: So then, the next thing we need to do is create our list for the projects. And I’m making this very simple, our project list is going to have a project manager and that’s a person field. A person field in SharePoint is a special kind of field, it looks at the Active Directory people and in reality, there’s two separate lists. The Active Directory List is duplicated into SharePoint and the ID of that person changes based on the site that you’re actually in. I used the start date as an example of how we could create other data that’s related to the project in here, there’s plenty of other things that you can add here. And I also put in a status field for the, it’s a choice field where we have active and complete. The project ID is a single line of text, and I use that to store the ID of the project from either Project Online or Project for the web. This is critical if we want to update things later on because we have to be able to identify which project it came from.
Jeff Lynch: And then I’m going to create an assignment list. And in this case, I need to have a lookup that goes back to the project and I need to have another people picker field for contractor, and I put in hours assign. There’s a lot of other information we can add but this is a good baseline. Modify the title column and then put in TBD, in my case, I said leave blank, auto complete, something like that because I’m going to show you at the end how when we change the permissions, we also can change the title so that we have a consistent set of titles. In my case, I put in there the project name-contractor, you can put in whatever information you want to be consistent. Some people have IDs and a lot of different things that go into those.
Jeff Lynch: So the last piece of the SharePoint piece that I’m going to cover today is how we connect the Project list and the Assignment list. The reason we want to do this is so that we can show all of the assignments for a given Project in one simple screen without having to click back and forth. This is the out of the box SharePoint method. I want to put in a little bit of a caveat, I agree that there are a large number of third party applications that do similar things and they’re a little bit easier and a little bit slicker than the out of the box program, but it is possible to keep this completely within SharePoint as out of the box. The downside to this one thing is that you must use Classic SharePoint, this doesn’t work in modern views because in modern views your display form and edit form pop up in a sidebar instead of in a separate window.
Jeff Lynch: So the first thing you’re going to do is you’re going to click either the edit or the title field. Title brings up the display field or the display form of the project. You’re going to do this for both of them, but it’s going to bring you to the list of, excuse me, the actual item that you’re working on. So in this case, it says P Project Demo. And then down below that, what we’re going to do is we’re going to add a separate list that’s filtered by the project that we’re looking at. To do that, the first thing we do is open up the cog in the top right hand corner, and we click edit page. And then there’s another screen in between there where we can add a Web Part and we’re going to select the Assignment List that we’ve created.
Jeff Lynch: And then when we create that list, we’re going to move it to the bottom, and I can show you all this in just a second because we’re going to go over to the live demo since the screen is over. We’re going to move that to the bottom and then we’re going to connect that separate list with filters to the Project’s List. And to do that, we have to enable pop ups. And then once you do that, then you click on, Get Filter Values From, and then you click Project Lists, that’s the other list that’s on the page, it’ll give you a pop up window that’ll suggest that you use the ID from the provider field and you tell it which field in your sub-list contains that lookup. And so, if you remember back to when we created that Assignment List, we created a lookup field in the assignment list that looks back at the projects. So that’s how that works.
Jeff Lynch: Let me go show you what this looks in… This is the employee Project Online demo and you’ll notice that there are no items in my time budgets. In order to create one, I simply have to create new. And I’m going to assign, it says leave blank-auto complete. And then the project is going to be the online demo, the contractor, I’m putting my wife to work today, her name is Tracy, she’s going to have to come in and do an easy 15 hours of work on this particular item. When we save that, when we go back into our project demo, it’s going to say specifically the same thing, leave blank-auto complete. The reason for that is when we look at this right now we go to advanced, we go to share it with, and we say advanced, you’ll notice that we still have contractors PMs and all these other groups. In just a minute, that’s going to change because in the background the flow is running, that’s going to change the permissions and it’s also going to update the title.
Jeff Lynch: So to demonstrate that, of course I have the other one already created that has it. And in this one, you’ll notice that it’s MPUG Demo P4W, that’s this title up here -Tracy. And if I go up in Classic SharePoint, this is a quick little trick, is if you go to advanced and say shared with and then just click immediately on advanced, you’re going to see everybody that can see this particular item. And you’ll notice now that those groups are all gone but now Jeff, I’m the project manager, I get to see it, the owners get to see it and Tracy gets to see it. You’ll also notice over here that I’m the owner of this website and I’m also the one who created the Power Automate flow that creates it, so that permission stays no matter what, which is the full control. But you’ll notice that it added Contribute, obviously, I get the highest permission that I’ve assigned so it’s not important. The important pieces to see that it did actually assign that permission so you can see that it’s all there. And for a really fun adventure, we can always look at this and make sure that it ran in real time.
Jeff Lynch: And so, now you can see that the one that we were on before, a few minutes later changes to MPUG Project Online demo for Tracy Lynch. And we can see that the permissions that we looked at a few seconds ago that had all those other groups are now limited to Jeff Lynch Full Control, Jeff Lynch Contribute, EMPUG Demo Site Owners. And that’s changed to Contribute by the way because they actually have Full Control and then Tracy who has Contribute permissions. So that’s the SharePoint side of things. And if you’d like, we can take a quick break real quick if there’s any questions. Melanie, if you’ve got anything that’s come up, hopefully my audio improved and-
Melanie: I have no-
Jeff Lynch: [crosstalk 00:18:49].
Melanie: No questions so far, Jeff?
Jeff Lynch: Oh, my gosh, that’s amazing. All right. Well, we’re going really fast here. This was supposed to take a full hour and I planned for a few questions, so we’ll just move straight into the Power Automate part of things. So the first one I’m going to go over is creating the Project Online flow that created Project in that project list that you saw. And those controls, those triggers are different than for using Project for the web. So let’s first do Project Online. Excuse me. Project Online has very specific triggers for a variety of actions. In some ways, it’s limited to these actions, so if Power Automate, you have triggers and you have actions. Triggers is the thing that says, “Okay, it’s time to go to work, get to work,” that’s the trigger. Then the action is all of the things that you can do afterwards.
Jeff Lynch: And in the case of Project Online, the triggers are limited to these eight things. We’re going to use one of the new project is created. The reason we’re not going to use the V2, and this is especially important at the end what we do when a project is published, if you use the when a project is published V2, it’s going to look for a very specific project. And we don’t care which project gets published, we want to have an update every time any project gets published. So for that, we’re going to use the one right above that when a project is published. In this case, we’re going to use when a project is created and then we’re going to go through and we’re going to use information from that to create our list item in SharePoint for the projects. And I’ve got a whole set list of steps over here, it might be a little hard to read so I’m going to kind of walk through each one of them one at a time.
Jeff Lynch: So we’ve already discovered that the first thing is when a new project is created in the root site URL, you’re going to put in your PWA URL. So https://yourdomain.sharepoint.com/sites/pwa is general, if your company has a different one, then that’s the URL that you’d put in there. That’s all you need to put in that one. The next one I have up is what I call is a compose command. And I used this as an example, I didn’t actually use this in the live demo because I only put in the project name. But if you want your project name in SharePoint to be different, to include more information or to otherwise have, anything that you want to do different to it, you can do by doing a composed function right here. Everything in that new project that is available, I put in here as the example Project Name, -Project ID, -Project Owner. The project owner is already listed and the project ID is [inaudible 00:22:06] that’s about 20 characters long so it’s kind of incoherent anyway. But if you wanted to put in a project and project type, or if you wanted to put in, as any other piece of information that you might want to put in there, that’s how you would do it, is user compose action.
Jeff Lynch: The next thing we have to do is we have to initialize a variable, and we need a variable to hold or contain some information that we’re going to use later on. The reason we have to do this is because in SharePoint, it utilizes what’s called Claims. And Claims is just a formatted string of text with somebody’s email in it. But it’s the value that it wants when you’re creating an item, it doesn’t want just the email address, that would make it way too easy and the consultants like me would be out of work. But in this particular case, it’s really easy to construct that claims value. And the way we do that is we create a variable. And the reason I set up the variable at the top, you’re going to run into this if you’re doing it, is that SharePoint later on puts it into this thing called an apply to each. Because in the next one where we’re talking about search for users, we’re going to use the owner’s email address or, excuse me, we’re going to use the project owner or the project manager’s name to look them up using our active directory.
Jeff Lynch: When we looked up Jeff Lynch, that’s not a unique value, it’s not a unique view so we have to assume that there’s two Jeff Lynch’s working at Lynch Interactive. So what SharePoint does or, I’m sorry, what Power Automate does is it puts in what’s called an apply to each, and it uses the value that you got from that search and it does the thing that you put in below. We all know that there’s only one Jeff Lynch, so we don’t have to worry about it. In a case where there was two Jeff Lynch’s, what this would do is actually set the variable to the last Jeff Lynch because it would go through it and say, “Okay, give me the first Jeff Lynch, there he is, put him in. Give me the next Jeff Lynch, there he is, put him in.” And we’re overriding that variable, notice that we’re setting the variable. There’s another command for append a variable, so you use that if you were trying to put for example, if you were looping through a group and you wanted to put all of those people’s email addresses into a string so that you can use them to send an email out later on, that could be an append. In our case, we’re just doing set.
Jeff Lynch: Once we set that variable, then we can create our item and down here at the bottom, at the top here on my notes. The other thing I wanted to point out is that the format for an individual is the i:0$.f, that’s the next character there is the one above the right bracket, I’m sorry, above the /, it’s the |, people call it all different things, but then a membership| and then anytime when in my stuff, when you see a curly bracket, it’s a variable, and you just put in the email address right there. That’s how you build the owner’s claims. The reason we build that again, just repeat that, it’s so that when we go down here to the bottom and we create an item so we go through each one of these rows, the first thing is there’s a drop down, it’s going to ask us, what site are we creating the site in? The next thing it’s going to ask us is what list name are we creating this item in? We’re creating it in the project list.
Jeff Lynch: The title which is required, anytime there’s a redstart required, I’m going to make my title, in this particular case, I made it just the project name because it was simpler and it’s cleaner looking, but I wanted to demonstrate the Compose up above so that you knew you have options. Then I picked the start date as a piece of data that I want to communicate across and I got that and then all the green ones come from, nice thing about Power Automate, everything is color coded, it comes from the thing at the top one where new project is created. And when you’re actually working in Power Automate, it gives you the choice of all things that are available to pick from.
Jeff Lynch: In the case of the project owner claims, I obviously don’t have claims in the project because it doesn’t use claims. So that’s why I’ve constructed the variable for owner claims. I’m going to set the project active automatically however, I could for example, decide to do something different there. I could change that to percent complete and work on some kind of a routine that would update my percent complete, I can do a lot of different things here to make project status value some other field.
Jeff Lynch: The final one though is really important because the project ID has to come from the project ID in the project we created in Project Online. That’s so that later on we will be able to update our SharePoint item with information from the project that was modified in Project Online. So now we’re going to move into Project for the web. Project for the web it’s a lot different because Project for the web utilizes what’s called the Microsoft Dataverse. The Microsoft Dataverse is, if you think about it as it’s everything you could ever think of that Microsoft ever built and it has the database tables for all of those things that you can pick from.
Jeff Lynch: They have different triggers, they only have a couple of triggers as a matter of fact. And the triggers, again, the trigger is the thing that gets the whole process started. We’re going to use the when a row is added, modified or deleted. It actually gives us a choice of selecting whether it was modified, deleted, added or deleted, added or modified, added, modified or deleted, modified, if you have a lot of choices there. The one we want to select this added and I’m going to talk to you, this is going to be one of the more complicated things that we’re going to cover today. But in the next screen, I’m going to talk about why we’re doing it that way. One of the things that you have to understand about the Dataverse is every time that you touch your project in Project for the web, it creates another entry in the Dataverse. It modifies a row, it does something in the Dataverse because it’s real time data, it’s a direct connection. And when we get to that a second, you’re going to understand more what I’m talking about.
Jeff Lynch: For this purpose, what we’re going to pick out here is we’re going to pick one, something is added and then it’s going to ask us, “What table that we want to use?” And as you can see, this is just a small little snippet and you can see where the slider is over here. We’re two thirds of the way down the list and we finally got the process, there are hundreds of tables. And then there’s another thing called scope, but you just generally want to make that organization. So that’s not a big deal, you only have three choices there. So that’s how we get the thing started. Now let’s take a look at the flow. So in the Dataverse when you create a new project, it’s named untitled project and it exists. Compare that to Project Online, the Project Online project doesn’t exist until you hit that finish button or the create button. In Project Online it says finish. So you’ve already created the title, the name of that project, you’ve already assigned who the project manager is, and you’ve already created a start date for that project.
Jeff Lynch: In Project for the web, all of that stuff comes up as today’s date, whoever’s creating it and untitled project. If I made this so that it only created the project in SharePoint, when I did an update that was not equal to untitled project, I would have it duplicating itself every time that we did something in Project for the web. So let’s say that we go in and we update a couple of tasks and add milestone. When we do that, it doesn’t change the project, per se, but it does update the percent complete. When that percent complete gets updated, it’s considered an update to the project’s row that we’re working on. And it’s going to trigger this again, if we had it as a modified or changed. So we want to limit it to only that one time when that row is added.
Jeff Lynch: But how are we going to get the actual value of the project that we named? And I came up with a solution for this, and this is a little bit of the, some of this stuff is not written down in any manuals or anything that Microsoft publishes, you just have to figure out where is the data, what is the condition of the data at the time that I need to access it? And Dataverse makes that even more prevalent and more obvious than a lot of the other ones. In Project Online for example, we have two triggers that we’re going to use. We’re going to have when it’s created, which is a definitive click of a finished button, or we’re going to have when a project is published, which is another action that you take in project when you click the publish button. In Project for the web, there is no project publish button and there is no create button, except for when you first create it. And again, it’s created as untitled project.
Jeff Lynch: So how did I come up with a solution to give us the right name? Well, I made up my own rule that says I have one minute to get it done. You can have your own rule, you can make it five minutes, you can make it so that it only runs once a day, you can do several different things to delay things. But then after that delay, I didn’t put a little jumping numbers on the screen because it was just, I didn’t. So what I did was after something is created, I said give it a delay of one minute, five minutes might be more realistic for you and your work but that’s up to you and your particular situation. Just be aware that you have this delay so that you get the things done that you need to get done before it goes back in because then after a minute, what it does is it says give me the data from the row that was created up here. In other words, give me the updated version of my project. Now I can work with my updated version and I can go in here.
Jeff Lynch: And the next thing I have to do is I have a different method that I have for getting a user because in the Dataverse, it doesn’t look at your active directory users, it looks at a user’s table. That user’s table happens to be derived from your active directory lists. However, it’s not stored that way and you can’t access it that way. So how do we get it? Well, it’s another row by ID. And this row by ID, we’re looking for the project manager. So we change the cable name to users. Up here, we have it as projects and we’re getting the project ID from the project that was created up here. Down here we’re looking to the users and we’re looking for the ID of the user that is the project manager. So the Project Manager ID is this field here.
Jeff Lynch: And again, when you’re building your Power Automate flows, you have choices of all of these things that you’ll be able to pick from to figure out which one is which. And at the end if you’d like, I can give you some kind of troubleshooting and debugging tips on how to figure out which one it is because in the database it provides, you have to remember, it provides every piece of data that Microsoft ever uses for any of their programs. So there’s a bunch of stuff in there that you’re going to look at and go, “What’s that? I have no idea, I’m never going to need it.” You probably aren’t. But again, that’s how you have to figure out some of the Dataverse complications, you have to decipher what field do I need? What field do I want?
Jeff Lynch: Again, I put in for this screenshot of Compose for the title but I changed that to just be the title simply just to demonstrate that you can’t do that. And then down here, we again come back to those because when we create our SharePoint item, we still need to have claims and so we duplicate that claims the claims variable. A quick note about variables, they’re one of the most powerful, in my mind, one of the most powerful tools in the Power Automate package. The thing about them, you have to realize is that you have to, before you have any branching, you can’t put initialize a variable, you can’t create a variable inside of a loop, a condition or something like that, you have to initialize it outside. If you know the value at the time that you initialize it, you can set otherwise, you just initialize it and then set again later on down the list.
Jeff Lynch: And then again, we go in and create an item. So we’ve not created an item called the Project for the web application. I used the outputs from the Compose even though just because I put in there was the name of the project, I put in the end date. Again, you can see the purple down here, I used the variable for the project manager claims, I made it active because that was a choice. And then I put in the project ID, the unique ID from Project for the web. I can use the same field for Project ID whether or not it’s Project for the web or Project Online, because it’s a single line of text and it’s just storing that value for me to use to compare it a little later on. How are we doing on questions, anything coming up, Melanie?
Melanie: We have no questions yet. Audience please, if you’d like to raise a hand or type in a question about anything you’re seeing or these products that Jeff was covering, please get involved today.
Jeff Lynch: Absolutely. So the next flow we’re going to create is setting the permissions on new assignments. So this is going to occur whenever you were in SharePoint and you click on your project, and you click that button it says new assignment, and you created it. We already saw this work. Now I’m going to show you how to build it. Basically, it’s a repetitive process with three different steps, the same steps repeated over and over again in some cases. The first thing we have to do is get the SharePoint ID of the group or user. And we use a variable, which is an integer to format that ID from the value returned in the request. Then the next step, and I’m going to go over each one of these so don’t freak out at this point. But we’re going to then break the inheritance of those permissions and then we’re going to assign the new permissions. For all of these, we’re going to use the http requests to SharePoint action to get what we want. So let’s go through and show you what we do.
Jeff Lynch: First thing is I’m going to talk through some of these. And we’ve already seen examples of them but we’re going to, the first thing we’re going to do is we’re going to say when an item is created, that’s when the new time assignment is created. As soon as that’s created, this will run. If you have the per user plan, it’s supposed to run within the first minute, if you have the free version of Power Automate, it will run between five to 15 minutes after you create that. Some other little things to know is just because you have your Power Automate flow turned off, you go and create a bunch of things or let’s say you’re doing an import, I learned this the hard way, when you turn that flow back on, it will go back and run that flow on everything that you entered while it was turned off. So that’s something to be aware of.
Jeff Lynch: So the next thing we need to do though because we’re going to be talking about the project manager in our cases, we need to get the project. And we can get the specific project item as opposed to items because we have a lookup in our team assignment that we created. What that allows us to do is get one item so we don’t have one of those ugly for each loops that goes through and says, “Is this the right one? Is this the right one?” You automatically know because we have a unique ID that we can compare it to. And when we do that, we can just immediately get that information. So that’s how that works. So then the next thing we’re going to do is we’re going to send an http request to SharePoint. In my examples here, I tried to leave the name of the action and then just did a – so you can know what I’m doing in each one. In a typical scenario, I click three dots and rename each one with a little bit more shorthand since typically, I’m the only one looking at it.
Jeff Lynch: So the first one we’re going to do is we’re going to send an http request to SharePoint to get the project manager, it gives you a drop down for which site you’re going to be looking at. And then you have a choice of a method, the method is generally going to be either a get or a post, and then it’s going to be a URI. The URI is basically going to be appended to this string. And basically, what’s happening here is that the Power Automate is executing the command of that site using the URL. And it has a whole library of these things that you can use to do different things. In this particular case, what we want to do is I want to know what is the ID of project owner from my project. So this project owner, the green one right here, is coming from when I got an item, which is get the project. And then down below that, I have to set that into a variable, and I initialize that variable called PMID. I make that variable an integer because I have to have it be a number, you have to have variables, things have to have a certain type. And there’s different examples where you use different things. We’ve used so far a lot of strings because it’s a non-descript alphanumeric string that we’re using. This one is very specific, it’s integer.
Jeff Lynch: And then the way we get that is we construct this body DID. Basically, what we’re going to do is we’re going to use the results from this request to get just the ID. And to achieve that, there’s a format that you put it in. And what I typically do, and I think we’ll have enough time, it looks like we’ll have enough time to go in and I can show you how I do it. But what I use, when I go into this, you have a choice between using one of the fields that exists in the flow at this point, or you have the ability to create an expression. What I do is I created expression, put in the BIB part of it, and then switch back over to the variables and put in the variable. And I’ll show you how to do that. It’s not that hard but it’s one of those things I learned the hardware because the first couple of times it wouldn’t do what I wanted it to do.
Jeff Lynch: So now we have the ID of this particular project manager. Again, if we wanted to give all the project managers permission, we need that ID that we copied from when we created the group in one of the very first screens. So then the next thing, and we’ll get to that in just a second when we start assigning permissions. Before we assign the permissions however, we need to delete the permissions, we need to delete all the permissions except for the person who’s running the flow, which in this case is me. Once again, we’re going to use to send an http request to SharePoint to do this, we’re going to find the site address at the input demo site. This time we’re going to post because we’re actually putting stuff into, we’re changing SharePoint. So we’re not just getting data out, we’re actually changing what’s in SharePoint.
Jeff Lynch: The API for this one for lists is get by title. And then we put in the name of our list, which is time budgets. And then we put in the items and then here we put in the ID of the actual time budget, the thing that was created at the top. And you have to make sure when you’re selecting these, it gives you a choice of everything. So it gives you a choice of the ID for the project, it also gives you an ID for the item that was created, you have to make sure that you pick the right one, when you’re filling all of this in. The rest of this is in what we call the break inheritance section. And then we just tell it, okay, break all the inheritance, get rid of all those other groups that had permission to see this particular item. By this I mean the ID right here.
Jeff Lynch: Now we’re ready to start setting those permissions and we go through and as you saw in the original one, we obviously do this three times. In this particular instance, I have some samples that were done for clients where there’s six, seven, eight different groups that need to have different levels of permission. So let’s break this down, tell you what each one of these http requests share points columns do. The site address obviously, that’s the same as what the other two were, we’re working in the MPUG demo site. The method, we’re going to post this method and the reason we’re doing that is we’re making a change to the SharePoint item. And the URI is that we’re going to use the API for lists and we’re going to get my title again, type budgets and the item is the same item that we had above. And now we’re going to assign roles. And so, this is all, and I’m going to show, I have a cheat sheet for everybody so you don’t have to write any of these down and you’ll be able to use my cheat sheet for this.
Jeff Lynch: So the first thing it does is it says, add a little assignment and then in the parentheses it says, principal ID equals. Remember we came up with PMID and in this case we’re doing the contractor ID, we have variables that we set that are integers. And what you’ll notice is, is that when you’re in this area and you look at the variables that you’re able to select, it will only give you the integer variables, it won’t give you the single line of text variables. Then the last part of this is this role definition ID. And this number here is unknown number, there’s a different number for Read, Contribute and Full Control. And I’ll give you that cheat sheet as well, I have that on the next screen. So now we have our permission set for the contractor, we do the same thing for the project manager and we do the same thing for the SharePoint group owners.
Jeff Lynch: We got our permission set, now it’s time to update our item. Remember, this is the sub-item so we want to make sure that we have this in a format that’s consistent and easy for you to read. So we put in the title, the title of the project, and the contractor display name, the contractor in the time budget. And that’s all we do. Another note here is that notice that I keep project ID, contractor claims and hours assigned all blank. When you’re doing an update on a SharePoint item, anything that is blank will stay the same. This is especially problematic if you think you’re going to delete something, you’re not going to delete it. The product ID if you leave it blank, is going to stay what it was. If you want to reset project ID to know, there’s another whole session on how to do some of those commands. But just to understand that if you leave it blank, it will not change. And I want to keep it blank because I don’t want any accidental typos or anything like that to mess up my relationship between that project ID and the project ID of the Project Online or Project for the web project. And so, I will pause momentarily with full screen up, if anybody has any questions on that?
Melanie: No question so far, quietest-
Jeff Lynch: No questions-
Melanie: … audience ever.
Jeff Lynch: I know. Is there anybody there? Could somebody raised their hand? Wouldn’t be the first time I talked to myself for over an hour.
Melanie: Oh, I’m seeing hands go up, I don’t know if they really want me to. Please chat us [inaudible 00:47:57] if you’d like to ask the question out loud, raise those hands back up and I will turn you on. Okay. We got someone coming in here, “A bit confused, can this be done with Project for the web, PWA?”
Jeff Lynch: So this is all contained within SharePoint, this particular screen is all contained within SharePoint. We created the SharePoint items from Project for the web and then this action here is all taking place on the SharePoint items specifically. We can’t control permission on Project for the web, you’re either a part of the group or you’re not in Project for the web and that’s the distinction. And that’s one of the reasons why we might want to integrate with SharePoint because the data that we want to store is private and not part of the whole group. Does that answer that question? Yes, no, maybe? Okay. Oh, if there’s more confusion about that, let me know and I’ll answer that afterwards.
Jeff Lynch: So here’s my cheat sheet. I have a lot of cheat sheets, I just don’t have a good enough memory to remember where every little dash and every little apostrophe goes, so I have cheat sheets. And when we create that body for the variable that’s in that request ID, it’s equal to the body. So what I do basically is, I’ll show you this in just a second, what I do is I go into the field and I type in the DID part. And then I click on the body of the http request right above it, and then that gives me, it returns just a single integer that I can then use down in these other places. When I break inheritance, I always want to make sure that I have the right list name, and then the item ID is a critical piece of this and make sure you pick the right item ID.
Jeff Lynch: And again, on the setting of the permissions, in this case, I just put in the example of a one. And then if I, so get my title, my list is, you got to make sure that’s the right list, spaces, make sure it’s capitalized correctly as well. The items, you’re going to usually use a variable, you’re not going to have it be just one, but you’re going to use the variable of the item that was created at the top. When you do your principal ID, this is where I was telling you that for example, if you want to give all project managers permission to all of the project assignments or the time assignments, then you would put in the group there instead of the group ID that we saw before not the individual’s ID that we discovered up here in the get user part. And then the role definition ID, those are right here, the Full Control is this number, Contribute is this number, and the Read is this number. And there are ways, there are methods you can find on Google to find custom permission levels if you need to do that.
Jeff Lynch: The last thing we’re going to do is talk about how we update when a project is changed to complete, what I consider complete is when it’s 100%. Project Online has a trigger for when a project is published. Again, we talked about this before, it’s a very definitive clickable button that says published. And when we do that, we have the ability to use a trigger that is very specific. We don’t have to worry about repeating over and over and over again, we don’t have to worry about a lot of things. In this case, what we want to do then after that is we want to find A, we want to set a condition that says is the project percent complete equal to 100? One of the sudden differences is that Project Online uses 100 as the value while Project for the web uses one, the numeric number one, as a percentage. And in a mathematical sense, they’re both correct, so I can’t say why they’re different but they are.
Jeff Lynch: Once we’ve determined that this project is at 100%, we go to the yes side of our condition and we say, “Get the items.” And this one is a little bit trickier. We want to get the items, and this is where I’m going to show you in a second how we do the old data to find the value of the project that matches the project, what was changed. And then we update it and we set it to closed. In the case of Project for the web, you have to use a Dataverse call, in this particular case, we’re going to use when it’s changed, we have a different variable called percent complete and it’s equal to one, but then we do the same thing over here, get the items and we do old data to find all of the items where the ID matches the project that was updated. And if it’s updated, then we just change the project to closed. What this allows you to do is create a view in your SharePoint site that shows only active projects so you’re not looking at everything. I didn’t want to go to that screen yet, sorry. Where did I want to go? I want to go here.
Jeff Lynch: So let me just show you a couple real quick things. Looks like we have seven minutes left in my flows. I wanted to show you when a project modified is completed, the condition. So when we get the items, it says, “Okay, what items do you want to get?” “I want to get all of the MPUG demo site project list items, but I want to limit my entries, I don’t want all.” Some of the subtleties, Project ID, that’s what’s called the field internal name. If your field has a space, it’s going to have this _X0020_ in it, if it has other characters, it’s going to have other things in there. And there’s ways to get that very easily, I’ll share it up really quickly.
Jeff Lynch: And also I’m doing with this filter query, it’s an old data filter query, I’m saying word equals project item. Now you and I both know that there’s only one project where that project ID equals this project ID that we’re working on up here, but Power Automate doesn’t know that, it thinks there might be more than one so it says apply to each and that’s where we update the item. If there were multiples, it would apply to each one of them, it would go to each one of them and mark it as closed. And that’s Simple part of that.
Jeff Lynch: A simple way to get that value, the field internal name, is that you go, let me close this and again, I put this into classic experience and I believe that the modern experience is way more elegant but we don’t get that choice. One of the things that you can do to make it stay in the classic experiences is go to the advanced settings of the list settings and change it to classic experience right there. The other thing, to get the value that is the field internal name, so we were looking at Project ID, you can see down in the tray down at the bottom, it says that in the end is field equals, but if you click on this deal to edit it, up here in the URL is your field internal name, and you can copy and paste that so that you don’t have errors. If you put in just project ID, that one won’t work.
Jeff Lynch: So we have four minutes left and I’d like to go back and kind of wrap it up and then see if there’s any questions. So in summary, I think that for me, the things that I’d like everybody to take away from this presentation are that first of all, there are endless possibilities to mimic your company processes using SharePoint in conjunction with project. There’s just is no, “I have not found something yet that I can’t figure out a way to make it happen.” In my life, I think of myself as a puzzle solver and that’s exactly what it is. Somebody lays out something in front of you and says, “Here’s what we do now, we do this on paper, how can we do this on the internet?” That’s our job is to figure out processes that are going to mimic what people do for their job without tragically changing what they do for their job or making it harder say, “Gosh, on our sakes, we can’t do that.”
Jeff Lynch: Second point is the Power Automate, the budgets, all of the triggers and all of the actions that we need to work with SharePoint, Project Online and P4W, it gives us a whole lot more than that but this is a one hour seminar so I’m not going to be able to get into that much. The third thing is is that, if you understand the condition of the data at the time you’re working with it, that’s going to be critical to a successful and reliable [inaudible 00:57:22]. And the best example of that is in Project for the web where it begins its life as an untitled project and then becomes whatever you change it to. And then finally, understanding the differences between Project for web and how it works in the Dataverse, and Project Online which is essentially a Classic SharePoint application. And SharePoint are going to help provide clarity as you plan your next project.
Jeff Lynch: And with that, I’d like to open it up if anybody has questions because this is a ground level introduction to all of the things that we could be doing with Project for the web, Project Online and SharePoint and many other applications as well. So Melanie, you want to open it up or?
Melanie: Yes, I don’t see any questions coming in. I think what might be useful, we maybe had some audio problems as well, so I think it’d be really helpful if we could share the deck with the audience from today as well-
Jeff Lynch: Sure.
Melanie: … afterward. And then if we get questions, we can route them to you. I’ll also share your contact information with that. It was a great session, so thank you for that, Jeff. And well still I got some feedback that it was a great overview and some thank yous for the cheat sheet as well. Thank you audience for growing your skills with MPUG today. A quick look into the future, we have a power Microsoft Planner reporting with Power Automate and Power BI. You’re going to get the basics of using Planner for lightweight project management there, you’re going to get some use flow to get a Planner test into Excel, and then you’re going to use Power BI to show all of your Planner projects at once. It’s going to be a cool session.
Melanie: And then Excel, one of my favorite tools on the planet is coming up on March 30th, so they’re going to be good. I will be sending out a link later today with this recording, assuming it all recorded well, and a quick survey. Jeff is considering coming back with us. I think this was a deep topic so we’re thinking about a complete course on the subject, so please share your thoughts and interest in that survey. The activity ID today for those of you submitting is on the screen. Thank you again for joining us today, and I will leave the PDU on the screen for you afterward. And Jeff, thank you so much again for your presentation.
Jeff Lynch: Thank you, everyone and it’s been a pleasure. And I hope everybody learned a lot of things that are going to useful in your daily lives. Thank you.
