Filter on Sharepoint List Not Working with ID

[mchapman]

I'm connected to a sharepoint list that has only 470 records.  It's a list of projects where users can enter and update projects.  Pic 1 is an example of the front-end.  Each box at the top along with the selection box along the left side is for filtering the project list.  Everything works fine until I tried to add in the internal ID (Project ID) of the sharepoint record from the 'List-Projects' Table. Further below is the filter code along with a pic (Pic 2) of the chunk of code, underlined in blue, of the piece causing the issue.  It only returns a portion of the records as is.  I tried using just an equals sign instead of starts with but then it doesn't work with all of the other "Startswith" filters and only returns something when you type in an ID in the "Project ID" search bocx.  What might I try here?  is there a function that works like a startswith for numeric columns?

Pic 1:

Here is the filter I'm using as the data for the gallery

Sort(
If(
IsEmpty(LBxGroup.SelectedItems),
Filter(
'List-Projects',
DdYear.SelectedText.Value = Year
,StartsWith(
Text(ID),
TxInID.Text
),
StartsWith(
Title,
TxInProjectName.Text
),
StartsWith(
'Business Lead'.DisplayName,
TxInBusinessLead.Text
),
StartsWith(
'MIS Lead'.DisplayName,
TxInMISLead.Text
)
),
Filter(
'List-Projects',
DdYear.SelectedText.Value = Year,
Group.Value = First(vSelectedItems).Value,
StartsWith(
Text(ID),
TxInID.Text
),
StartsWith(
Title,
TxInProjectName.Text
),
StartsWith(
'Business Lead'.DisplayName,
TxInBusinessLead.Text
),
StartsWith(
'MIS Lead'.DisplayName,
TxInMISLead.Text
)
)
),
'Status:Sort'.Value
)

pic 2 (formatted code):

[RandyHayes · Accepted solution]

@mchapman 

You will have delegation issues with that Formula!

Please consider changing your Formula to the following:

With({_items:
    Filter(
            'List-Projects',
            DdYear.SelectedText.Value = Year &&  // SelectedText should NOT be used.
            (IsBlank(LBxGroup.SelectedItem.Value) || Group.Value = LBxGroup.SelectedItem.Value) &&
            StartsWith(Title, TxInProjectName.Text) &&
            StartsWith('Business Lead'.DisplayName, TxInBusinessLead.Text) &&
            StartsWith('MIS Lead'.DisplayName, TxInMISLead.Text)
    )
    },
    
    Sort(
        Filter(_items,
            StartsWith(Text(ID), TxInID.Text)
        ),
        'Status:Sort'.Value
    )
) 

This Formula will isolate the results of the filter into a scope variable _items.  Then it will use that to perform the non-delegable part of the process.  So, basically we are pre-filtering based on delegable criteria and holding that table to then perform, what would be, non-delegable actions on the table we have just retrieved.

Also, I made an assumption that your LBxGroup combobox has some sort of variable collection thing going on that you were putting into vSelectedItems....you need not do this.  The above formula will determine if the combobox has selected items and if so, then it will use the selected item in the criteria.

Also...you are using SelectedText in your formula - you should consider changing that now as the SelectedText property has been deprecated. 

I hope this is helpful for you.

[mchapman]

Thanks that worked great and I learned a lot!  Couple tweaks, see below:

With (
{
_items: Filter(
'List-Projects',
DdYear.Selected.Result = Year && (IsBlank(First(LBxGroup.SelectedItemsText).Value) || Group.Value = First(LBxGroup.SelectedItemsText).Value) && StartsWith(
Title,
TxInProjectName.Text
) && StartsWith(
'Business Lead'.DisplayName,
TxInBusinessLead.Text
) && StartsWith(
'MIS Lead'.DisplayName,
TxInMISLead.Text
)
)
},
Sort(
Filter(
_items,
StartsWith(
Text(ID),
TxInID.Text
)
),
'Status:Sort'.Value
)
)