Has the Power Apps Combobox Search feature ever been a thorn in your side when you need a specific field from a SharePoint List? Only that every time you attempt to return the distinct values from the field, or do some other transformation, the search feature immediately flips to off and refuses to allow you to turn it back on??
Ever decided to store the values as a Variable or Collection, then get stuck attempting to update on every behavior action with a refresh from the data source? Well, not anymore!
A friend reached out for some help trying to get a Distinct list of all Titles (in all caps) from a SharePoint List. He’s in government (GCC). This means limited resources and even stranger SharePoint requests. But this trick is one I discovered while still working in GCC. So, I knew it would work for him!
With({i:
Distinct(
Sort(SourceName, FieldName, SortOrder.Ascending),
Upper(FieldName)
)},
i
)Why or how does this work? The only explanation I have is based on the way With operates. I believe:
- With caches the result,
- then the search uses the result, instead of performing a calculation each time.
If anyone has any insights or a better explanation, I would love to know. 🤓
Until next time!