| Author |
Message |
mabrown
Newbie
Joined: 08 Jun 2007
Online Status: Offline
Posts: 27
|
   Topic: Start position less than 1 error
Posted: 21 Sep 2017 at 7:19am |
|
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
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 21 Sep 2017 at 7:57am |
|
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"?
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 21 Sep 2017 at 7:59am |
|
also note that using < on a string may not be getting you want you think or intend...
'1' is > '09000'
|
|
IP Logged |
|
mabrown
Newbie
Joined: 08 Jun 2007
Online Status: Offline
Posts: 27
|
Posted: 21 Sep 2017 at 8:06am |
|
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
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 21 Sep 2017 at 8:59am |
|
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?
|
|
IP Logged |
|
mabrown
Newbie
Joined: 08 Jun 2007
Online Status: Offline
Posts: 27
|
Posted: 21 Sep 2017 at 10:03am |
|
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
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 21 Sep 2017 at 10:24am |
|
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?
|
|
IP Logged |
|
mabrown
Newbie
Joined: 08 Jun 2007
Online Status: Offline
Posts: 27
|
Posted: 21 Sep 2017 at 10:32am |
|
Yes
|
|
IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 21 Sep 2017 at 1:29pm |
|
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).
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 22 Sep 2017 at 9:51am |
|
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.
|
|
IP Logged |
|
|
|
