# Substrings

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.

1 Like

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.

I fixed it the first two characters, but the trailing digits at the end are incorrect.

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.

I fixed the parentheses and got this

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)``````

It still says LEFT function is used incorrectly

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)``````

I will try reading through it and fixing it myself

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

1 Like

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

So try subbing NUMBER() for EXTRACTNUMBERS().

1 Like

IT WORKED!

1 Like

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

It should because year(today()) returns the year programatically.