I have an error in a report when I try to split a field that doesn’t have the index value in the field. the first 22 worked but the 23rd didn’t exist but it may in some cases. I have potentially 1 to 78 indexes and it may vary.
The Field shows #error
Expression I’m using:
=Microsoft.VisualBasic.Strings.Split(First(Fields!Description.Value, “JobHead”), Microsoft.VisualBasic.Strings.chr(13)).GetValue(23)
iif(
First(Fields!Description.Value, "JobHead").Split(chr(13)).Length >= 23,
First(Fields!Description.Value, "JobHead").Split(chr(13))(23),
"")
However, take a look at STRING_SPLIT too:
I tried it but it still gives an error if the is not there. It won’t use the false “” parameter. It works for the lower values.
Did you try the String_Split in the SQL?
Not yet.
Another option is to use Report Functions. In the Report Properties, there’s a Code section and you can write small VB routines:
Public Function ConvertToTable(ByVal s as String) as String
Dim sa() as string
sa = split(s, "~")
if (sa.Length < 78) then
return("")
end if
Return sa(77)
End Function
Edit
Then your expression becomes:
=Code.ConvertToTable(First(Fields!Description.Value, "JobHead"))
Interesting, I haven’t used report functions. So this looks like it would make a table of 78 and even if the text I’m splitting has a variable amount less than 78. Then I can check the value isn’t “” and use it.
All standard Epicor reports use Report Functions so check out what they’re doing. There’s no syntax check until you upload it so do a little at a time and build on it.
Just curious…
Can you show us any examples of the field (Description)?
The field data below has separated returns of chr(13) I can split each and put them in specific fields on the report until I hit one missing in the index value then everything I’ve tried will return an error.
A)W/C 7.63 X 9.7 X 30.18 W/SKIN B)W/C 5.13 X 9.7 X B)W/C 5.13 X 9.7 X 30.18 C)W/C 7.63 X 9.7 X 30.18 W/SKIN D)W/C 5.13 X 9.7 X 30.18 E)S/C 1.67 X 12.5 X 31 W/SKIN F)F/C .67 X 12.5 X 25 W/SKIN G)D/C 1.5 X 28.18 X 31 W/SKIN H)W/C .125 X 18.5 X 26 (PLS-.125) I)W/C .125 X 1.5 X 30.18 (PLS-.125) J)F/C .5 X 30.18 X 35.4 W/SKIN K)F/C .5 X 10 X 13 W/SKIN L)F/C 3 X 20.5 X 30 W/SKIN M)F/C .5 X 20.5 X 26.5 W/SKIN N)W/C 3 X 10 X 30 W/SKIN O)W/C 2 X 9.72 X 30.18 W/SKIN P)W/C 4.13 X 9.72 X 30.18 W/SKIN Q)F/C 2X9.72X30.18 W/SKIN U)CASE ASSY R)LAM A-F,J,I&V V)W/C.13X1.5X30.18 W/ S)LAM K - M W)W/C 5.13X9.72X30.18 W/S T)LAM O-Q,H,W&X X)W/C1.88X7.6X26.18W/NNN U)CASE ASSYR)LAM A-F,J,I&V V)W/C.13X1.5X30.18 W/S)LAM K - M W)W/C 5.13X9.72X30.18 W/ST)LAM O-Q,H,W&X X)W/C1.88X7.6X26.18W/NNN
This is the code in a field expression that works. Each field uses a specific number of the index.
=Microsoft.VisualBasic.Strings.Split(First(Fields!Description.Value, “JobHead”), Microsoft.VisualBasic.Strings.chr(13)).GetValue(23)
Have you tried adding a Chr(13) to the end of it?
And you’re sure its a base 1 (meaning GetValue(1) returns the first element), and not base 0 (where GetValue(0) returns the first, and GetValue(22) would return the 23rd) ??
the problem is each record changes it the number of elements. may be one to 78. as soon as the number doesn’t exist there is an error. I’ve even tried a IIF it is over a number to put “” and it still evaluates if it contains the index and errors. I haven’t found a way to ignore the error.
=IIF(
First(Fields!Description.Value, “JobHead”).Split(chr(13)).Length < 24,Microsoft.VisualBasic.Strings.Split(First(Fields!Description.Value, “JobHead”), Microsoft.VisualBasic.Strings.chr(13)).GetValue(24),
“”)
Just looked up the split function
Returns a zero-based, one-dimensional array containing a specified number of substrings.
(emphasis mine)
So your expression should be something like (not checked for syntax)
=IIF(First(Fields!Description.Value, "JobHead").Split(chr(13)).Length <= 23,
Microsoft.VisualBasic.Strings.Split(First(Fields!Description.Value,"JobHead"),Microsoft.VisualBasic.Strings.chr(13)).GetValue(22),
"")
Edit, not sure how you’re get A)W/C ... as your first line, unless … there is a CR prior to it.
Corrected function code (was GetValue (24), changed to GetValue(22) … )
The split command returns the length of the array but isn’t the index zero based?
I made the same mistake in my example above…
This is the expression for A and it works.
=Microsoft.VisualBasic.Strings.Split(First(Fields!Description.Value, “JobHead”), Microsoft.VisualBasic.Strings.chr(13)).GetValue(0)
If I try 24 thru 78 that doesn’t exist with this record it errors.
I tried your code and still got the error. Because 24 doesn’t exist even if the IIF is True or false.
Are you sure you’re getting the Split that you think? I’m only guessing at what your data is supposed to be, but the following looks like it failed to split some “records”
Let try this:
Public Function GetItem(ByVal s as String, itm as integer) as String
Dim sa() as string
sa = split(s, chr(13))
if (sa.Length < itm) then
return("")
end if
Return sa(itm)
End Function
Then your expression would be same on every item but you would vary the index:
=Code.GetItem(First(Fields!Description.Value, “JobHead”),< zero-based item # >)
