Start position less than 1 error

We have 2 databases with the same data in them (one was converted to the other.)  Formula 1 works and formula 2 returns the error "Start position less than 1 or not an integer".  Any ideas why the second formula does not work??

1. Varchar(100) null  
If {inventory_master.web_base_sku} < '09000' Then
    (If trim(replace(Mid({inventory_master.description},instr({inventory_master.description},'Pkg'),20),"Pkg"," ")) startswith "/"
        then Tonumber(replace(trim(replace(Mid({inventory_master.description},instr({inventory_master.description},'Pkg'),20),"Pkg"," ")),"/"," "))
        else Tonumber(trim(replace(Mid({inventory_master.description}, instr({inventory_master.description},'Pkg'),20),"Pkg"," "))))
Else 0

2. Varchar (120) not null
If {INVENTOR.ITEM_ID} < '09000' Then
    (If trim(replace(Mid({INVENTOR.ITEM_DESC},instr({INVENTOR.ITEM_DESC},'Pkg'),20),"Pkg"," ")) startswith "/"
        then Tonumber(replace(trim(replace(Mid({INVENTOR.ITEM_DESC},instr({INVENTOR.ITEM_DESC},'Pkg'),20),"Pkg"," ")),"/"," "))
        else Tonumber(trim(replace(Mid({INVENTOR.ITEM_DESC}, instr({INVENTOR.ITEM_DESC},'Pkg'),20),"Pkg"," "))))
Else 0

I am not sure it is the formula as much as the data it is evaluating on.
does every row in the {INVENTOR.ITEM_DESC} field have "Pkg"?

also note that using < on a string may not be getting you want you think or intend...
'1' is > '09000'

No not every record has PKG but remember the first formula works WITH THE SAME DATA and returns the data we are looking for.  What could be different about the database or structure that returns the error on the second formula?

Edited by mabrown - 21 Sep 2017 at 8:07am

Is it exactly the same data set?

I didn't break it down entirely but the "Start position less than 1 or not an integer" is likely referring to once of the mid() functions.
I would split each into a different formula and run them to see which one breaks.
Also is each formula set to handle nulls the same way?

I checked the table fields for null and Crystal did not report any null records and the fields are set to not null in the table structure that is not working.  (The one that works allows nulls....)
This is the section that fails Mid({INVENTOR.ITEM_DESC},instr({INVENTOR.ITEM_DESC},'Pkg'),20)

OK maybe there is another way to get what we want.  We want to get the pkg size so we can use it in another formula.
Data
XYZ Widget Pkg/20
abc widget Pkg/6
TTT widget Pkg/100
KKK widget



Edited by mabrown - 21 Sep 2017 at 10:04am

basically you want the 'number' at the end of the string only when the string also has 'Pkg' or ' Pkg/' otherwise dummy in a zero.
Correct?

Yes

if instr( {INVENTOR.ITEM_DESC}, 'Pkg/') > 0 then
right( {INVENTOR.ITEM_DESC}, instr( {INVENTOR.ITEM_DESC})+4)
else instr( {INVENTOR.ITEM_DESC}, 'Pkg') > 0 then
right( {INVENTOR.ITEM_DESC}, instr( {INVENTOR.ITEM_DESC})+3)
else
'0'

I think this covers the bases correctly (not tested). If you need a numeric value (i.e. for math operations), then convert each string value to a number (i.e., CDbl function).

agreed with kevlray, although I think the instr() is missing the search text for both the THEN parts.
Also other things to consider, you can use InStrRev() instead to look for the strings in from right to left in case 'pkg' is used in another capacity in the string and it mixes up a row. Also your original process was using a trim in case these items might have lead or trailing spaces that would confuse the process.

I made some assumptions (and wrote the code on the fly) :-)

I added the missing search text and I did get it to work enough I can put in hold until I have time to hone it.  Thank you.