Wednesday, July 18, 2018

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.



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






Charts & Visualizations







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





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.



Edit form screen

Screen Formulas

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


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