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

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
Resolver II
Resolver II

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)

SharePoint Get Items Advanced OptionsSharePoint 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
Resolver II
Resolver II

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)

SharePoint Get Items Advanced OptionsSharePoint 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

Highlighted
Regular Visitor

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.

Highlighted
Regular Visitor

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!

 

Highlighted
Resolver II
Resolver II

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (5,295)