Bugged "startIndex less than zero" error using MID expression

[hodgson_doug]

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.

[hodgson_doug]

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)

[hodgson_doug]

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?

[Austin]

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?

[hodgson_doug]

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.

Austin_Lambeth:

If you’re [ParseSLExtra] is always gonna look like that then you could also try INDEX(SPLIT([ParseSLExtra], " "),2).

[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.

[Steve]

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.

[hodgson_doug]

Steve:

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?

[Steve]

hodgson.doug:

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?

Dependencies are handled properly: dependent expressions will be evaluated only after their requirements are computed.

hodgson.doug:

I’m getting ‘NaN’

Your result is probably not a number.

[hodgson_doug]

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

[Austin]

If you’re [ParseSLExtra] is always gonna look like that then you could also try INDEX(SPLIT([ParseSLExtra], " "),2).

[Austin]

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?

[hodgson_doug]

Austin_Lambeth:

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…

[Austin]

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.

[hodgson_doug]

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.

[Austin]

What does the sheet say the value of the NaN cell is?

[hodgson_doug]

Austin_Lambeth:

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:

hodgson.doug:

    INDEX(SPLIT(
    RIGHT([ParseFullRecordLine], 
          LEN([ParseFullRecordLine])-(FIND([ParseSLExtraName], [ParseFullRecordLine])+LEN([ParseSLExtraName])))
    ), 1)

[hodgson_doug]

Here’s a screenshot of my emulator, with the values stored in the VCs (Virtual Columns).

[hodgson_doug · Accepted 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.