Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Group by Formula

Status
Not open for further replies.

trbyrne

IS-IT--Management
Jul 5, 2007
60
US
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
 
You would have to show us the content of your nested formulas, too.

-LB
 
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
 
And any nested formulas in the nested formulas!

-LB
 
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 := "";
 
You won't be able to group on this because of the display comments formula which is "whileprintingrecords."

-LB
 
Is there a way that I can modify my formulas so that I CAN group on REPAIRSTATUS?
 
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
 
<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"

 
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.
 
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
 
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*')
 
Not sure I'm familiar with your suggestion but I'm open to anything if you can provide some guidence

Thanks
RJL
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top