One of the main problems I have with PowerApps at present is that it doesn't nicely support Dynamics 365 lookup fields out of the box. Today I will show how I've worked around its limitations to make Dynamics 365 lookup fields easily viewable and editable in PowerApps.
For today's demonstration, I've created two custom entities in a Dynamics 365 online environment:
- Pet - This entity is used to track companion animals.
- Pet Type - This entity stores the different types of pets (bird, cat, dog, etc.) that are tracked in the system. There is a 1:N referential relationship between Pet Types and Pets.
This view shows a list of pets with the corresponding type:
Building the PowerApp
If you use the new PowerApp wizard to build an app based on the Pet entity, you will see the Pet Type lookup field shows up as just the GUID:
To display the name property for the corresponding Pet Type, you can use the PowerApps LookUp function.
The LookUp function finds the first record in a table that satisfies a formula. Use LookUp to find a single record that matches one or more criteria.
In order to use the LookUp function, you need to first add Pet Types as a data source.
Updating the text display
Once the Pet Types data source is added, you can update the text property of the control on the browse screen that's showing the Pet Type GUID with a LookUp formula to show the Pet Type name value instead. My Dynamics 365 solution is using "new" as the publisher prefix, so the function looks like this:
LookUp('Pet types',new_pettypeid = ThisItem._new_pettypeid_value, new_name)
You can see where I've updated it in the PowerApps designer and the name value is now showing instead of the GUID:
The GUID is also showing on the detail screen:
So the text property for the data card value control gets changed to use a LookUp formula, too.
Showing lookup items in a drop-down menu
The edit screen also needs to be updated, but this is trickier because we have to be able to display the name and also sending the GUID to Dynamics 365 when the record is saved. I'm using a relatively short list of Pet Types, so I can display all the possible values in a drop-down menu. If you want something comparable to the lookup functionality in Dynamics 365, I suggest you take a look at this blog post to see how the author created an account lookup dialog.
The edit screen initially looks like this:
To implement the drop-down menu, the following steps are required:
-
Change the Pet Type id data card type to "allowed values."
-
Unlock the data card and select the data card value control. You will need to set both the items and default properties. The items property formula is
SortByColumns(Filter('Pet types',statecode=0),"new_name"). This reads all active Pet Types and sorts them by name. Note that the items property has an additional "value" setting that controls what is displayed to the end user. Set it to the name field. -
Set the default property to
LookUp('Pet types',new_pettypeid = Parent.Default, new_name). This sets the selected item in the drop-down menu to the underlying value of the record you are editing. After you complete this step, the data card value control properties in the PowerApps designer should look like this:
-
Select the data card control and set its update property to
DataCardValue22.Selected.new_pettypeid. This tells PowerApps to supply the id of the selected item from the drop-down menu. When you're done, the data card's properties view should look like this:
The resulting edit screen will now show the possible lookup values in a drop-down menu.
Wrapping up
While this approach certainly works, I am hopeful that it won't be necessary in future versions of PowerApps. What do you think about what I've shown here? Are you planning to use PowerApps to complement your Dynamics 365 deployments? Why or why not? Please let me know in the comments.