Group by Formula

[trbyrne]

I want to create a new group based on the following formula but it is not an available field in Group Expert. What can I do to be able to group by this formula?


//@REPAIRSTATUS
IF {REP1.RepairStatus}="100" THEN
"*** UNISSUED ***"
ELSE IF {REP1.RepairStatus}="200" THEN
"OUT TO VENDOR"
ELSE IF {REP1.RepairStatus}="300" THEN
if {@OneYear} then
if not {@archived} then
"NEED TO BE ARCHIVED"
else
"ARCHIVED REPAIRS"
else
"READY FOR CUSTOMER PICKUP

 

[lbass]

You would have to show us the content of your nested formulas, too.

-LB

 

[trbyrne]

Oh, OK. Here the are:

//@OneYear
if datediff("d",{@FixReturned},currentdate)>365 then
true
else
false


//@Archived
if instr(lcase({@DisplayComments}),"archive")>0 then
TRUE
else
FALSE

 

[lbass]

And any nested formulas in the nested formulas!

-LB

 

[trbyrne]

LOL - sorry about that, didn't catch them at first....here are all of the relevant formulas (I think)


//@REPAIRSTATUS
IF {REP1.RepairStatus}="100" THEN
"*** UNISSUED ***"
ELSE IF {REP1.RepairStatus}="200" THEN
"OUT TO VENDOR"
ELSE IF {REP1.RepairStatus}="300" THEN
if {@OneYear} then
if not {@archived} then
"NEED TO BE ARCHIVED"
else
"ARCHIVED REPAIRS"
else
"READY FOR CUSTOMER PICKUP"


//@OneYear
if datediff("d",{@FixReturned},currentdate)>365 then
true
else
false


//@Archived
if instr(lcase({@DisplayComments}),"archive")>0 then
TRUE
else
FALSE


//@FixReturned
if {REP1.DateReturnFromVend}>0 then
if len(totext({REP1.DateReturnFromVend},0,""))=5 then
date(2000+val(mid(totext({REP1.DateReturnFromVend},0,""),4,2)),
val(mid(totext({REP1.DateReturnFromVend},0,""),1,1)),
val(mid(totext({REP1.DateReturnFromVend},0,""),2,2)))
else
date(val(mid(totext({REP1.DateReturnFromVend},0,""),5,2)),
val(mid(totext({REP1.DateReturnFromVend},0,""),1,2)),
val(mid(totext({REP1.DateReturnFromVend},0,""),3,2)))



//@DisplayComments
//{@display} to be placed in the group footer:
whileprintingrecords;
stringvar x;
trim(x);


//@Comments
propercase({REP1C.CommentLine1}&" "&{REP1C.CommentLine2}&" "&{REP1C.CommentLine3}&" "&{REP1C.CommentLine4}&" "&{REP1C.CommentLine5})


//@ConcatComments
//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar x := x + {@Comments}+" ";


//@ResetComments
//{@reset} for the group header:
whileprintingrecords;
stringvar x;
if not inrepeatedgroupheader then
x := "";

 

[lbass]

You won't be able to group on this because of the display comments formula which is "whileprintingrecords."

-LB

 

[trbyrne]

Is there a way that I can modify my formulas so that I CAN group on REPAIRSTATUS?

 

[lbass]

What is your current group field (where the display comments formula is placed). If there is more than one group, please identify them by field and group #. Also, what is your selection formula? Do you have the option of using SQL expressions?

-LB

 

[trbyrne]

<GROUP 1 - {REP1.LOCATIONNUM}>
<GROUP 2 - {REP1.RepairStatus}>
***this is the section that I want to change to {@REPAIRSTATUS}***
<GROUP 3 - {REP1.SALESPERSONNUM}>
<GROUP 4 - {REP1.REPAIRNUM}>
***this is where my display comments formula is placed, in the footer of this group. the individual comments are contained in the <detail> section***
<DETAIL>



SELECT "REP1"."LocationNum", "REP1"."RepairNum", "REP1"."RepairStatus",
"VENDOR"."Name", "REP1"."DateOpened", "REP1"."DateIssuedToVend",
"CUSTOMER"."FirstName", "CUSTOMER"."LastName", "REP1"."RepairDescription",
"REP1"."ExtendedDescrLine1", "REP1"."ExtendedDescrLine2",
"REP1"."ExtendedDescrLine3", "REP1"."SalesPersonNum", "SLSMNFIL"."Name",
"LOCATION"."Name", "CUSTOMER"."CustomerNum", "REP1"."DatePromYYYYMMDD",
"REP1"."DateReturnFromVend", "REP1C"."CommentLine1",
"REP1C"."CommentLine2", "REP1C"."CommentLine3", "REP1C"."CommentLine4",
"REP1C"."CommentLine5"
FROM {oj (((("REP1" "REP1" INNER JOIN "VENDOR" "VENDOR" ON
"REP1"."VendorNum"="VENDOR"."VendorNum") INNER JOIN "CUSTOMER" "CUSTOMER"
ON "REP1"."CustomerNum"="CUSTOMER"."CustomerNum") INNER JOIN "LOCATION"
"LOCATION" ON "REP1"."LocationNum"="LOCATION"."LocNum") INNER JOIN
"SLSMNFIL" "SLSMNFIL" ON ("REP1"."SalesPersonNum"="SLSMNFIL"."Num") AND
("REP1"."LocationNum"="SLSMNFIL"."Location")) LEFT OUTER JOIN "REP1C"
"REP1C" ON ("REP1"."LocationNum"="REP1C"."LocationNum") AND
("REP1"."RepairNum"="REP1C"."RepairNum")}
WHERE NOT ("REP1"."RepairStatus"='400' OR "REP1"."RepairStatus"='500')
AND "REP1"."LocationNum"=1 AND NOT ("CUSTOMER"."CustomerNum"=9020310 OR
"CUSTOMER"."CustomerNum"=9033951)
ORDER BY "REP1"."LocationNum", "REP1"."RepairStatus",
"REP1"."SalesPersonNum", "REP1"."RepairNum"

 

[trbyrne]

I think what I need to do here is to eliminated any records that have the word "archive" in any of the rep1c comment fields. I've been playing with the selection formula but haven't found the right combination yet.

 

[lbass]

So you are no longer trying to group by a formula that references the archive formula and now you just want to eliminate records?

If so, then do you mean you want to eliminate RepairNum groups based on the presence of any archive records in the group? Or do you want to just eliminate individual details that contain "archive" in the comments section? Or is there only one detail line per RepairNum group?

-LB

 

[trbyrne]

The REP1 table has a one to many relationship with the REP1C table (inner-joined on REPAIRNUM and LOCATIONNUM fields). So, any single repair could have multiple records in the REP1C table (the PAGENUM field would increment).

I am now trying to eliminate any records from REP1 that have the word "archive" in any of the REP1C comment fields. So far the only thing that I've accomplished is to eliminate the individual comments that have "archive" in them, but the records from REP1 still appear. For the purposes of this discussion, there is only one REP1C record with the word "archive" in the COMMENT1 field (see selection formula below).


REP1
{repairnum} ***
{locationnum} ***

REP1C
{repairnum} ***
{locationnum} ***
{pagenum}
{comment1}
{comment2}
{comment3}
{comment4}
{comment5}

JOIN TYPE = Inner Join
ENFORCE JOIN = Not Enforced

SELECTION FORMULA
not (lcase({REP1C.CommentLine1}) like '*archive*')

 

[trbyrne]

I found my solution here....

http://www.dbforums.com/crystal-reports/1212704-how-exclude-records-crystal-report.html

 

[RJL1]

Not sure I'm familiar with your suggestion but I'm open to anything if you can provide some guidence

Thanks
RJL