Sure there is also a RIGHT() and a MID(), as well as functions for “casting” to any data type.

That is a very complex expression. What does agid[id number] look like?

An example of an ID number would be 20-R1000 where 20 is the last two digits of the year

Ok, so this is a column expression that looks at itself. The part after the “R”, is it always 4 digits? Is it just sequential increments as long as the year is the same? Are these last 4 digits supposed to reset when the year changes? That’s the part I’m having a hard time with.

So the part after the R increments with user input. The year part is constant and stays the same until next year. Yes, the last 4 digits reset when the year changes. For now I will assume it’s at LEAST 4 digits but it can be more.

This is what comes up for initial value. It’s odd because I don’t know where the e) characters come from.

Try:

```
IF(COUNT(SELECT(AGID[ID Number], RIGHT(TODAY(), 2)=LEFT([ID Number], 2)))>0,
RIGHT(TODAY(), 2) & “-R” & MAX(NUMBER(SUBSTITUTE(SELECT(AGID[ID Number],
RIGHT(TODAY(), 2)=LEFT([ID Number], 2)), (RIGHT(TODAY(), 2) & “-R”), “”)))+1,
RIGHT(TODAY(), 2) & “-R” & 1)
```

*IF(COUNT(SELECT(AGID[ID Number], RIGHT(TODAY(), 2)=LEFT([ID Number], 2)))>0*

Find all entries that start with this year and count them. If there are 0, then it is a new entry for a new year.

*RIGHT(TODAY(), 2) & “-R” & MAX(NUMBER(SUBSTITUTE(SELECT(AGID[ID Number],
RIGHT(TODAY(), 2)=LEFT([ID Number], 2)), (RIGHT(TODAY(), 2) & “-R”), “”)))+1*

If other entries already exist this year, take the last two digits of today’s year, add “-R”, and then the hard part. Select all that entries that start with this year. Remove the prefixes, and then find the max number and add 1.

*RIGHT(TODAY(), 2) & “-R” & 1)*

If the count in the first step is 0, it must be a new entry and therefore can start at 1.

This implementation doesn’t care about the length of the digits after the R, which may be helpful if you are unsure of how many entries per year might be added. Hopefully I got all the parenthesis right…

Thank you. I will fix the parentheses and let you know if it works.

This should have correct parenthesis.

```
IF(COUNT(SELECT(AGID[ID Number], RIGHT(TODAY(), 2)=LEFT([ID Number])))>0,
RIGHT(TODAY(), 2) & "-R" & MAX(NUMBER(SUBSTITUTE(SELECT(AGID[ID Number]),
RIGHT(TODAY(), 2)=LEFT([ID Number])), (RIGHT(TODAY(), 2) & "-R"), ""))+1,
RIGHT(TODAY(), 2) & "-R" & 1)
```

OH! Forgot the stupid second piece of it!

IF(COUNT(SELECT(AGID[ID Number], RIGHT(TODAY(), 2)=**LEFT([ID Number], 2)**))>0,

RIGHT(TODAY(), 2) & “-R” & MAX(NUMBER(SUBSTITUTE(SELECT(AGID[ID Number]),

RIGHT(TODAY(), 2)=**LEFT([ID Number], 2)**), (RIGHT(TODAY(), 2) & “-R”), “”))+1,

RIGHT(TODAY(), 2) & “-R” & 1)

Did it on the right, gotta have the same params on the left.

Typos, typos. Freehanding code always makes life more difficult for myself.

```
IF(COUNT(SELECT(AGID[ID Number], RIGHT(TODAY(), 2)=LEFT([ID Number], 2)))>0,
RIGHT(TODAY(), 2) & “-R” & MAX(NUMBER(SUBSTITUTE(SELECT(AGID[ID Number],
RIGHT(TODAY(), 2)=LEFT([ID Number], 2)), (RIGHT(TODAY(), 2) & “-R”), “”)))+1,
RIGHT(TODAY(), 2) & “-R” & 1)
```

@tvinci, you’re going to have trouble supporting this app if you don’t understand these expressions.

Turns out I was wrong on being able to use NUMBER() on a List of values.

So try subbing NUMBER() for EXTRACTNUMBERS().

Woo! And *hopefully*, without any intervention, next year will automatically start at 21-R1.