cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JC75
Advocate IV
Advocate IV

Find highest number in column with text based on filter

I have a document library - that has a text column (instNumber) that contains a number that is at least  in format of 3 digits e.g 001 but could be more. E.g 1000. 

the reason it’s a text column rather than number - is it could have either an A or C added at end e.g 001C.

 

for each document in library they equally have a “instType” column so you could have inst type = A and instNumber=001 but equally instType = B and InstNumber = 001 

 

i.e the numbering sequence for each instType is sequential for that instType rather than each document just having the next number.

 

a document may be tagged with number 001 and a new  document would be added that corrects it would be created with instNumber 001C (or 001A).

 

I want a label in power apps that looks up based on document type selected in app(and some other lookup criteria) that finds the highest number and adds 1 to indicate what next instnumber can be based on existing documents of that type.

 

using lookup and max functions only works when each document is tagged with InstNumber 001, 002, 003 etc but if my library has 001 002 003 003A 004 004C it would suggest next number is 004 rather than 005.

 

how do I do this so I can essentially strip the text A or C out based on numbering maybe any length I.e 001 or 1000 or 001a or 1000a?

12 REPLIES 12

The syntax works now - but unfortunately it’s still not giving the result I require. The number it shows is incorrect (even with me adding a plus 1) to formula to get next highest.

 

If as sample data in my document library I have documents with following metadata.

 

DocNameinstructionType(choice)Instyear(choice)Unit(choice)instNumber(text)etc
A.docxTOI22A  
B.docxTOI22A001 
C.docxTOI22A001C 
d.docxTOI22A002 
e.docxSI22A001 
f.docxSI22A002 
G.DOCXSI22A003

 

h.docxSI22A 

 

 

On the label in power app(which is a modified SharePoint document form using PowerApps) if I was say editing metadata for A.docx

if I’m filtered on InstructionType=TOI, unit, year unit etc the label needs to show next instruction number which would be 003. If the instruction type was SI the label would show 004.

 

The issue is some docs won’t have a instnumber yet even though created before one that does, some will have a number without text, they will always be 3 digits but could be 4, or 5 I.e 001 or 1000, some will have a C or A at end but means there will be a different document with same number but without the c or a, so can’t just count number of documents etc..

 

I want it to say for that type excluding a letter at end what is highest number plus 1.

RandyHayes
Super User
Super User

@JC75 

So then, the issue is related to documents that have no instNumber.  You only want to account for those that do.

If so, then change the formula to the following:

Value(
   With({_items: Sort(Filter(FBInstructions, InstructionType.Value=DataCardValue2.Selected.Value), Created, Descending)},
       Match(LookUp(_items, !IsBlank(InstNumber)).InstNumber, MultipleDigits).FullMatch
   ) + 1
)

This will exclude the blanks and only return the highest one.  

I also change the formula now to increment that number by one.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
JC75
Advocate IV
Advocate IV

Hi both..

 

Thanks both for your help I managed to get it resolved, as I thought though using sort by created, caused issues where date document created versus when it may have been allocated a number meant it would come ups with the wrong next number sometimes. 

I changed the formula to sort by instruction number and wrapped it in a Text function so that it output the number still as a 3 digit code, and testing so far it seems to handle both numbers with letters and numbers without, and also entries that have no number.

 

This is the code that works, with some of the additional filters I apply.

Text(Value(
   With({_items: Sort(Filter(FBInstructions, InstructionType.Value=DataCardValue2.Selected.Value && InstYear.Value=DataCardValue4.Selected.Value&&Units.Value=DataCardValue5.Selected.Value), InstNumber, Descending)},
       Match(LookUp(_items, !IsBlank(InstNumber)).InstNumber, MultipleDigits).FullMatch
   ) + 1
),"000")

 

Thank you both for your excellent help, really appreciated...

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (3,024)