Re: Substrings

tvinci · 05-12-2020 10:35 AM

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

Bahbus

Same concept, different names.
NUMBER(LEFT([YourColumn], 1))

tvinci

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?

tvinci

Here is what I tried

Bahbus

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

tvinci

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

Bahbus

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.

tvinci

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.

tvinci

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

tvinci

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

Bahbus

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…

tvinci

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

tvinci

I fixed the parentheses and got this

Bahbus

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

Bahbus

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)

tvinci

It still says LEFT function is used incorrectly

Bahbus

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.

tvinci

Bahbus

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

I will try reading through it and fixing it myself

Bahbus

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

So try subbing NUMBER() for EXTRACTNUMBERS().

tvinci

IT WORKED!

Bahbus

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

tvinci

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

Steve

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