POWEROBJECTS

718 Washington Ave. N. Suite #101
Minneapolis, MN 55401
View map and all Locations

Send us a message

Let’s say you use Microsoft Dynamics 365 in your organization extensively, and now you want to build some apps in PowerApps 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.

powerapps

In this blog post, we will assume that you are building a PowerApps 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<DateAdd(Today(),1))

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 PowerApps 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!

Avatar for Joe D365

Joe D365

Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.