'starts with' function used for searching everything past first space

[h_dunnehowrie]

I have a filter on a gallery for the value from a text input. Currently this is set to filter on TextInput1 with a 'starts with' but this is user's full name, so first name (space) last name. I want to be able to use 'starts with' for everything in the text input from after the first space. My instinct was to use index but the text input may have no value and I need the filter to work on just the selected date as well as filtering by last name. Any ideas on how to combine 'starts with' and splitting out the value to everything after the first space?

If(
IsBlank(TextInput1),
Filter(
'Archives Item Request Data',
'Request Visit' = DatePicker_visitdate_1.SelectedDate
),
Filter(
'Archives Item Request Data',
'Request Visit' = DatePicker_visitdate_1.SelectedDate && StartsWith(
'Customer Full Name',
TextInput1.Text
)
)
)

[sgtsnacks64]

Hi @h_dunnehowrie, what data source are you using?

I might have an idea on how to achieve this, but might be dependent on the data source as could induce delegation warnings.

[TheRobRush]

Filter(
       AddColumns(
                  //YourSPList
                   WorkOrders,
                   "lastN",  
                   //change SubmittedBy to your column with name
                   Last(Split(SubmittedBy, " ")).Value), 
       RequestDate = DatePicker1.SelectedDate && 
       StartsWith(lastN, TextInput3.Text)
)

[v-qiaqi-msft]

HI @h_dunnehowrie,

I want to explain to you that the StartsWith() function only tests the start string of a string, it does not support to test any part of a string.

If you want to search full name by any part of the string, I think you should refer to the operator "in".

If(
IsBlank(TextInput1),
Filter(
'Archives Item Request Data',
'Request Visit' = DatePicker_visitdate_1.SelectedDate
),
Filter(
'Archives Item Request Data',
'Request Visit' = DatePicker_visitdate_1.SelectedDate && 
TextInput1.Text in 'Customer Full Name'
)
)
)

[WarrenBelz]

Hi @h_dunnehowrie ,

Firstly the post from @TheRobRush is correct in terms of one possibility of what you are asking. The main reason I will offer the below is Delegation and also allowing for a blank search field

With(
   {
      _Name:
      Last(
         Split,
            TextInput1.Text,
            " "
         )
      ).Value
   },
   Filter(
      'Archives Item Request Data',
      'Request Visit' = DatePicker_visitdate_1.SelectedDate && 
      (   
         Len(TextInput1.Text) = 0 ||
         StartsWith(
            'Customer Full Name',
            _Name
         )
      )
   )
)

also if you want to allow for "everything after the first space" meaning the possibility of double word surnames

With(
   {
      _Name:
      Right(
         TextInput1.Text,
         Len(TextInput1.Text) - 
         Find(
            " ",
            TextInput1.Text
         )
      )
   },
   Filter(
      'Archives Item Request Data',
      'Request Visit' = DatePicker_visitdate_1.SelectedDate && 
      (   
         Len(TextInput1.Text) = 0 ||
         StartsWith(
            'Customer Full Name',
            _Name
         )
      )
   )
)

[h_dunnehowrie]

SharePoint List 🙂

[h_dunnehowrie]

@TheRobRush thanks for the suggestion but this doesn't work and also we need the 'starts with' so that results appear as someone types as we may only know the start of a surname.

[h_dunnehowrie]

@v-qiaqi-msft  thanks anyway. Unfortunately in won't work for us as we need results to appear as someone types and also for the search box to narrow down results as opposed to hide them until there is an exact match.

[h_dunnehowrie]

@WarrenBelz  thanks for the suggestion. Unfortunately neither are working. The first one has no results and the second one is only performing 'starts with' from the start of the input box, so essentially performing the same as my original formula

[h_dunnehowrie]

Hi everyone! I've just worked out how I can achieve this. I'm going to create an additional column in my SP list that calculate the surname from the full name column automatically. I will then get my gallery filter to check this instead of trying to come up with a way to use starts with partly through a string, which seems to be impossible! Thanks everyone for your suggestions. I'll close this question.