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

Filter to pull specific column data from last entry on SharePoint list

So I am trying to create my own userID system using the first 2 letters of the first name and last name and combine it with an increasing number that comes from a SharePoint column called "Record" that is set as a number column. I set the previous entry manually, but I'mhaving trouble getting powerapps to pull that from a large list of over 2,000 data row entries. Because it's so large, I assume I need the Filter function somewhere, and Last function to pull that number. I was planning on calling the previous Record number and having powerapps add 1 to that number, then adding the Right(Record,3) to the userID so that it would be something like NaGr123. 

 

Additionally, is there a way to keep if from counting all 0 values when I hit a number like 1,000? I'd rather not have something like NaGr000, if possible. Although that may display as NaGr, correct? Can it skip the 1,000 and go straight to 1,001 for every thousand?

 

For specifics:

 

List name: Visitor Import Test

Number column name: Record

1 ACCEPTED SOLUTION

Accepted Solutions
mdevaney
Level 8

Re: Filter to pull specific column data from last entry on SharePoint list

@ngreen

 

You know, I may have just had this formula backwards.  I just tested this on a 3,000 name Sharepoint List and came up with the correct result

 

First(Sort(UserList,ID,Descending)).ID+1

 

If this doesn't work, please let me know what error you are getting, or what the result is as well as the expected result.

View solution in original post

10 REPLIES 10
mdevaney
Level 8

Re: Filter to pull specific column data from last entry on SharePoint list

@ngreen 

 

Sharepoint assigns an ID to every data row starting at the number 1, then 2, then 3... and so on, and so forth.  To determine the next ID number you would use the formula: Last(yourDatasourceName,ID,Ascending).ID+1

 

However, once your Sharepoint list grows to 2000+ items this formula breaks because the Last function cannot be delegated.  Therefore, we must Sort the list first, and then obtain the last record.  The Sort function can be delegated.  Here's the formula you should use:  Last(Sort(yourDatasourceName,ID,Ascending)).ID+1

 

As for your 2nd question, I would not recommend doing this because you might create a duplicate User ID (e.g. MiDa123 for 123 and MiDa123 for 1123).  It is my understanding that a User ID must be unique.

 

---

Please click "Accept as Solution" if my response helped to solve your issue so that others may find it more quickly.  If your thought the post was helpful please give it a "Thumbs Up."

ngreen
Level: Powered On

Re: Filter to pull specific column data from last entry on SharePoint list

@mdevaney ,

 

Thanks for your help with this! The second formula works 90% of the way, but it seems like Sort is not a delegable function. I'll try and get this going with a delegable function and hopefully I can get it.

cwebb365
Level 10

Re: Filter to pull specific column data from last entry on SharePoint list

Hmm sort should be delegable unless your using a complex column type?
ngreen
Level: Powered On

Re: Filter to pull specific column data from last entry on SharePoint list

@cwebb365,

 

It's just sorting the standard SharePoint ID column, so I'm not sure. 

cwebb365
Level 10

Re: Filter to pull specific column data from last entry on SharePoint list

Yeah I think that pesky SharePoint ID falls under complex type which really needs to get supported cause it’s used for so much stuff lol....

Only way around it to my knowledge is create a flow that copies that ID into another number column on your list that you can then reference from your app with delegation.
ngreen
Level: Powered On

Re: Filter to pull specific column data from last entry on SharePoint list

@cwebb365,

 

I have one of those on another list. I'll give it a shot.

mdevaney
Level 8

Re: Filter to pull specific column data from last entry on SharePoint list

@ngreen

 

You know, I may have just had this formula backwards.  I just tested this on a 3,000 name Sharepoint List and came up with the correct result

 

First(Sort(UserList,ID,Descending)).ID+1

 

If this doesn't work, please let me know what error you are getting, or what the result is as well as the expected result.

View solution in original post

cwebb365
Level 10

Re: Filter to pull specific column data from last entry on SharePoint list

Nice!
ngreen
Level: Powered On

Re: Filter to pull specific column data from last entry on SharePoint list

@mdevaney 

 

It seems so! That definitely worked! Thanks so much. I really appreciate your help, especially since it's the weekend. I'll mark it as the solution. Thanks again for your help!