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?
Here is what I tried
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.
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
Sure there is also a RIGHT() and a MID(), as well as functions for โcastingโ to any data type.
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
Turns out I was wrong on being able to use NUMBER() on a List of values.
So try subbing NUMBER() for EXTRACTNUMBERS().
IT WORKED!
Woo! And hopefully, without any intervention, next year will automatically start at 21-R1.
It should because year(today()) returns the year programatically.
@tvinci, youโre going to have trouble supporting this app if you donโt understand these expressions.
User | Count |
---|---|
43 | |
29 | |
23 | |
21 | |
13 |