Let's say you use Microsoft Dynamics 365 in your organization extensively, and now you want to build some apps in Power Apps so your sales team can start interacting with your CRM data quickly and efficiently while on the road. If you were planning to use the Dynamics 365 connector to connect to your CRM, you would want to look at the formulas below to complete and deploy your app.
In this blog post, we will assume that you are building a Power Apps app based on the Opportunity entity in Dynamics 365. The use case is that you want a sales person to be able to view "today's" appointments and view only their own opportunities ("My Opportunities").
Setting Variables When App Starts
On the main form of the app, there will be an additional Event called OnStart that will not be present on other forms you add to your App. This Event will run the formulas when the App opens in the designer, but not afterwards. So if there are Formulas you wish to re-execute, such as Refresh(datasource), then you would want to add a refresh button on the form with the same formulas. It also will give the user the ability to refresh the data to its most current state. Below are some formulas that you will want to set in any app you build:
Set(CurrentUserEmail, Text(User().Email)); // set current user's email in the CurrentUserEmail variable
Set(CurrentUserID, LookUp(Users, domainname=CurrentUserEmail,systemuserid)); // set current user in the CurrentUserID variable
Filtering Opportunities for Current User
Let's say you want the logged in user to see only their own opportunities (My Opportunities) in the app. In your gallery control, you can set the Items property to a Filter function to filter the rows.
Filter(Opportunities,_ownerid_value=CurrentUserID)
Filtering Appointments for Current User With Actual Start Date of Today
The following formula can set your gallery to show the current user's appointments for the day:
Filter(Appointments,_ownerid_value=CurrentUserID,scheduledstart>=Today(),scheduledstart
Selecting Opportunity, Setting Variables, Navigating to Opportunity Detail Form
The Filter formula above sets the current user's opportunities to a gallery control. When a user selects an opportunity, you would want the next screen to open that exact opportunity with all the details. For this to happen, you can set some variables that will make subsequent formulas easier to read/write. For example:
Set(CurrentOpportunityID,OpportunitiesGallery.Selected.opportunityid); // Grab the selected opportunity's Id from the gallery control and store it in the CurrentOpportunityID variable.
Set(CurrentOpportunityContact,OpportunitiesGallery.Selected._parentcontactid_value); // this formula takes the opportunity's related contact and stores it in the CurrentOpportunityContact variable
Navigate(OpportunitiesDetails,ScreenTransition.Cover) // navigate to the next screen.
Tip: You would want to set these formulas in the "Next" button of your gallery control.
Displaying a Count of Associated Records for Opportunity
Sometimes it's useful to display counts of associated records for navigation purposes. For example, if your field staff needs to know how many activities they need to take care of, you can use the following formula and display it in a label on top of the "Details" screen.
Text(CountIf(Activities,_regardingobjectid_value=CurrentOpportunityID))
Similarly, you can have a count of other related records as well. The formula below shows the number of notes an opportunity has.
Text(CountIf(Notes,_objectid_value=CurrentOpportunityID))
Complex Filtering Based on DropDown and Search Text Box Values
Let's say there is a need to filter the records in a gallery control based on values from a dropdown control. (Filter opportunities by sales stage, for example).
In your gallery control, you can set the Items property to a complex Filter function to filter the rows. This example allows the rows to be filtered by a dropdown and a search textbox. The dropdown filters for All Stages, 1-StageA, 2-StageB. The SearchOpportunities text box further filters the results by searching within the contact's fullname.
Switch(
Dropdown1.Selected.Value,
"All Stages", Filter(Opportunities, statecode=0,_ownerid_value=CurrentUserID,If(Find(Lower(Trim(SearchOpportunities.Text)),Lower(LookUp(Customers,contactid=_customerid_value,fullname)))>0,true)),
"1-StageA", Filter(Opportunities, statecode=0,_ownerid_value=CurrentUserID,stepname="1-StageA", If(Find(Lower(Trim(SearchOpportunities.Text)), Lower(LookUp(Customers,contactid=_customerid_value,fullname)))>0,true)),
"2-StageB", Filter( Opportunities, statecode=0, _ownerid_value=CurrentUserID, stepname="2-StageB", If(Find(Lower(Trim(SearchOpportunities.Text)), Lower(LookUp(Customers,contactid=_customerid_value,fullname)))>0,true))
)
Conclusion
All the formulas mentioned above can get you a simple, lightweight, and effective Power Apps app that will let your sales team interact with ONLY Opportunities and Activities – which is a very common requirement for many projects! They will be able to look at just their own opportunities and activities without getting distracted by menu items, screens, tiles that they don't need!
Try these formulas for your apps, and let us know how it goes!
Want to learn even more about Microsoft PowerApps? Join PowerObjects, this year's Platinum Sponsor, at the Microsoft Business Applications Summit on July 22-24 in Seattle. Register with code HCL100dc to receive a $100 discount.
Be sure to subscribe to our blog for more Dynamics 365 related posts!
Happy Dynamics 365'ing!
