In my previous post I discussed how to create a new PowerApp directly from a SharePoint list. I also indicated it had a few limitations that needed to be addressed. One of these limitations was the default browse screen is not compatible with lookup type fields (People\Groups, Choice, Lookup, etc). These field types are of course available in the details and edit screens, but not the browse screen.
I can force the control to look at the field I want, but it doesn’t know how to convert the object into text.
Being limited to just the text fields of the list or at least fields PowerApps knows how to treat as text (Date\Time fields) was creating a bad user experience in the forms I wanted to create. I had to come up with another way. Using a calculated field in the SharePoint list won’t work because a calculated field can’t see those columns either. It’s actually
Using SharePoint Lookup Type Fields in PowerApp Browse Screens
Because I want to be able to see these values on my browse screen they need to be converted to text. The two fields I want on my browse screen that are not compatible with the view are:
- Training Requested For – The name of the person taking the training (Person or Group field)
- Training Requested – Title of the training course (Lookup Field)
Luckily there are formulas that can help us out here. To display the name of the user PowerApps has the ability to call the DisplayName property of the field.
To get the title of the training requested is just another property of the field.
I decided to get a bit fancier and add some text at the beginning of the value to add to the experience. I wanted the field to read: Course Name: <“Name of the Course”>. To do this, you simply combine two strings together (the can be variable based or static (what you typed)) inside a Concatenate function.
Concatenate("Course Name: ", ThisItem.TrainingRequested.Value)
The final values I wanted on the form were the start date, End Date and total estimated cost. Dates were straight forward as they are available from the drop down of the data properties. The cost I had to get a bit fancy here too. I wanted the cost to display as Total Estimated Cost: $XXXX.XX. The problem now is that the Concatenate function requires strings and the TrainingCost field comes back as a numeric. Enter in a another formula. Text(<string>, formula) will convert a string to a formula. The final formula looked like this:
Concatenate("Total Estimated Cost: ", Text(ThisItem.TrainingCost, "[$-en-US]$#,###.##00")) //The #,###.##00 format indicates to put a comma at every thousand, ten thousand etc, //and the 00 at the end indicate to add trailing zeros if none exist.
When all was said and done I ended up with my form looking like this”
PowerApps is still pretty new, but there is a lot you can do with it. I look forward to seeing where it will get to in the near future.
Thanks for Reading!
Nice work David. Thought I would throw out the idea instead of the Concatenate function which can be scary I will often make my labels like so.
“Full Name: ” & Datasource.FirstName & ” ” & Datasource.LastName
Same result, less scary. That is the joy of PowerApps, almost always more than one way to do things. 🙂
Thanks Shane. Great suggestion!!