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?
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.
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?
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
No comments:
Post a Comment