Optimise Setting Default Values for Dynamics 365 Lookup Controls in a Canvas PowerApp

Colin Maitland, 23 August 2019

In a previous article, I demonstrated how to set the default values for Dynamics 365 Lookup fields in a Canvas PowerApp. The example used was a Leave Requests app that requires the selection of Requester and Manager.

The following image shows the formula used for setting the default Requester to the current User. This formula is associated with the Default property on the Data Card for the Requester.

The following image shows the formula used for setting the default Manager to the current User’s Manager. This formula is associated with the Default property on the Data Card for the Manager.

Both of these formulas retrieve the current User from an underlying CDS Users data source. The currently used formulas cause the current User to be retrieved twice every-time the Leave Request form is opened for adding a new Leave Request. The following optimisations can be used instead.

The following four formulas can be added to the OnStart event of the app. This event runs only when the app is started, and so the formulas associated with this event only run once per app session.

The first formula creates and populates a collection named CollectionCurrentUser to a table containing a single record, i.e. the record for the current User, retrieved from the underlying CDS User’s data source.

The second formula sets a global variable named CurrentUser to the record for the current User retrieved from the CollectionCurrentUser table.

The Default property on the Cover Person Data Card in the app can now be changed to reference the CurrentUser record.

The third formula creates and populates a collection named CollectionCurrentUserManager to a table containing a single record, i.e. the record for the current User’s Manager. This formula uses “Choices('Users’.'Users (user_parent-user)')”, instead of “First(CollectionCurrentUser).Manager” or “CurrentUser.Manager”, to obtain the current User’s Manager record because those syntaxes do not provide the User’s Manager record. The “User” and “.User” clauses in this formula reference the Dynamics 365 systemuserid GUID values of the Users. “'Users (user_parent_user)'” is a User lookup field from the Users data source. Using Choices('Leave Requests’.'mag_ManagerId') would provide the same result.

The fourth formula sets a global variable named CurrentUserManager to the record for the current User’s Manager retrieved from the CollectionCurrentUserManager table.

The Default property on the Manager Data Card in the app can now be changed to reference the CurrentUserManager record.

I have initially deliberately separated the formulas associated with the app’s OnStart event to demonstrate the use of Collections to store a local copy of a table and Variables to store a local copy of a record; however, the first and second formulas and the third and fourth formulas can be combined as shown here:

Finally, the following formula, which is a modification of the original formula used for setting the Default property for Manager, cannot be used in the OnStart event of the app because it does not set the CurrentUserManager to the current User’s Manager record.

In conclusion, the optimisations demonstrated in this article have reduced the number of times the underlying data source needs to be used to retrieve the current User and current User’s Manager for use by a Canvas PowerApp.