Get latest entry with certain criteria from list of items

[zdziarskih]

Good afternoon. I have the following expression working but it returns the latest recording. I want to increment the item by 1 when found. My Material Numbers are text with numeric values (ie: M123 - 1, M123 - 2, M123 - 20, etc) so I am not sure how to increment the number inside of the text. I am assuming I need to extract the value and increment then concat it again somehow. Fairly new to Appsheets but years of experiencing programming.

Any help is greatly appreciated.

Current Expression: (This works and retrurns the latest value which is M123 - 29)

INDEX(SORT(SELECT(FilteredDataByUserLocation[Material Number], CONTAINS([Material Number], "-"), TRUE), TRUE), 1)

[Marc_Dillon]

SPLIT() on the " - " and take the 2nd INDEX(), then NUMBER() then +1.

[zdziarskih]

Thank you for the response Marc. This is what I modified the expression to, but it still returns the M123 - 29 record which is the last of the M123 records in the column. Is the expression proper? 

Expression: INDEX(
                         SORT(
                             SELECT(
                                  FilteredDataByUserLocation[Material Number],
                                  CONTAINS([Material Number], " - ")
                               ),
                          TRUE
                       ),
                       NUMBER(
                           INDEX(
                                SPLIT(FilteredDataByUserLocation[Material Number], " - "),
                                2
                           )
                      ) +1
                 )

[Marc_Dillon]

I would have imagined all the stuff I added would wrap around the existing bit that you had, I'm not really sure what you're trying to do with that expression.

[zdziarskih]

Thanks Marc. I misunderstood. I now have this, but it returns 1. Closer? 

SPLIT(NUMBER(INDEX(SORT(SELECT(FilteredDataByUserLocation[Material Number], CONTAINS([Material Number], "-"), TRUE), TRUE), 2)) +1, " - ")

[Marc_Dillon]

It would have been NUMBER(INDEX(SPLIT(INDEX(SORT(SELECT(....)))))+1

Inner parenthesis functions happen before more outer ones.

[zdziarskih · Accepted solution]

I figured it out. For future reference to help someone if needed, here is what I ended up doing:

Create Virtual Column to hold a value (Number), Use this script to filter, select, sort to get the latest value from the list: INDEX(SORT(SELECT(FilteredDataByUserLocation[Material Number], CONTAINS([Material Number], "-"), TRUE), TRUE), 1). In my form view, I add this script to the input that I am looking to increment to the next value based off a selection of a button: IF([My Column Yes/No] = "YES", '',CONCATENATE(INDEX(FilteredDataByUserLocation[Column], 1), " - ", NUMBER(RIGHT([Increment], (LEN([Increment]) - FIND("-", [Increment])))) +1)).

This gave me the next part number to use. I use this in the Auto Compute as Initial value. Hope that helps someone