cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JohanJohansson
Level: Powered On

Auto generate reference number in SharePoint list

Hi,

After some searching I haven't quite been able to find the solution to my flow needs on other threads so hopefully this thread is useful to other new flow users.


As a new user of MS Flow I aim to create a simple customer number generating flow. The aim is to have two lists, one will be a lookup list which will hold values like 'customer number' (Let's call it 'list A' for now) and the second will be the main list (List B) where customer data is entered. When users enter an item to 'List B' they aren't asked to enter a value for the customer reference field as this need to calculate automatically.

The flow wants to: See the 'customer number' filed is blank on newly added items > grab existing 'customer number' value from List B > increment the value by 1 > copy that value into the same field in list A > save the incremented value back to list B and so on.

The customer number in list A will therefor get updated on each flow run and make sure that all new items receive a unique customer reference number.

So far the flow looks like this:

1) Trigger: When a new item is created (in SharePoint)

2) Initialise Variable: Name= CaseRefNo Type= Integer

3) Get Items: Site Address and List Name set to location of my lookup list (List A)

4) Condition: If CustomerReference field is EQUAL to NULL

5) Set Variable: Name=CustRefNo Value= CustomerNumber from the lookup list

And here is where the issues start. If I try to set this variable the flow designer won't let me select Customer Number from the lookup list. 

Can anyone advise how I can create a variable that can utilise data from a specific column in a separate SharePoint list on the same site?

If not please let me know of your recommendations for creating automatic reference numbers for list items :). Ideally I was going to generate the unique reference number and then use a concatenate function to add a prefix to the reference for the type of job (e.g Service Request = SR001, SR002 and Incident - IR001, IR002 ect, ect) however this may have to wait for the basics to work first!

Thanks in advance for your advice.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Piper
Level 8

Re: Auto generate reference number in SharePoint list

@JohanJohansson I think you can do a SharePoint Get Items action against your lookup list. Go to the Advanced Options and specify the following:

  1. Order By your Customer Number column (descending)
  2. Top Count 1, this ensures you only get the max value 
  3. Limit Columns by View (if necessary)

SharePointGetItems.pngSharePoint Get Items Advanced Options

Once you do that you can create an Apply to Each action (it will only execute once because you limited the results to 1) and inside of your loop you can set your new customer number variable. Then I suppose you will want to add it to the list you just fetched from and assign it to the triggering item.

 

I hope that helps.

Piper

 

View solution in original post

4 REPLIES 4
Highlighted
Piper
Level 8

Re: Auto generate reference number in SharePoint list

@JohanJohansson I think you can do a SharePoint Get Items action against your lookup list. Go to the Advanced Options and specify the following:

  1. Order By your Customer Number column (descending)
  2. Top Count 1, this ensures you only get the max value 
  3. Limit Columns by View (if necessary)

SharePointGetItems.pngSharePoint Get Items Advanced Options

Once you do that you can create an Apply to Each action (it will only execute once because you limited the results to 1) and inside of your loop you can set your new customer number variable. Then I suppose you will want to add it to the list you just fetched from and assign it to the triggering item.

 

I hope that helps.

Piper

 

View solution in original post

JohanJohansson
Level: Powered On

Re: Auto generate reference number in SharePoint list

Hi @Piper,

 

Thanks for your response that's really helpful.

 

Do you know if a similar approach would work for setting other variables if I were to add them to the same lookup list?

 

If the list were to expand to include other items (possibly mixed variable types) then it would be great to be able to have flow look at the list and set a variable using column 3 or 4 for example:

 

Title                      |  Name of VAR 1   | Name of VAR 2 | Name of VAR 3  

       Value1                   Value2                     Value3              Value4

 

Based on your advice I was thinking I could create views which would only display one of each Colum. Then I could apply your solution to get the value from say  'Name of VAR 3'  which would then be set as a variable and used in the original list containing the rest of the job/ customer info.

JohanJohansson
Level: Powered On

Re: Auto generate reference number in SharePoint list

Hi @Piper,

 

Thanks for your response that's really helpful.

 

Do you know if a similar approach would work for setting other variables if I were to add them to the same lookup list?

 

If the list were to expand to include other items (possibly mixed variable types) then it would be great to be able to have flow look at the list and set a variable using column 3 or 4 for example:

 

Title                      |  Name of VAR 1   | Name of VAR 2 | Name of VAR 3  

       Value1                   Value2                     Value3              Value4

 

Based on your advice I was thinking I could create views which would only display one of each Colum. Then I could apply your solution to get the value from say  'Name of VAR 3'  which would then be set as a variable and used in the original list containing the rest of the job/ customer info.

 

End result would therefor let me create multiple variables from the one lookup list.

 

Thanks again for your time!

 

Piper
Level 8

Re: Auto generate reference number in SharePoint list

Hi @JohanJohansson,

It sounds fine conceptually but I would want to know more of the use cases for those columns and how they relate, if at all, to the customer number column to see if the approach might present challenges.

 

If my recommendation solved your issue please mark the post as solved.  We can continue to discuss these other nuances as well within the post.

Thanks,

Piper

 

 

Helpful resources

Announcements
firstImage

Power Platform Online Conference

Speakers, submit your sessions now! Call for speakers ends Feb. 10!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (4,536)