In the last several months, I've been learning quite a bit about PowerApp's syntax. From what I've read, I've been able to figure out one way of filtering out the latest date (per category), but it seems very inefficient. I'm sort of looking for a better way to do this.
I'll provide a hypothetical example and solution (which I'm sure is not the best way):
I have a SPO list of people's status. Consider an SPO list with these records:
I want to create a collection that categorizes the list by PersonName and filtering only the latest dates:
My intuition is to do something like this:
Filter( AddColumns(ExampleList, "MainPersonName", PersonName), DateOfStatus= First(Filter(SortByColumns(ExampleList, "DateOfStatus", Descending), PersonName=MainPersonName)).DateOfStatus )
In this example, I'm creating a temporary column called "MainPersonName" to act as the category and then my filter statement is essentially creating a subquery of a filtered-version of the same datasource. Is there a way to make this shorter or more efficient? I've read about disambiguation, but it's a bit confusing. Is there an opportunity to disambiguate here to make the syntax more efficient?
Solved! Go to Solution.
Possibly not the answer you are looking for but...
When data structures/processing become complex or you need to work with large amounts of data, I recommend using Azure SQL DB instead of SharePoint. You can then perform a lot more of the data filtering/processing in the Database (as well as more validation rules such as compound keys and much better delegation support) and just use PowerApps for what it does best - allowing the display of, and interaction with, the data.
The price of a single database, basic tier Azure SQL DB is $5 a month. Although it is a single database, for development you can always use different schemas to separate out the tables for different 'databases'.
With the example you have given, you would just create a View that serves up the data in the desired format and add that as a datasource to your PowerApp. Quicker/easier to develop, more peformant and more flexible.
Do you want to filter your SP list items to filter out the latest date per category?
I think the GroupBy function could achieve your needs. I have made a test on my side, please take a try with the following workaround:
Set the OnSelect property of the "Collect" button to following formula:
On your side, you should type the following formula:
More details about the GroupBy function, please check the following article:
Agreed. If I had an Azure SQL DB license, that would make my life 100x easier. I prefer to use SQL queries and views; unfortunately, the only resources I have at my disposal are SharePoint lists, Excel, and/or plain-text files.