Running into some strange behavior when using the First or FirstN functions in PowerApps.
I have a data table control in the PowerApp that shows a filtered and sorted data source (SharePoint list). The filtering is based on a variable (we'll call it var_Item_Num) that the user enters. The formula looks like this:
=SortByColumns(Filter(My_Data_Source,Item_Number_Column = var_Item_Num), "Date_Time",Descending)
Works fine -- as soon as the user changes var_Item_Num, the data table updates. If the var_Item_Num is not found within the source, the data table is blank/empty. Great.
Next I wanted to use the same filter, but only return the first/most recent row to the data table. This is where I'm running into issues. Tried with both First/FirstN like so:
=First(SortByColumns(Filter(My_Data_Source,Item_Number_Column = var_Item_Num), "Date_Time",Descending))
This is fine if var_Item_Num is found -- the most recent result is returned. However the behavior changes when var_Item_Num is not found; instead of showing blank, the data table just doesn't update. It shows whatever the previous result was. For example if I search 1111 and view the first result, then search 2222 which is not in the data source, the result for 1111 is still shown.
If I remove First, this behavior is corrected immediately -- a search for 1111 and then 2222 will yield an empty data table as expected.
For kicks I also tried using a collection as a data source instead of the sharepoint list, but had the same result.
Any idea why is this happening?
Hi @FinePilsner ,
Please try below formula instead:
First(SortByColumns(Filter(My_Data_Source,StartsWith(Item_Number_Column , var_Item_Num)), "Date_Time",Descending))
Thanks for the reply @v-monli-msft -- tried this formula, exact same results unfortunately!
The function works correctly when I use a valid item number -- data table shows the first result for that item number.
When the item number is not found, this function does not update and the data table continues to show whatever the previous result was. Data table only updates once a valid item number is entered.
To be clear I am finding this issue to be associated with the First/FirstN functions (last/LastN as well actually). If I use the exact same filter without First, everything works correctly -- if an invalid item # is entered, the data table becomes empty, as expected.
Any other ideas?
Fill out a quick form to claim your user group badge now!
Find out where you can attend!
Features releasing from October 2019 through March 2020
The largest Power BI, Power Platform, and Data conference in New Zealand