cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ChrisMendoza
Frequent Visitor

I went to work every day prepared to die in a tiger cage...

My cage, however, is that my organization invests into systems that support the student life cycle. Systems which support staff are very often de-prioritized, lost in the enterprise queue.

#BetterTogether – Power Apps & Power Automate

Every three years, to start the big push towards physical inventory, the asset management office reaches out to the managers and directors with the main goal of identifying whom has been delegated the responsibility of conducting the physical inventory. The delegated persons will then be sent targeted instruction and information to fulfill the procedure according to policy.

Power Apps

The managers and directors will use Power Apps to interact with connections to SharePoint and Office 365 Users.

Power Automate

A recurring trigger will get the SharePoint items and convert to a CSV file to be uploaded into an AD group using a PowerShell script.

SharePoint List

I chose to use a SharePoint List because unfortunately there is no database view that has the information I need; it is in the queue.

As you can, see it is rather simple.

sharepoint-list-columns.pngSimple SharePoint List

I did get fancy by adding a calculated column which I use to extract the samaccountname for AD.

sharepoint-calculated-column.pngCalculated Column

 

=IF(NOT(ISBLANK(PropertyCoordinatorEmail)),LEFT(PropertyCoordinatorEmail,INT(FIND("@",PropertyCoordinatorEmail)-1)),"null")

 

I created a view that I use later in Power Automate.

sharepoint-view.pngA customized SharePoint View

Power Apps

The welcome screen for the manager or director. In OnStart, I set a variable '_curUser' which allows me to display a filtered gallery.

 

App > OnStart > Set(_curUser,User().Email);

GalleryDept > Items > Sort(Filter(PropertyCoordinatorMaintenance,ManagerEmail = _curUser || DelegatedEmail = _curUser),DeptID,Ascending))

 

better-together-home-screen.gif

Selecting Gallery Items

Most of the managers and directors have very few departments however, some have near a dozen. All departments need to be assigned a Property Coordinator. I decided to allow single selection, by clicking a gallery item or mutli-selecting gallery items via a checkbox. Either way the item(s) are added to a Collection.

powerapps-collect.pngAdding a selected item to a Collection

 

GalleryDept > checkbox_Dept > OnCheck > 
 If(
  IsBlank(
   LookUp(CheckedItems, coll_DeptID = ThisItem.DeptID)
  ),
  Collect(
   CheckedItems,
   {
    coll_DeptID:ThisItem.DeptID,
    coll_Department:ThisItem.DepartmentDescription,
    coll_Coordinator:ThisItem.PropertyCoordinatorEmail
   }
  )
 )

 

ThisItem.PropertyCoordinatorEmail is populated after assignment.

powerapps-collected-checked-items.pngCheckedItems Collection

Assigning a Property Coordinator

Depending on the previously collected items, the manager or director can then select a person who they would like to assign the physical inventory task.

choose-delegate.pngOverlay to assign the Property Coordinator

 Using a simple text input box I can filter Office 365 Users.

 

GalleryEmployee > Items > Filter(Office365Users.SearchUserV2({searchTerm:textInput_Employee.Text,top:50}).value,!IsBlank(Department))

 

The !IsBlank( ) was added to filter the users results even more as I am only interested in staff and faculty; students do not have departments.

search-delegate.pngFilter Office 365 Users

A selected item gets added to the 'SelectedEmployee' Collection.

 

GalleryEmployee > OnSelect > ClearCollect(SelectedEmployee,{coll_emplName:DisplayName,coll_emplEmail:UserPrincipalName})

 

A simple gallery of the 'SelectedEmployee' serves as a visual confirmation prior to saving.

selected-delegate.pngSelected and ready to be saved

The result of a save is visual confirmation on the landing page.

confirmation-reviewed.pngGreen is good

Power Automate

A recurring schedule runs a Power Automate flow to create a CSV file for import into AD. The detail file is also created at the same time only to provide a quick glimpse of the transactions.

powerautomate-overview.png

Step Detail

I add Filter Query, Order By, and Limit Columns by View criteria to the 'Get Items from SharePoint' step.

powerautomate-get-items.pngGet Items from SharePoint

Using the applied step from above, I create CSV tables with selected columns.

powerautomate-create-csv.pngCreate CSV table

Lastly, create SharePoint files for consumption.

powerautomate-create-files.pngCreate SharePoint File

#BetterTogether

How is this #BetterTogether? Three years ago I was using Power Query, Word (mail-merge), Google Forms, and Google Sheets. As you can imagine, a lot of managers and directors asked, "Is this a phishing scam?". Now we have an integrated and dynamic experience for the user.

Where is the Power BI?

This idea was only approved yesterday (04/20/2020) by our accessibility office so I didn't want to create a Power BI report without knowing if my datasource would even be used. As a Super User in the community, it will not take much to get Power BI connected to a SharePoint List and then create a couple of measures for this dataset. There is the other issue, which is I am one of the few that has a Pro license so embedding into the Power App itself does not do much good.

Where is Teams?

The app is actually in the Teams General Channel. There is a small issue in that I cannot add the Teams O365 group to the app which would give share permissions to the members. I have submitted a ticket to have our tech support to take a look. I also have created a couple of Power Automate flows to 'Post Message to Channel' and 'Post Message to User' as the flow bot probably on a SharePoint trigger.

 

I am excited to see how other folks are doing #BetterTogether!

Helpful resources

Announcements
MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Top Kudoed Authors
Users online (8,302)