Technical Questions: Group by String Retrieved in Formula

Print Page | Close Window

Group by String Retrieved in Formula

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=14729
Printed Date: 02 May 2024 at 10:21pm

Topic: Group by String Retrieved in Formula

Posted By: cms7602
Subject: Group by String Retrieved in Formula
Date Posted: 20 Oct 2011 at 8:42am

Hello all,

I'm definitely not a Crystal expert, so any help that can be provided would be greatly appreciated.

I need to sort a report based on the string data that I have extracted from a Description (string) field.

Example of the type of data stored in this string field {brptGLFinDet;1.DetailDesc}=

Invoice/20/920000/ZENITH TECH INC/ 8373003311/83733- /JB App

I am extracting the job# (83733-).

The job# will be located in a different place in the field depending on the type of transaction it is. This is the reason for the IF statement for the Jrnl {brptGLFinDet;1.Jrnl}. Ie, AP entries show the Job# between the 7th & 8th delimiter ("/"), AR trans show the Job# between the 5th & 6th delimiter.

I had success extracting the job# onto the report design using the formula:

local numbervar Start := instr({brptGLFinDet;1.DetailDesc},"/");
if
Start > 0 then

if {brptGLFinDet;1.Jrnl} = "AP" then split({brptGLFinDet;1.DetailDesc},"/")[7] else
if {brptGLFinDet;1.Jrnl} in[ "AR", "JB", "BL"] then split({brptGLFinDet;1.DetailDesc},"/")[6] else
if {brptGLFinDet;1.Jrnl} = "ER" then split({brptGLFinDet;1.DetailDesc},"/")[5]
else
"Undefined job"

However, I can't use this formula as a sort.

I'm not very good with variables, but I'm thinking that the use of variables may be the solution. (??)

Thanks in advance for any help!

Replies:

Posted By: DBlank
Date Posted: 20 Oct 2011 at 9:42am

try:

if instr({brptGLFinDet;1.DetailDesc},"/")> 0 then
(if {brptGLFinDet;1.Jrnl} = "AP" then split({brptGLFinDet;1.DetailDesc},"/")[7] else
if {brptGLFinDet;1.Jrnl} in[ "AR", "JB", "BL"] then split({brptGLFinDet;1.DetailDesc},"/")[6] else
if {brptGLFinDet;1.Jrnl} = "ER" then split({brptGLFinDet;1.DetailDesc},"/")[5]
else "Undefined job") else "Undefined job"

Posted By: cms7602
Date Posted: 20 Oct 2011 at 10:05am

Thank you for the response.

I tried the formula below, as suggested, but I still get the message, "A subscript must be between 1 and the size fo the array" when I try to use that formula in a Group.

Posted By: DBlank
Date Posted: 20 Oct 2011 at 10:16am

one of your splits is trying to get a value from too large of a number...

(if {brptGLFinDet;1.Jrnl} = "AP" then split({brptGLFinDet;1.DetailDesc},"/")[7] else
if {brptGLFinDet;1.Jrnl} in[ "AR", "JB", "BL"] then split({brptGLFinDet;1.DetailDesc},"/")[6] else
if {brptGLFinDet;1.Jrnl} = "ER" then split({brptGLFinDet;1.DetailDesc},"/")[5]

Posted By: cms7602
Date Posted: 20 Oct 2011 at 4:39pm

I hope this isn't a dumb question! Why does it work when I use it in the body of the design then? I only get that error message when I try to use this formula as my sort/group.

Posted By: DBlank
Date Posted: 20 Oct 2011 at 6:17pm

My guess is that you will get the error if you preview and go to the last page (and don't try to sort). If so, then the formula is choking on a particular row of data in your set.

Print Page | Close Window