Due to heavy traffic. I am opening up a YouTube Page and will be posting content there too. Thanks so much for reaching out to me about the video and this post. More to come soon!
On first glance, PowerApps and Flow seem like excessive product enhancements for SharePoint that many SharePoint Power Users and Developers overlook for a variety of reasons.
There are many confusing tutorials and convoluted solutions out there and, in this series, we are going to work through a practical solution that any organization can utilize to enhance their lines of business with these tools.
THE BUSINESS PROBLEM
Human Resources has a manual checklist process for onboarding new employees.
There is a private HR site where the manual checklist is scanned and placed into a SharePoint Document Library with a folder for each employee to house all their important HR documentation.
The process is cumbersome and because it is a manual process, many steps are often forgotten leaving some new employees without vital information and no tracking details for Human Resources to view where the new employee is in the onboarding process.
We will create a SharePoint list and use an Employee Onboarding Content Type with the site columns necessary to capture our required data.
We will then use Power Apps to create a custom form with custom screens for the Add, Edit, and View pages. These screens will have different views based on the appropriate information we are attempting to track.
Once our form is created, we will use Microsoft Flow to create a workflow to assign a Sequential Case Number for HR to use in tracking, create a new folder in the Document Library with the new Employees Name, create a Word Document in that folder based on a document template, and track the New Employee Onboarding process.
WHAT DO YOU NEED FOR THIS SOLUTION
- SharePoint Online or 2016
- Basic understanding of Content Types and Site Columns
- Power Apps
- Microsoft Flow
WHAT WILL YOU NOT USE FOR THIS SOLUTION
- SharePoint Designer
- Any Custom Code to create forms or workflows
STEP 1: Create a Content Type and its Associated Site Columns
There are many options and locations where you can create Content Types and Site Columns. I prefer to use a Content Type Hub so that if I make any changes to a Content Type or Site Column, I can publish those changes to any lists or libraries in all of my SharePoint tenant site collections (or farms for my on-prem environments).
For this solution I strongly recommended creating the Content Types and Site Columns at least at the Site Collection level but the best practice for this solution is to use the Content Type Hub.
If you’ve never seen your Content Type Hub and use SharePoint Online, visit the following address to find it: https://yourdomain/sites/contenttypehub.
Once on the Content Type Hub we will go to Site Settings and create a new Content Type.
For this project we will call it “HR New Employee Onboarding Demo.”
For the Content Type Settings, we will make the Parent Content Type “List Content Types” and ensure we use “Item” to ensure we deploy with only the default columns. We will also place it in a custom group so that it will be easily found.
Now that our Content type is created, we will add the following Site Columns:
- Employee Name(Single line of text column)
- Hire Date(Date and Time column)
- ID Tracking Number(Single line of text column)
- Met and Reviewed Benefits?(Choice column; Yes, No are the choices and default the column to display “No”)
- Reviewed Employee Handbook?(Choice column; Yes, No are the choices and default the column to display “No”)
- Laptop Issued? (Choice column; Yes, No are the choices and default the column to display “No”)
Laptop Type (Choice column; Dell Laptop, Microsoft Surface, other)
- Onboarding Process Complete? (Choice Column; Yes, No are the choices and default the column to display “No”)
- Who Completed Onboarding (People or Group Column; in this example I have a tenant with only one user but for you, you will want to use a People Picker Column)
It will take an hour or two for the Content Type publishes out because you are waiting for a Timer Job to run in SharePoint Administration you have no control over.
To check its status, visit any site collection, navigate to site settings, click Site Content Types and scroll down to your newly created Content Type Group and see if your new Content Type published. If it has, we are ready to proceed to our next phase.
STEP 2: Create a Custom List and Library
For this solution, we need both a custom list and library to fulfill the requirements.
First create your library. We will name it “Employee Onboarding Documents.”
For this solution we do not need a content type or any information but in a live solution you can attach a Content Type, site columns, etc. to it if you want to capture other data associated with your documents.
Add a Word Document as a template to the top level of your library. We will use it to help create our folder in the Microsoft Flow in Step 4. For our demo, I’m calling the .docx file “New Employee Template.” For your organization you can add whatever blank document template you need here.
Now, we must create a list to create the custom forms.
Create your list and name it “Employee Onboarding Form.”
Go to your List Settings Menu and ensure the “Allow Management of Content Types” radio button is checked “Yes” in Advanced Settings then click “OK.”
Once back on the List Settings screen, scroll down to the Content Type area and click “Add from existing site content types.”
Add the new Content Type and click “OK.”
Back on the List Settings screen click “Change new button order and default content type.” Uncheck item and change your new Content Type to 1 on the “Position from Top” selection and click “OK.” This will ensure that any new list item created will default to our new Content Type and all the columns will sync up properly.
Click back to the main area of the list using the Breadcrumb. You will notice in this view none of your new site columns are showing. We need to edit the view and add the columns so that we can see them easily in our default view.
Click “All Items” and then click “Edit Current View.”
Check the Display boxes and arrange the order of the fields as you see fit. Then click “OK” to save your changes.
You should now see all of your custom columns.
STEP 3: Create Custom Forms Using PowerApps
Normally when we create a new list item it appears like this:
It’s very plain, not custom and shows fields we aren’t necessarily ready to fill out, especially since we need a sequential ID Tracking number!
To create a solution that fits our requirements best, we want to create custom Add, Edit, and View Screens for list items.
With PowerApps, we can create a custom form with no custom code to design the form and a little bit of code to tell the list which form to use in what situation.
The first step in creating these screens is to click the PowerApps link on the main list page and choose the “Customize forms” option.
After a few moments PowerApps will build your app and redirect you to the customization screen. It should look like this when the process is complete.
By default, PowerApps will create the form in a Mobile View. However, you are not forced to use a small mobile view. You can create a tablet view that also looks good on Laptop or Desktop displays. For the purposes of this demo we will create a tablet view.
To change the app to a tablet view, click “File”, “App Settings”, and on the “Screen size + orientation” option, change the Orientation to “Landscape” and the Size to “Large.” Then click “Apply.”
Once the process completes, click the arrow in the top left of your screen to return to the customization page.
You will now see the form in a landscape view.
From here we can start customizing the look and feel.
The first thing we want to do is create a form header and arrange the fields because it is much easier to do these before creating multiple screens.
To create a form header, select SharePointForm1 and in the Properties menu click “Data” to get the Data Source Screen.
Now scroll down in the Data Menu to the Fields area and click the ellipses to enable the ability to create a custom DataCard.
Close the Data Menu and scroll down on the form screen to the newly created field and select it.
Once selected, click the insert tab and insert an HTML Text box.
The HTML Text box will be automatically selected. Change the width in properties menu to 1180 and the x and y locations to 0.
At this point click into HTML Text then add your HTML to the HTML text box.
Now select SharePointForm1 in the Screens menu and reopen the Data Screen.
In the fields area scroll down until you see your new DataCard and drag it to the top of the fields screen.
In the form screen you should see your form header at the top of the form.
Click back into the Data menu for the form and change the form Snap Columns from 1 to 2. You will see all columns “snap” to two columns which provides a problem for our header. We have to manually set the width for it. Select your DataCard and in the width type 1180.
At this point, we are ready to create custom screens because it is easier to remove fields and customize screens than it is to add them in later.
To do this, right click on FormScreen1 and click “Duplicate Screen.”
Repeat the process to create a third screen.
Now we want to rename the screens to make customizations easier.
Right click on each screen and rename to the names in the screenshot below.
Next expand “CreateScreen.” Rename “SharePointForm1” to “CreateItemForm.”
Collapse “CreateScreen” and expand “EditScreen.”
Rename “SharePointForm1_2” to “EditItemForm.”
Collapse “Edit Screen” and expand “Show Screen.”
Rename “SharePointItemForm1_3” to “ShowItemForm.”
For our form, we are going to create a unique experience for the CreateScreen because when we create a new user we will only have information for 3 fields: Title, Employee Name, and Hire Date.
Go to CreateItemForm and open the Data menu.
All the visible fields are checked so in order to meet our requirements we will uncheck everything but DataCard1, Title, Employee Name, and Hire Date.
Our form will now look like this:
To make this a nicer looking form, we are going to default the Title field and resize it because we will use the Title field later in our Microsoft Flow to create a custom Employee ID.
Select the Title DataCard and select the input box. This will allow us to edit the properties not for the DataCard but the input box itself to set some default properties. To confirm you have the field selected, you should see DataCardValue1 in your right properties menu.
Select the Advanced tab and click Unlock to change properties.
Under the DATA area, click into the Default input and delete Parent.Default.
Click back to Properties and in the Default box type: “EMP-ID will be generated after submitting form”.
Now, select the entire DataCard and change the width to 1180.
Now click the Advanced tab and in the DisplayName field, change Title to “Employee ID”.
Next, we need to put Hire Date on the same line as Employee Name. Select the Hire Date DataCard and put your cursor in the top right of the box. You should see the expand arrow appear diagonally. Drag your cursor up to the right and it should snap beside Employee Name.
The last thing we want to do now is select our entire CreateItemForm and in the properties menu change Default Mode from Edit to New.
We are finished with our CreateScreen and now need to work on our EditScreen.
Collapse CreateScreen and expand EditScreen.
Select EditItemForm to ensure you’re working in the correct screen. It should look like this:
Open the Data Menu and hide the fields you do not need and rearrange the fields until they look like this:
Now, we will create conditional fields to show/hide based on a dropdown’s value for Laptop Type and Who Completed Onboarding.
For Laptop Type, we want to only show it if Laptop Issued?’s value is Yes.
For Who Completed Onboarding, we want to show it if Onboarding Process Complete?’s value is also Yes.
To do this we need to select the Laptop Issued DataCard then select the dropdown control itself.
You should notice that PowerApps refers to this as a COMBO BOX. Copy the name of the COMBO BOX (in this case DataCardValue4_1) and paste into notepad or some other file.
Next select the entire Laptop Type DataCard.
From here, we click Advanced and Unlock the ability to edit the Advanced Properties and scroll until we see the Visible Property.
Delete “true” and add this formula to the Visible input box:
Once you do this, your Laptop Type DataCard will disappear from the view.
Now, let’s set the same conditions for the Onboarding fields. Select the Onboarding Process Complete? DataCard and then select the dropdown box.
Copy the DataCardValue9_1 from the COMBO BOX name and select the “Who Completed Onboarding” DataCard.
From here, we click Advanced and Unlock the ability to edit the Advanced Properties and scroll until we see the Visible Property.
Delete “true” and add this formula to the Visible input box:
Once you do this, your Who Completed Onboarding DataCard will disappear from the view.
The last thing we want to do on the EditScreen is select EditItemForm again and ensure the Default Mode field is set to Edit.
Here we have two options for ShowScreen. In the steps above we created ShowScreen but in this instance we want the exact same look and feel for the ShowScreen as we do on the Edit Screen. So, for us it is easier to deleted ShowScreen, and duplicate EditScreen and rename it appropriately.
Change the name of the duplication to ShowScreen and ShowItemForm.
Change the Default Mode to View for ShowItemForm and it will now look like this:
Now that we have customized our form screens, we must create formulas to show the different forms depending on whether you are creating, editing, or viewing a list item.
Click “SharePoint Integration” on your left navigation and then click “Advanced” on the right navigation. Be sure to expand More Options.
NOTE: Thanks to Christopher below for struggling with this. DO NOT directly copy-paste the code for the following SharePoint Integration piece directly in to PowerApps. The font WordPress uses does not play well for code functions in PowerApps. Type this code by hand and it will work fine! Thanks Christopher for identifying an issue and being patient as I struggled to figure out why it would not work.
In the “OnNew” field put the following formula:
Set(SharePointFormMode, “CreateForm”); NewForm(CreateItemForm); Navigate(CreateScreen, ScreenTransition.None)
In the “OnEdit” field put the following formula:
Set(SharePointFormMode, “EditForm”); EditForm(EditItemForm); Navigate(EditScreen, ScreenTransition.None)
In the “OnView” field put the following formula:
Set(SharePointFormMode, “ShowForm”); ViewForm(ShowItemForm); Navigate(ShowScreen, ScreenTransition.None)
Now, to ensure the submission and cancellation process works correctly we must expand the more options button so that there are OnSave and OnCancel fields.
In the “OnSave” field put the following formula:
If(SharePointFormMode=”CreateForm”, SubmitForm(CreateItemForm), If(SharePointFormMode=”EditForm”, SubmitForm(EditItemForm)))
In the “OnCancel” field put the following formula:
If(SharePointFormMode=”CreateForm”, ResetForm(CreateItemForm), If(SharePointFormMode=”EditForm”, ResetForm(EditItemForm)))
The fields will look like this when filled out:
Now we can save and publish our form by clicking the “File” tab, saving, and publishing.
Click “Back to SharePoint.”
Now we can test our solution.
Click “New” to create a new list item. If we are successful, our form should show up like this:
This confirms that our custom form was created properly.
STEP 4: Create Microsoft Flow for Automation
On the main list screen, click “Flow” to “Create a flow.”
On the pop-out menu to the right we want to expand the option to see more templates and choose “more templates”.
Flow will open a new tab or window.
Hit the “My Flows” link in the top nav bar.
On the next screen click “Create from blank”.
Start with one of the popular triggers for SharePoint “When an item is created”.
On the Flow screen, change the Flow name to something you can easily remember and click “Edit” under the “When a new item is created” box to verify it is pulling from the correct list.
Once we verify the list click “New step”, then “Add an action”, then “SharePoint” under Connectors, and select “Update Item.”
From here we will select the SharePoint site where our list resides, select the current list, and at that point a menu will drop down with all of our available fields.
Click into the ID field and select the ID option in the right pop-out menu under “When a new item is created.” This specifies we are pulling the ID from the item we are working with from the above step.
Click into the “ID Tracking Number” field. Type in your custom prefix then click “ID” from the right pop-out menu again. This will attach the SharePoint List Item ID to your Prefix when you create the item and update the field.
Your Update item box should like this:
Below our Update item box, click “New Step”, then “Add an action”, “SharePoint”, and then “Update item.”
Repeat the process from above of selecting our site, current list, but now in the Id field choose the ID under “Update item” in the right pop-out menu. It should look like this:
Now click into the title field and delete the “New Onboarding Item” value.
In the right pop-out menu select “ID Tracking Number – Demo” under “Update item” and ensure it clicks into the Title field as show below:
It is vital to the success of the Flow that you choose the “ID Tracking Number – Demo” column from “Update Item” and NOT from “When an item is created.” For the Flow to succeed, we need to get the value from the previous step above that was created. If we pull from “When an item is created”, it will pull a blank value.
Under the Update Item 2 box, click “New Step”, “Add an Action”, “SharePoint”, then “Get file content.”
In the “Get file content” box, choose the current site, then select the path of the file we created back in Step 2.
After selecting the document, the “Get file content” box should look like this:
The last step of the Flow is creating the folder with this document template.
Under the “Get file content” box, click “New Step”, “Add an action”, “SharePoint”, then “Create File.”
In the “Create file” box, choose the current site.
In the “Folder Path” field, choose the Employee Onboarding Documents library.
Click back into the Folder path and add a forward slash (/), then in the right pop-out menu choose “ID Tracking Number” under the Update item 2 section. Occasionally Flow will delete your forward slash and you will have to click to the left of the field and re-insert it.
In the file name field, choose “Employee Name” from the right pop-out menu and add .docx to the right of the field ID.
In the “File Content” field, choose “File Content” from the right pop-out menu.
The Create file box should now look like this:
Now we want to update the list item with a link to our Onboarding Documents folder.
Below our Create File box, click “New Step”, then “Add an action”, “SharePoint”, and then “Update item.”
Select the correct ID and Title fields from Update Item 2 in the appropriate places.
Then in the Onboarding Documents field, put the relative path of the URL to the library and at the end add the “ID Tracking Number”. It will look like this:
Save the Flow and exit.
We have now created our custom form and our Microsoft Flow.
STEP 5: TEST THE ENTIRE SOLUTION
Navigate back to the list and create a new item filling out the three fields and clicking “Save.”
After the form closes, refresh your screen. You should see the Title field has been updated to reflect the custom ID.
Now test the View and Edit Forms by clicking on the Title field.
Now click “Edit All” to test the conditional fields we created.
At first it will look like this:
Change Laptop Issued? and Onboarding Process Complete? To “Yes” and watch your fields magically appear.
On the View Screen we also see that a link to our Onboarding Documents folder has been updated!
Now we can also click the link to the documents in the List View to verify our folder and file was created!
Congratulations on creating your first PowerApps form and automated Flow!