Using Virtual Columns I need to extract the phone number and EXTRACTPHONENUMBERS doesnโt even detect the number โ EXTRACTPHONENUMBERS really has garbage in my experience, so Iโm left with my next-best option: using a character index with MID and SPLIT.
Now, Iโm wondering why Iโm getting an error that seems to be wrong. Using
concatenate( [ParseSLExtra]&" ", FIND([ParseSLExtraName], [ParseSLExtra]), " "&(11+1+12) )
I get a result of:
002001ABROZ,MAHYA 408-111-1111 16557176 7 24
which is correct: last two numbers (7 & 24) will be used with MID, because each record has varying bits stuck to the front. In other words, the only thing I can count on is the name; I canโt even be certain a phone number will be attached, but I must test for it each time.
For clarity,
[ParseSLExtra] = "002001ABROZ,MAHYA 408-111-1111 16557176"
&
..[ParseSLExtraName] = "ABROZ,MAHYA"
.
Hereโs where I get the error, by implementing the MID function โ I, I, I mean expression:
MID( [ParseSLExtra], FIND([ParseSLName], [ParseSLExtra]), (11+1+12) )
Did I donk something up? I wouldnโt be asking if I hadnโt already tried a myriad of things but the concatenate test shows Iโm getting the correct character index (startIndex) for the string: 7 is not less than zero. The Expression Assistant verifies the MID expression is valid, too.
Solved! Go to Solution.
Moving the expression from the VC to the Action solved it (using RIGHT). In fact, keeping the expression in both VC and Action allows me to see the value before using the Action. The two areas of redundant code means I have to remember to update both places if I ever make changes to one in the futureโฆopens the avenue to errors, but still, it works.
What still doesnโt work: expression just in VC, and Action is [Phone] = [ParseSLPhone]
FYI: [Name] = [ParseSLName], etc works for every other column, including VC expression that employ MID; they seem to work flawlessly. Thanks for the input gents.
Workaround ONLY, NOT a Solution:
I achieved an acceptable result by using a different cut of the same data, and replacing the MID expression with RIGHT:
INDEX(SPLIT(
RIGHT([ParseFullRecordLine],
LEN([ParseFullRecordLine])-(FIND([ParseSLExtraName], [ParseFullRecordLine])+LEN([ParseSLExtraName])))
), 1)
So Iโm still convinced thereโs an issue with MID and/or Virtual Columns derived from Virtual Columns thatโre derived from a column of data (Iโm using 2 - 16 expressions performed on each of up to 16 pieces of data on one record, all within Virtual Columns)
I was wrong.
Using RIGHT and a different cut of data (a more macro sample of the record) ultimately ended up giving me, โNaNโ, which if Iโm not mistaken, is a geek term meaning a number was not able to be coerced from the expression.
Funny thing is, I could see it produced the desired result in the Slice as a Virtual Column, but when applied during an Action from the Virtual Column to the real column, the phone number changed from the expected result to NaN.
Any thoughts?
I think its the 24 part? I dont know if MID will take a second value that would put it outside of the total string length?
Iโve read MID carefully: if you give it a number higher than the end of the string, itโll return everything starting from the first number given. Problems โmayโ arise if either number isnโt already cast as a number, but both FIND and the grouped addition produce a number-type. I had considered wrapping FIND within NUMBER, but itโs return type is already type-cast as a number.
[ParseSLExtra] will have varying things stuck to the front of it, and I never know exactly what jumble of things will be there. Itโs literally 4 pieces of info, but the only 1 I can guarantee will be there is the Name, and length of Name will vary too, but max at 11; sometimes thereโll be extra spaces in between the extra stuff like,
"DIR CARO,MARK"
Which is missing the Phone, but must be tested, not assumed.
"NOPRD003001GUTIERREZ,L
408-411-1111 16567429" NewLine is part of same record, and has all 4 pieces of info stuck together, before the phone #.
"NOPRD002 ABROZ,MAHYA
408-111-1111 16557176" Here is missing one of the 3-digit numbers, but a complete record.
Thank you @Austin_Lambeth, I appreciate your effort.
FIND() returns 0 if the substring isnโt found. The โless than zeroโ reference is an internal error referring to the internal value derived from the result of FIND().
Basically, your expression needs to handle the possibility the substring isnโt present.
The substring will always be present. Everything is derived from the same record, so Iโve isolated the Name before attempting this in a Virtual Column. Thereโs no way itโs not present, unless itโs an issue of timing. Earlier (yesterday), this was attempted in a Virtual Column thatโs higher up on the list (closer to column #1, under the Columns tab), but it crossed my mind that the Virtual Column order may affect processing order. Would you know, Steve, does a Virtual Column whoโs value isnโt yet initialized because itโs dependent upon another Virtual Column finalizing itโs own value, wait for the Virtual Column(s) itโs dependent on to produce their result(s) before trucking ahead? Or is there a possibility that it doesnโt care whether the other Columns itโs waiting on have produced a valid result, and maybe thatโs why Iโm getting โNaNโ: this Virtual Column is finishing before the others that it needs to wait on?
Dependencies are handled properly: dependent expressions will be evaluated only after their requirements are computed.
Your result is probably not a number.
The VC above shows it results in the Phone number, โ408-111-1111โโฆ Then the Action is [Phone] = [ParseSLPhone]
So, the result is a number until the Action is applied, in which case I was originally getting a total crash of the AppSheet, with the error โstartIndex can not be less than zeroโ upon Saving the AppSheet. I KNOW my startIndex was a 7 on the original record. But the VCโs Expression Assistant would pass both before and after the Saving (and the halting error).
If youโre [ParseSLExtra] is always gonna look like that then you could also try INDEX(SPLIT([ParseSLExtra], " "),2).
You say youโve tried lots of things, would making the Find() and the (11+1+12) into VCs and trying to use those in the formula?
I had not considered thatโฆI felt the expression was simple enough, but you may be rightโฆIโve had to store other variables in simplistic Virtual Columns near the beginning of this projectโฆ
This also helps you isolate where a problem is occurring, if I ever have compound formulas this is my first attempted solution because I am likely incorrect on my assumed outputs of some sub-formula.
Yeah, I still think something more than meets the eye is happening here because I did see my desired result in the Virtual Column and Slice (using RIGHT), up until hitting the Action that applies it to the true column: the result of applying that action does something wrongโฆ or maybe I donโt see exactly what I did wrong yet.
What does the sheet say the value of the NaN cell is?
The literal value on the Sheetโs [Phone] column is blank. Only one column holds the entire record. From that Cellโs data, Iโve developed a parsing Action that picks out the relevant data (using Virtual Columns), and plugs them into the appropriate columns via an Action. Nothing is applied until the action is ran, but the Virtual Columns begin showing results in the Detail screen of the current record because theyโve been included in the Slice so I can see my values quicker than using the Test function in the Expression Assistant.
Maybe I should take the expression from the Virtual Column and instead plug that into the Action. The Action is currently โSet these columnsโ, [Phone] = [ParseSLPhone].
The Virtual Column is currently:
Hereโs a screenshot of my emulator, with the values stored in the VCs (Virtual Columns).
Moving the expression from the VC to the Action solved it (using RIGHT). In fact, keeping the expression in both VC and Action allows me to see the value before using the Action. The two areas of redundant code means I have to remember to update both places if I ever make changes to one in the futureโฆopens the avenue to errors, but still, it works.
What still doesnโt work: expression just in VC, and Action is [Phone] = [ParseSLPhone]
FYI: [Name] = [ParseSLName], etc works for every other column, including VC expression that employ MID; they seem to work flawlessly. Thanks for the input gents.
User | Count |
---|---|
38 | |
35 | |
27 | |
23 | |
18 |