Substrings

Here is what I tried

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


3 Likes

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.

image

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! :slight_smile:

1 Like