Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Crystal Reports formula

Posted on 2012-08-21
12
Medium Priority
?
406 Views
Last Modified: 2012-08-24
Hello,

The second part of this formula I cannot get the syntax right. So I wrote it out in a logical format for how I want it to work.  Can you please help me format it?  Thanks, Joel


//From here to here the code is fine......................

WhileReadingRecords;
Local DateVar RealDueDate ;

if CDate({tblRevisions.DRAWINGSTAGEDUE}) < CDate({tblRevisions.CheckerComplete}) then
(
RealDueDate := Cdate({tblRevisions.CheckerComplete});
)
else
(
RealDueDate := Cdate({tblRevisions.DRAWINGSTAGEDUE});
);

//From here to here the code is fine.....................


//From here to here I need help.  Wrote code in logical format.....................

if {tblTaskLock.PaymentTerms} = "N30" then

use RealDueDate, but

if {tblTaskLock.PaymentTerms} = "N30" and if the greatest date of (CDate({tblTaskLock.StandardColorApprovalDate}) or (CDate({tblTaskLock.CustomColorApprovalDate}) is greater than RealDueDate then

use the greatest date of (CDate({tblTaskLock.StandardColorApprovalDate}) and (CDate({tblTaskLock.CustomColorApprovalDate})



if {tblTaskLock.PaymentTerms} <> "N30" then

use RealDueDate, but

if {tblTaskLock.PaymentTerms} <> "N30" and if the greatest date of CDate({tblTaskLock.CheckReceived1}) or (CDate({tblTaskLock.StandardColorApprovalDate}) or (CDate({tblTaskLock.CustomColorApprovalDate}) is greater than RealDueDate then

use the greatest date of CDate({tblTaskLock.CheckReceived1}) and (CDate({tblTaskLock.StandardColorApprovalDate}) and (CDate({tblTaskLock.CustomColorApprovalDate})

//From here to here I need help.  Wrote code in logical format.....................
0
Comment
Question by:Genius123
  • 5
  • 4
  • 2
11 Comments
 
LVL 35

Assisted Solution

by:James0628
James0628 earned 1000 total points
ID: 38319383
When you said "greatest date", at first I thought you meant the latest date in a group of records.  For example, you have a group of records for a task, and you want to look at the latest StandardColorApprovalDate in that group of records.

 But know I'm thinking that you meant the latest date in those fields in the current record (eg. StandardColorApprovalDate or CustomColorApprovalDate, whichever is later).  Assuming that that's correct, the logic actually seems to be quite simple.  It seems like Maximum will do nicely.  Give this a try.

//From here to here the code is fine......................

WhileReadingRecords;
Local DateVar RealDueDate ;

if CDate({tblRevisions.DRAWINGSTAGEDUE}) < CDate({tblRevisions.CheckerComplete}) then
(
RealDueDate := Cdate({tblRevisions.CheckerComplete});
)
else
(
RealDueDate := Cdate({tblRevisions.DRAWINGSTAGEDUE});
);

//From here to here the code is fine.....................


//From here to here I need help.  Wrote code in logical format.....................

if {tblTaskLock.PaymentTerms} = "N30" then
  Maximum ([ CDate({tblTaskLock.StandardColorApprovalDate}),
   CDate({tblTaskLock.CustomColorApprovalDate}), RealDueDate ])
else
  Maximum ([ CDate({tblTaskLock.CheckReceived1}), CDate({tblTaskLock.StandardColorApprovalDate}),
   CDate({tblTaskLock.CustomColorApprovalDate}), RealDueDate)

//From here to here I need help.  Wrote code in logical format..................... 

Open in new window

0
 
LVL 101

Expert Comment

by:mlmcc
ID: 38320336
I think this can be simplified since RealDueDate is just the maximum of the other 2


if {tblTaskLock.PaymentTerms} = "N30" then
  Maximum ([ CDate({tblTaskLock.StandardColorApprovalDate}),
   CDate({tblTaskLock.CustomColorApprovalDate}),
    CDate({tblRevisions.DRAWINGSTAGEDUE}), CDate({tblRevisions.CheckerComplete})])
else
  Maximum ([ CDate({tblTaskLock.CheckReceived1}), CDate({tblTaskLock.StandardColorApprovalDate}),
   CDate({tblTaskLock.CustomColorApprovalDate}),
    CDate({tblRevisions.DRAWINGSTAGEDUE}), CDate({tblRevisions.CheckerComplete}))


mlmcc
0
 

Author Comment

by:Genius123
ID: 38321710
Hello,

Both replies are close to what I need.  I need to do something like this though:

if {tblTaskLock.PaymentTerms} = "N30" then
  if Maximum ([ CDate({tblTaskLock.StandardColorApprovalDate}),
   CDate({tblTaskLock.CustomColorApprovalDate}) > RealDueDate ] then

give me the Maximum ([ CDate({tblTaskLock.StandardColorApprovalDate}),
   CDate({tblTaskLock.CustomColorApprovalDate})

Thanks, Joel
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
LVL 101

Expert Comment

by:mlmcc
ID: 38321781
WHat do you want if it maximum of the 3 is smaller?
By the original question you want the maximum of the other 2.

The maximum of the 5 values will be the same with fewer tests.

mlmcc
0
 

Author Comment

by:Genius123
ID: 38321795
It would be RealDueDate.  So:

if {tblTaskLock.PaymentTerms} = "N30" then
  if Maximum ([ CDate({tblTaskLock.StandardColorApprovalDate}),
   CDate({tblTaskLock.CustomColorApprovalDate}) > RealDueDate ] then

give me the Maximum ([ CDate({tblTaskLock.StandardColorApprovalDate}),
   CDate({tblTaskLock.CustomColorApprovalDate})

else

RealDueDate
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 1000 total points
ID: 38322768
Have you tried the formulas?

You are trying to do

If A > B then
   R = A
Else
    R=B

If Max(c,d,e) > R then
    Max(c,d,e)
Else
   R

This would mean you are looking for the max of (C,D,E,R) which is the same as Max(C,D,E,A,B)

//From here to here the code is fine......................

WhileReadingRecords;
Local DateVar RealDueDate ;

if CDate({tblRevisions.DRAWINGSTAGEDUE}) < CDate({tblRevisions.CheckerComplete}) then
(
     RealDueDate := Cdate({tblRevisions.CheckerComplete});
)
else
(
     RealDueDate := Cdate({tblRevisions.DRAWINGSTAGEDUE});
);

//From here to here the code is fine.....................


//From here to here I need help.  Wrote code in logical format.....................

if {tblTaskLock.PaymentTerms} = "N30" then
(  if Maximum ([ CDate({tblTaskLock.StandardColorApprovalDate}),
                          CDate({tblTaskLock.CustomColorApprovalDate})]) > RealDueDate then
    Maximum ([ CDate({tblTaskLock.StandardColorApprovalDate}),
                        CDate({tblTaskLock.CustomColorApprovalDate})]) 
Else
    RealDueDate 
)
else
(
    If  Maximum ([ CDate({tblTaskLock.CheckReceived1}), 
                            CDate({tblTaskLock.StandardColorApprovalDate}),
                            CDate({tblTaskLock.CustomColorApprovalDate})]) >  RealDueDate  Then
          Maximum ([ CDate({tblTaskLock.CheckReceived1}), 
                             CDate({tblTaskLock.StandardColorApprovalDate}),
                             CDate({tblTaskLock.CustomColorApprovalDate})]) 
    Else
         RealDueDate
);
//From here to here I need help.  Wrote code in logical format..................... 

Open in new window



Please understand, we are trying to help you write the code more efficiently.  The fewer tests you have to do the faster the report will run

mlmcc
0
 

Author Comment

by:Genius123
ID: 38322828
This makes sense mlmcc.  Let me try it out and I'll come back here shortly.
0
 

Author Comment

by:Genius123
ID: 38323392
This worked great.  Thank you for helping me understand how to make my code more efficient.  I am pasting my final code below just for the record.  One final question if I can.  Why do you think the brackets [] are needed for the Maximum function?  Seems strange.

Thanks, Joel
---------------------------
WhileReadingRecords;
Local DateVar RealDueDate ;

RealDueDate := Maximum([ Cdate({tblRevisions.CheckerComplete}), CDate({tblRevisions.DRAWINGSTAGEDUE})]);

if {tblRevisions.DrawingStage} = "Ready For Fab Docs" or {tblRevisions.DrawingStage} = "Released For Fab" then
 if {tblTaskLock.PaymentTerms} = "Net30" then
      Maximum ([ CDate({tblTaskLock.StandardColorApprovalDate}),
                CDate({tblTaskLock.CustomColorApprovalDate}),
                      CDate({tblRevisions.CheckerComplete}),
                      CDate({tblRevisions.DRAWINGSTAGEDUE})])

 else


      Maximum ([ CDate({tblTaskLock.StandardColorApprovalDate}),
                CDate({tblTaskLock.CustomColorApprovalDate}),
                      Cdate({tblRevisions.CheckerComplete}),
                      Cdate({tblTaskLock.CheckReceived1}),
                      CDate({tblRevisions.DRAWINGSTAGEDUE})])
else
RealDueDate;
---------------------------
0
 
LVL 35

Expert Comment

by:James0628
ID: 38323797
That's just the way the Maximum function works.  Look it up in the CR Help.  The main argument can be a field, in which case it will give you the maximum value for that field (in the whole report or in a group), or the main argument can be an array, in which case it will give you the maximum value in the array.  So, you put the fields in [] to create an array, and then use Maximum to get the maximum value in the array.

 James
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 38324820
I really think you should have split the points between my comment and James initial comment.

mlmcc
0
 

Author Comment

by:Genius123
ID: 38325174
Is there a way to change the points, or is it too late?
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Loops Section Overview
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question