cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mchapman
Regular Visitor

Filter on Sharepoint List Not Working with ID

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:

mchapman_1-1614175071591.png

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):

mchapman_0-1614174952680.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@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.

 

 

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

2 REPLIES 2
RandyHayes
Super User
Super User

@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.

 

 

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

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

mchapman_0-1614186097569.png

 

 

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
)
)

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,517)