Speaking Session at SPTech Con Boston 2018

Using PowerApps to customize SharePoint List forms

Microsoft PowerApps is Office 365's offerings for building custom business mobile apps & SharePoint List form. This session will demonstrate the power & vision of PowerApps for building custom business applications on SharePoint platform. We will cover role-based SharePoint List Form, Tabs, Graphs and MS Flow to interact with external services like Google Calendar & Wunderlist. Role based security implementation will be done using 2 different approaches, SharePoint List & Azure Security Groups. To be respectful of the time I will be doing as much Hands-On as I can but eventually display a working model and run though the code. Please attend this session to understand the power behind PowerApps and what you can really achieve with it. 

Slides:

1. PowerApps to customize SharePoint List forms
2. PowerApps To Customize List Forms - Expense Form Code

Power Apps to build Apps and customize SharePoint List forms

I am super excited to share our latest Tech Talk Tuesday webinar on PowerApps. Below is the link. We had more than 100 people sign up so our following is growing! There were so many questions I thought I would share with everyone since others may have the same or similar questions. Have a great rest of the month. We will do another Tech Talk at the beginning of August.

Brief Description:

How to customize forms in a browser using PowerApps very much the same way you did with InfoPath. You do not have to download another app; PowerApps is directly integrated with SharePoint. See how you are able to create richly customized forms without needing to write any code.

YouTube link for the webinar: Power Apps to build Apps and customize SharePoint List forms

Approval Mechanics

Roles

Below are the roles and the action that will be taken.

Role	Action	Status changes to
End user	Enter Expense	Saved
End User	Submit for Review	Submitted
PowerApp Business Logic	Evaluate the Correct Approver based on Amount	Submitted
Approver	Approve\ Reject	Approve\ Reject

Different Statuses

Lists involved

Screens & Features

Enter Expense Screen

Edit Expense Screen

Add Expense Screen

Charts & Visualizations

Misc Settings

Making the Form Landscape

Verifying the form settings for the list

SharePoint Integration Settings

Use this setting to integrate several screens with New, Edit & View functionality.

*This is supposed to be done after all the screen are created.

• ·         Once you open list in power apps for customization you will find an item with name SharePoint Integration

• ·         On SharePointIntegration option select OnEdit, OnView and apply the formulae mentioned below.

New Form screen

Pictorial tutorials

Configuring the screen to show forms in 3 columns

Change Columns that won’t be editable to be read only

Status column

Change description to Edit multiline text

Uncheck Columns use does not need to see in the screen.

Go to the DataSource “Expense Submissions” and select them on the below order.

• ·         Add a new button, name it btnSave. Select Save button and on “OnSelect” specify formula, this will save the record to list.
• ·         Select FormNew and select OnSuccess property from the formula bar and apply below formula

Button Settings to look like Tabs

Create two buttons as Expense Submission, Help, follow the below to give them round edges.

On Advanced Properties

Setting the Color of focused & unfocused buttons

Screen Formulas

Below are the formulae for this screen.

Control type	Control name	Property	Formula	What’s happening here?
	SharePointIntegration	OnNew	NewForm(FormNew);Navigate(NewFormScreen,Fade)	When user click on new item in the list it will open new item screen
		OnEdit	Navigate(EditFormScreen,Fade)	When user click on item on list, it will navigate them to Edit screen
		OnView	Navigate(EditFormScreen,Fade)	When user click on item on list, it will navigate them to Edit screen to show the details
		OnCancel	ResetForm(FormNew);Clear(ExpenseDetailRows);RequestHide()	Reset the form and clear the expensedetailsrows collection, hide the form
Form	FormNew	OnSuccess	If(IsBlank(SharePointIntegration.Selected) || IsEmpty(SharePointIntegration.Selected),Set(SharePointItemID,Last('Expense Submissions').ID),Set(SharePointItemID,SharePointIntegration.SelectedListItemID)); Navigate(FormScreenEdit,ScreenTransition.Fade)	When data get saved to list, then set the SharePointItemID variable value to the Last submitted item
		Item	If(IsBlank(SharePointIntegration.Selected) || IsEmpty(SharePointIntegration.Selected),Last('Expense Submissions'),SharePointIntegration.Selected)	Set the SharePointItemID variable value to Last submitted item
Button	btnExpenseSubmission	OnSelect	Set(showHelp,false)	Set the showHelp variable to false
		Fill	If(showHelp,RGBA(0,120,215,0.6),RGBA(0,120,215,1))	This is to change color to darker blue when user is on the tab and light blue when user is not on it.
		RadiusTopLeft	15	Set the border radius value to 15 to look like curved shape
		FontWeight	Bold	Set the font weight to bold
Button	btnHelp	RadiusTopRight	15	Set the border radius value to 15 to look like curved shape
		OnSelect	Set(showHelp,true)	Set the showHelp variable to true
		Visible	showHelp
		Fill	If(showHelp, RGBA(0,120,215,1), RGBA(0,120,215,0.6))	If showHelp is true then apply background color to reduced transparency
Html Text	HtmlText1	Visible	showHelp	Show the HtmlText1 control when the showHelp property is true
		Text	"<b><font color=blue>Help is coming!</font></b>"
Form	FormNew	Visible	!showHelp	Hide the control when showHelp is true
Button	btnSave	Visible	!showHelp	Hide the control when showHelp is true
		onSelect	SubmitForm(FormNew)	Save the data to list.

Final Screen

Edit form screen

Screen Formulas

Control type	Control name	Property	Formula	What’s happening here?
Button	btnSubmitExpense	OnSelect	ClearCollect(Approvers,ForAll('Expense Configurations',If(Value(Min_x0020_Amount)<Value(valTotalAmount.Text) && Value(Max_x0020_Amount)>Value(valTotalAmount.Text),Approver))); Patch('Expense Submissions',{ID:SharePointItemID},{Status:"Submitted",Approver:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser", Claims:Concatenate("i:0#.f|membership|",First(Approvers).Email), DisplayName:First(Approvers).DisplayName, Email:First(Approvers).Email, Department:First(Approvers).Department, JobTitle:First(Approvers).JobTitle, Picture:First(Approvers).Picture }}); RequestHide();	1.       Get the Approver for the amount. 2.       Save the data to “Expense Submission list” 3.       Close the form
		Visible	If(DataCard7.Default="Saved",true,false)	Show Submit button only when the status is “Saved”
		OnSelect	Navigate(AddExpenses,Fade) (We will add AddExpenses screen next step)	Navigates to AddExpense Screen
		Visible	If(DataCard7.Default="Saved",true,false)	btnAddExpenses button only visible when status of the expense is Saved
Edit Icon	editIcon	OnSelect	EditForm(FormNew); Navigate(FormScreenNew,Fade)	1.       Change the mode of the FormNew to “EditForm” so that users can edit it. 2.Navigate to “FormNew” now.
		Visible	If(DataCard7.Default="Saved",true,false)	Toggle Edit icon depending on the Saved Status.
Label	valTotalAmount	Text	Sum(Filter('Expense Details', (If(IsBlank(SharePointIntegration.Selected) || IsEmpty(SharePointIntegration.Selected),Last('Expense Submissions').ID,SharePointIntegration.SelectedListItemID))=Expense.Id),Amount)	Show the sum of all the Added sub category Expenses.
Button	btnApprove	OnSelect	Patch('Expense Submissions',First(Filter('Expense Submissions', ID=SharePointItemID)),{Status:"Approved"});RequestHide()	OnSelect of btnApprove, it will make expense status in “Expense Submission” list to Approved
		Visible	If(SharePointIntegration.Selected.Status="Submitted" && User().Email=SharePointIntegration.Selected.Approver.Email,true,false)	Show approve button only if status is Submitted & user’s email is in the approver list.
		OnSelect	Patch('Expense Submissions',First(Filter('Expense Submissions', ID=SharePointItemID)),{Status:"Rejected"});RequestHide()	OnSelect of btnReject, it will make expense status in “Expense Submission” list to Rejected
		Visible	If(SharePointIntegration.Selected.Status="Submitted" && User().Email=SharePointIntegration.Selected.Approver.Email,true,false)	Show reject button only if status is Submitted & user’s email is in the approver list.
Label	lblTotalAmount	Text	Total expenses:	It will hold the text for total amount
Screen	EditFormSreen	Visible	If(IsBlank(SharePointIntegration.Selected) || IsEmpty(SharePointIntegration.Selected),Set(SharePointItemID,Last('Expense Submissions').ID),Set(SharePointItemID,SharePointIntegration.SelectedListItemID))	If a List item is NOT already selected, then load the Last Saved List Item ID else get the List Item ID of the item select. Save the ID from the above condition in “SharePointItemID” variable.
Form	FormEdit	Item	If(IsBlank(SharePointIntegration.Selected) || IsEmpty(SharePointIntegration.Selected),Last('Expense Submissions'),SharePointIntegration.Selected)	Set the datasource of the form to either List Item user has selected or the Last saved.
		Title	View text	Convert Title field to read-only
		Approver		Convert approver field to read-only
		From	View text	Convert From field to read-only
		To	View text	Convert To field to read-only
		Status	View text	Convert Status field to read-only
		Description	View text	Convert Description field to read-only

Final Screen

Add expenses screen

Screen Formulas

Control type	Control name	Property	Formula	What’s happening here?
Icon	backIcon	OnSelect	Navigate(FormEdit,Fade)	On select of this icon, user will navigate to FormEdit screen.
Label	lblTitle	Text	Title	It will hold the expense title label
Textbox	txtTitle			This is a text box where user will enter the data for title
Label	lblDate	Text	Date	It will hold the expense date label
Date	txtDate			This is a text box where user will enter the data for date
Label	lblAmount	Text	Amount	It will hold the expense amount label
Textbox	txtAmount			This is a text box where user will enter the data for amount
Label	lblCategory	Text	Category	It will hold the expense category label
Dropdown	drpCategory	Datasource	Category list	This is a dropdown where user will select the option for category
		Items	Sort(Categories,Title)	This will sort the Categories datasource with Title property in ascending order
		Value	Title	Select value property to Title to show all the titles of category list.
Label	lblSubcategory	Text	Subcategory	It will hold the expense subcategory label
Dropdown	drpSubcategory	Datasource	Subcategory list	This is a dropdown where user will select the option for category
		Items	Sort(Filter(Subcategories, Category.Id=drpCategories.Selected.ID),Title)	This will sort the subcategory items with Title property and filter the subcategories based on category selected.
Button	btnAdd	Text	Add	This property will set the text property of button to Add
		OnSelect	If(     IsBlank(txtTitle.Text) Or IsBlank(txtAmount.Text) Or IsBlank(dtDate.SelectedDate),     Set(showErrorMessage,true),     Set(showErrorMessage,false);         Collect(         ExpenseDetailRows,         {Title:txtTitle.Text,Date:dtDate.SelectedDate,AmountFormatted:Concatenate("$ ",txtAmount.Text), Amount:txtAmount.Text,Category:drpCategories.Selected.Value,Sub_x0020_Category:drpSubcategory.Selected.Value,CatId:drpCategories.Selected.ID,SubcatId:drpSubcategory.Selected.ID}         )         )	OnSelecting btnAdd button, powerapp will check whether all fields were filled or not. If not it will throw the error. If yes data will be added local collection with the name ExpenseDetailRows
Button	btnClear	Text	Clear	This property will set the text property of button to Clear
		OnSelect	Clear(ExpenseDetailRows)	OnSelect of btnClear, it will clear ExpenseDetailRows collection
Button	btnSubmit	Text	Add to Expenses	This property will set the text property of button to “Add to expenses”
		OnSelect	If(IsBlank(txtTitle) Or IsBlank(txtAmount) Or IsBlank(dtDate),showErrorMessage=true,showErrorMessage=false; ForAll(ExpenseDetailRows, Patch(         'Expense Details', Defaults('Expense Details'), {     Title:ExpenseDetailRows[@Title], Amount:Value(ExpenseDetailRows[@Amount]), Expense:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",Id:SharePointItemID,Value:""}, Category:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",Id:ExpenseDetailRows[@CatId],Value:""},Sub_x0020_category :{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",Id:ExpenseDetailRows[@SubcatId],Value:""} ,Date:ExpenseDetailRows[@Date] } ) ); Clear(ExpenseDetailRows);Navigate(FormScreenEdit,Fade))	OnSelect of btnSubmit, it will add the data to the “Expense Details” list, by looping through the “ExpenseDetailRows” collection
Data table	DataTable2	Datasource	ExpenseDetailRows	Set the data source for a data table to ExpenseDetailsRows which is the local collection
		Columns to select	Title Amount Date Category Subcategory	Select these columns to appear on the screen
Screen	Add expenses screen	OnVisible	Set(show ErrorMessage,false)	Set the showErrorMessage value to false
Label	lblErrorMessage l	Text	All fields are required
		Visible	showErrorMessage

Final Screen

Graph screen

Final Screen

Control type	Control name	Property	Formula	What’s happening here?
Button	btnExpenseSubmission2	OnSelect	Navigate(EditFormScreen, Fade)	Onselect of this button user will navigate to EditFormScreen
		Fill	RGBA(0,120,215,0.6)
Chart	ColumnChart_1	Items	(Filter('Expense Details',Expense.Id=SharePointItemID))	This will filter the expense details based on selected expense
		Labels Series1	Title Amount	This will set the x-axis values to Titles, Y-axis values to Amount
		Grid Style		This will display both axis with values

·         Your screen should like below

Questions Users asked

1.       How to navigate between different screens in the same app?

Use Navigate function to navigate between different screens with some transition effect.

2.       I though you can only create apps in powerapp?

Intially only mobile applciatins could be created but sinc2 sometime in early 2018 you can customize List forms also.

3.       Can you create a role based form in PowerApp?
Yes, demo does that. You can use Office 365 groups also.

4.       Can you have multiple Approvers instead of just one?

Yes, I will just have to change the code to support it, currelty I imepelmtend only single approver.

5.       How to filter the items in a data table based on id?

Use Filter function on the Item property of data table and specify the column value for filtering and specify filter condition.

6.       How to insert multiple items into from one list context to another list ?

Use ForAll function to iterate through the collections. Use Patch function inside ForAll to insert the data into another list.

7.       How to get the last submitted value in any list?

Use Last function to get the last submitted item in any list.

8.       How to delete/dissociate the customized app from SharePoint list?

Go to list à list settingsàForm settings à select use the default sharepoint form. Then click on Delete custom form. This will delete the power app customized form

9.       Is the visual a "PowerBI" object, or is it built in to PowerApps?

This is a Column Chart control of PowerApps.

10.   Is it possible to call a webservice or rest api here?

Yes we can call REST api end points. This is possible using custom connectors. First create custom connector, then use in PowerApps and this process is a little complex. Follow the links below to get enlighten

https://docs.microsoft.com/en-us/connectors/custom-connectors/define-blank

https://docs.microsoft.com/en-us/connectors/custom-connectors/use-custom-connector-powerapps

11.   How can we add custom validation like from date cannot be greater than to date.?

Please see below. You can write a condition. 

12.   Is it possible to save the data to multiple lists using single form?

Yes it is possible. In my demo I am saving data from one list context to another list. Similarly we can do for multiple lists as well with Patch function.

13.   Is the sample code available for download?

Since it is list customized form, right now Microsoft provides no option for downloading or transferring the code from one environment to the other.

14.   Could you put formula so that the from date is not before the "to" date? Below is the formulae that has been done to implement the logic. 

a.       When error we can show error message or we can highlight the control as below