Filter() Not Giving Wanted Result As I Follow Formula Bar IntelliSense

[StevePenner]

I have illustrated in the image below my progression through the following formula:

ShowColumns(Filter('Meeting Agendas', Editable = true), "Meeting_x0020_Date").Meeting_x0020_Date

 Note that this formula was built using the progression through IntelliSense system following trial-and-error

1. I click the cursor inside 'Meeting Agendas'. This is a SharePoint list and I am only showing relevant columns, the column 'Editable' which is a 'yes/no' data type in SharePoint, but which translates 'Yes' => true and 'No' => false. Many of the items (rows) are not set at all and this is a desired use of the SP list. The two columns of interest are 'Editable' (internal SP name 'Editable') and 'Meeting Date' (internal SP name 'Meeting_x0020_Date'). I also show the 'ID' column from the list with its always-unique system-generated value.

2. I then click the cursor within the Filter() function name: This actually returns a one-item (or row) table, which is the wrong row: the Filter is where Editable field/column value should be true and not false. This should NOT be the item (row) with ID value = 1, but with ID value = 10!  It's clear that the formula bar analyzer is only returning the one row. This is the row bordered blue, when I expect it to return the row bordered in orange.

The rest of the formula does not seem to be in question, but I progress through the result in case there is any comment to be made on the whole of the formula:

3. I click the cursor inside the ShowColumns() function name, and it does return the column 'Meeting Date' (or SP internal name 'Meeting_x0020_Date' and then adding a dot notation 'Meeting_x0020_Date' to get the actual value.  The final result is put into the listbox for the screen shown in the image

That is not what I want: it should say "10/27/2020" and not "10/20/2020". What am I not understanding about the Filter() function which I had to construct with considerable trial-and-error?

ThanksBuilding the Formula To Get Wanted Date in ListBox

[WarrenBelz · Accepted solution]

Hi @StevePenner ,

Firstly, IntelliSense is a guidance tool on possible syntax and can allow you to at times to get outcomes other than intended. Understanding correct syntax is essential. Firstly, the syntax for your code supplied would be something like

ShowColumns(
   Filter(
      'Meeting Agendas', 
      Editable = true
   ), 
   "Meeting_x0020_Date"
)

however an additional issue is that Boolean (yes/no - true/false) fields are not Delegable and you can get unexpected results (I have found even on smaller lists). Providing you list item numbers will not get over your Delegation limit (500-2,000 dependent on your settings), you can do this. NOTE you will not get a Delegation warning if more items, but it will only work on the smaller data set.

With(
   {wAgendas:'Meeting Agendas'},
   ShowColumns(
      Filter(
         wAgendas', 
         Editable = true
      ), 
      "Meeting_x0020_Date"
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.