Hello AppSheet,

I want to omit the first letter of a value in a list so I can convert it into a number and find the max value in the column. So in Java I would just use casting and substrings, but how do I do this in AppSheet?

Thanks.

Tiffany

Hello AppSheet,

I want to omit the first letter of a value in a list so I can convert it into a number and find the max value in the column. So in Java I would just use casting and substrings, but how do I do this in AppSheet?

Thanks.

Tiffany

Same concept, different names.

`NUMBER(LEFT([YourColumn], 1))`

Thank you. Can I use this same concept with a date value? For example getting the last two characters of the year in a date value?

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.

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