Add items to existing Sharepoint list

[TechWriter89]

I have a Sharepoint list of document numbers and would like to extend it to add more items using the same format. So if I have number PIL-23-1582 as the last item in the current list, I would like the list to extend to add 200 new items, starting at PIL-23-1583 and counting up to PIL-23-1783.

 

Currently I plan to add this as a step in a flow that refreshes the middle digits each year. If there's an operation to instead just extend a list when available items drop below 10 etc. in a separate framework, that would be fine too. I'm flexible on exact approach as long as the list doesn't run out of material. 

It seems like Create Items is the right operation, but I'm not sure of the best way to read the current list values and avoid duplicates. I'd appreciate any advice here! 

[creativeopinion]

@TechWriter89 How will you be triggering your flow? Would you like to run a check every day?

[TechWriter89]

The current flow for these numbers runs on January 1st each year (to update the 22 to 23 to 24 for the current year). We only go through a few hundred numbers per year. I was thinking of adding this as a step to that flow so the logic is:

• On January 1, get the list
• Update the current numbers
• Add new ones matching the pattern

Here's what I currently have. 

[creativeopinion · Accepted solution]

@TechWriter89

Trigger

Start with a Manual trigger. You can replace your trigger after you've built and tested your flow.

Get Current Year 

It's always good practice to use a Convert time zone action when working with Dates/Times.

• Base time: Insert a utcNow() expression.
• Source Time Zone: Search for (UTC) Coordinated Universal Time and select it.
• Destination Time Zone: Search for your local time zone and select that.
• Format String: Round-trip (can be any one, Round-trip is just my default)

  Add a Compose action.

  Insert an expression. Use the formatDateTime() function.

  

  Switch back to the Dynamic content tab and insert the outputs from the Convert time zone action. 

  

  Add a comma and single quotes and yy for the last two digits of the current year. 

  

  Your expression should look something like this: 

  formatDateTime(body('Convert_time_zone'),'yy') 

  Please Note: If you've renamed your timezone action, your expression will need to match the name of your Convert time zone action..

  Get List Items

  Next, you need to add a Get Items action to get your SP List. Important: You will need to sort your list by the PIL Number column—this way you can access the last item in the list. 

  In my demo, I'm using the Title column for that. You will need to adjust your ODATA orderBy query based on your column's internal name. If you aren't sure how to get your column's internal name, please refer to this section of a YT tutorial I uploaded.

  

  Add a Compose action to get the last PIL item. You'll need to use an expression.

  

  My expression looks like this, however you will need to replace the Title text between the single qu...

  last(body('Get_items')?['value'])?['Title']

  Run a test.

  Confirm that the Compose action has pulled the last item in your SharePoint list. 

  

  Create the Range of PIL Text

  The next section of your flow will involve a few Compose actions. 

  

  Add a Compose action to store the number of items you want to add to your SP List. In your original post you stated 200. While you are testing your flow, I would recommend a smaller number. 5-10 is good.

  
  

   Updating Media

  Compose the Starting Number

  Add a Compose action to hold your starting number. You will need an expression for this. I'll walk you through how to build out the expression. You need to use the following functions:

  • split() to split your PIL text by the dashes
  • last() to grab the last part of the split string (the digits)
  • int() to convert your string into an integer
  • add() to add 1 to your number (as your starting point for the next set of the numbers)

    Start with the split() function and insert the outputs from Compose action that is storing the last PIL number.

    

    Add a comma and single quotes. In between the single quotes insert a -.

    

    Wrap the entire expression in a last() function. To wrap the expression go the start of it (press the up arrow key on your keyboard) and type in last(. Then go to the end of the expression (press the down arrow key on your keyboard) and insert a closing bracket ).

    The last() function will take the last part of the PIL text (eg. 1583)

    

    Next, you need to convert the string of text into a number (integer). Wrap the entire expression in an int() function. Don't forget to add a closing bracket at the end of the expression.

    

    Lastly, wrap the entire expression in an add() function. You need to add 1 to your number so it starts at 1584. Just as above. Go to the start of the expression and insert add( 

    

    Go to the end of the expression, and insert a comma and a 1, then insert the closing bracket.

    

    The expression should look something like this: 

    add(int(last(split(outputs('Compose_-_Get_Last_PIL_Item'),'-'))),1)

    Please note: This part of the expression outputs('Compose_-_Get_Last_PIL_Item') may look different depending on how you've named your Compose action.

    Compose the Array of Numbers

    To get the range of numbers you'll need an expression and the range() function. The range function takes two parameters:

    range( [starting number], [count of numbers])

    Insert the range() function in the Expression tab. Click on the Dynamic content tab and insert the outputs from the Compose action that is storing your starting number.

    

    Add a comma than insert the outputs from the Compose action that is storing the number of items to add. 

    

    My expression looks like this::

    range(outputs('Compose_-_Starting_Number'),outputs('Compose_-_Items_to_Add'))

    Please note: Your expression may look a bit different based on how you've named your compose actions.

    Compose Count of Items

    This is optional. However, I like to use Compose actions to confirm the count of the numbers created. The range function will create an array of numbers you can loop through. Use a Compose action to store the length of items in the array. You'll need an expression and the length() function. 

    Insert outputs from the Compose action above into the length() function.

    

    Run a test.

    Confirm the output of the Starting number, it should be next in the list. My list ended at 1586, so 1587 is the next number.

    

    Confirm the output of the Compose action with the range numbers.

    

    If you added a Compose action to store the number of items, it should match the number of items to add Compose action.

    

    Apply to Each

    Add an Apply to Each action to loop through each item in the Compose action that is storing the range of numbers.

    

    Add a Compose action to build your number. 

    PIL-[insert outputs from year]-[Current Item]

    

    

    Add a Create Item action. Use the outputs from the Compose action above for the Title (or whichever column you are using for your text).

    Hope this helps!
    
    

    
    

    If I helped you solve your problem—please mark my post as a solution ✅.
    Consider giving me a 👍 if you liked my response!
    
    

    👉 Watch my tutorials on YouTube
    👉 Tips and Tricks on TikTok

[TechWriter89]

Thank you very much! I'm only partway through testing, but it's great up through the Compose action. This is so educational to work through-- what a great level of detail. 

I will come back and accept this as the solution in a few days once I'm done with the rest.

My expressions adapted to some local names in case that helps anyone else in the future: 

In the Get Items steps, a filter query to grab PIL numbers that aren't already reserved by linked ADS items:

startswith(Title, 'PIL') and User_x0020_Story_x0020_Ticket_x0 eq '0'

Retrieving most recent PIL number from renamed steps: 

last(body('Get_PIL_Numbers_list')?['value'])?['Title']