These flows create or maintain the individual columns in a ‘StartsWith() Index’ pattern.

Section 1 – Build the initial index in a separate list based on an existing list of records:

The StartsWith() Index Pattern article describes this method in detail. This post is how to create the infrastructure and maintain this method using Power Automate.

This section takes an existing list with records and builds the StartsWith() Index to a separate list. If you want to create this index based on a new list with no records, you can skip this section and go to Section 2: Maintain the Index below. You could also forgo a separate list and parse your column back to the same list, assuming you have enough SharePoint index columns available (20 is the maximum).

The example below is to parse out a projectDescription column into StartsWith() Index columns, so a Power Apps user can ‘keyword’ search a project name from a project list that has over 2000 records.

• Manually trigger a flow
  • We will typically run this just once, or a handful of times
• Get items list A
  • List A is less than 5000 records, so we don’t need to worry about pagination, but I did set Top Count to 5000, not shown in the screenshot
  • The column we are parsing in List A is projectDescription
• Compose
  • This is an optional step to clean up data to help with StartsWith() searching
  • As soon as we add the Compose with dynamic content, it will create the For Each because body/value is an array
  • The replace() code, listed below the screenshot, is to replace parenthesis with nothing
    • Note: This particular list of projects sometimes has words in parenthesis. A user would not necessarily know to add a leading parenthesis ‘(‘ to a search, so this helps StartsWith() searching by removing the open parenthesis and then, so the results don’t look funny, will also remove the closing parenthesis

outputs('Get_items_list_A')?['body/value']

replace(replace(items('For_each')?['projectDescription'],')',''),'(','')

• ‘Get items list B the Index’ checks to make sure this project hasn’t been added to List B already
  • This is a step you might not need but I added it so I could run the flow again if necessary
  • Filter Query
    • ‘Title’ in List A has the project number which is a unique number and the primary key creating the relationship between lists

• Condition
  • I prefer expressions for logical evaluations vs. text, dynamic content, or other means
  • Both sides of the Condition have an expression, empty() and true
    • empty() is very handy for checking for no results from SharePoint. A check for null would return false because there is data, it is just a table with no records, something like value:[]
      • Sidenote: The Power App functions, IsEmpty() and IsBlank() are useful in the same way. It is extremely important for a Power App developer to know the difference between the two!
    • true is another expression, you must use the expression builder and add it; you cannot simply type in true

empty(body('Get_items_list_B_the_Index')['value'])

• Create item
  • This is the main step where we parse the column we want to index, space delimited word-by-word, into individual columns containing just that word
  • ProjectNumberIndexSLT: It’s a good idea to add some kind of primary key to tie the lists together. Here we are writing the unique project number to our index list.
    • This allows us to use the index for just searching, and then point the Power App user to the main project list via project number LookUp()
  • ProjectNameIndexSLT: Adding the project name to the index list is optional, but in my scenario nice to have so we can show search results without querying the main project list
  • P1SLT-P9SLT:
    • The split() function creates a table, then using JavaScript style index notation, [0], [1], etc., we determine the table row to parse accordingly
    • Index notation will generate an error if blank, so after the first word, index zero, I added an if(greater(length())) formula to return a blank value if that index doesn’t exist
    • Length() works because it can evaluate a table as well as a string. Here we are evaluating rows in the split() table
      • Note: A coalesce() function might be a more elegant solution here, I’ll give that a try the next time I make one of these
    • If(Greater()) is used to do the math and if true, returns the split()[X] row value, or if false returns blank

split(outputs('Compose'),' ')[0]
if(greater(length(split(outputs('Compose'),' ')),1), split(outputs('Compose'),' ')[1], '')
if(greater(length(split(outputs('Compose'),' ')),2), split(outputs('Compose'),' ')[2], '')
if(greater(length(split(outputs('Compose'),' ')),3), split(outputs('Compose'),' ')[3], '')
if(greater(length(split(outputs('Compose'),' ')),4), split(outputs('Compose'),' ')[4], '')
if(greater(length(split(outputs('Compose'),' ')),5), split(outputs('Compose'),' ')[5], '')
if(greater(length(split(outputs('Compose'),' ')),6), split(outputs('Compose'),' ')[6], '')
if(greater(length(split(outputs('Compose'),' ')),7), split(outputs('Compose'),' ')[7], '')
if(greater(length(split(outputs('Compose'),' ')),8), split(outputs('Compose'),' ')[8], '')

Section 2 – Maintain the index

• This flow is extremely similar to Section 1. All formulas are identical, however I did add a new formula to the Update Item action for Id. This is to prevent an unnecessary For Each
  • We must use a ‘Get Items’ action, vs ‘Get Item’. Get Item doesn’t have a Filter section and we need that to find our index record
  • Since we know Get Items will only return one record, using JavaScript style index notation again, [0], we can tell Power Automate to go to the first record in the Get index items action

body('Get_index_items')['value'][0]['ID']

These are a few ways to create and maintain a StartsWith() Index, however, I can think of other methods: Considering the example above, if users were entering new projects via a Power App, the app could easily parse out the project name into individual columns, no flow needed. My production projects list is coming from SAP, so again, the SAP admins could parse this for us so when that system writes to SharePoint, it would also populate the StartWith() Index columns.

The StartsWith() Index pattern is an easy method to provide Power App users with a rich search experience for any SharePoint list.