Skip to Content

Mark duplicate record based on multiple condition

Hi All,

I need to use a formula to print Repeat next to the row displayed in the detail section. I do not have the choice to sort the data as it is expected to print in the order of records that are entered from the UI. Below table shows the sample data. The fields From Date, To Date and Transaction Code are directly pulled from the database. The Repeat? column should be a formula driven. The formula is expected to check for From and To Date and the Transaction Code.

If the From Date >= From Date getting displayed currently, and the To Date <= To Date getting displayed then the record should be marked as Yes for Repeat?. For example, the 4th record in the table has the From and To Date which falls between the From and To Dates of the first record in the table and hence, marked as repeat.

From Date    To Date       Transaction Code    Repeat? 
1-Jan-19     10-Jan-19     Transaction1         No 
11-Jan-19    14-Jan-19     Transaction2         No 
11-Jan-19    14-Jan-19     Transaction1         No 
5-Jan-19     8-Jan-19      Transaction1         Yes 
5-Jan-19     8-Jan-19      Transaction2         No 
11-Jan-19    11-Jan-19     Transaction2         Yes
  

I greatly appreciate your help. Looking forward for the solutions from the great minds.

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Best Answer
    Posted on Feb 05, 2019 at 07:28 AM

    Here is a working solution if someone needs to know.

    WhilePrintingRecords;
    
    
    Local StringVar transactionCode := "" & {myDatabase.transactionCode};
    Local StringVar fromDate := Totext({myDatabase.fromDate});
    Local StringVar toDate := Totext({myDatabase.toDate});
    
    
    StringVar Array transactionCodes;
    StringVar Array transactionDateFrom;
    StringVar Array transactionDateTo;
    StringVar repeat := "No";
    
    
    Local NumberVar index;
    Local NumberVar arraySize := Ubound(transactionCodes);
    Local booleanVar found := false;
    
    
     
    if not(transactionCode in transactionCodes) then 
    ( 
        redim preserve transactionCodes [count(transactionCodes)+1];
        redim preserve transactionDateFrom[count(transactionCodes)+1];
        redim preserve transactionDateTo[count(transactionCodes)+1];
        transactionCodes[count(transactionCodes)]:= transactionCode;
        transactionDateFrom[count(transactionCodes)]:= fromDate;
        transactionDateTo[count(transactionCodes)]:= toDate;
        repeat
    ) 
    else 
    (
        for index := 1 to arraySize do
        (
            if found then
                exit for;
            
            if transactionCodes[index] = transactionCode  then
            (
                if DateValue(fromDate) = DateValue(transactionDateFrom[index]) to DateValue(transactionDateTo[index]) and 
                        DateValue(toDate) = DateValue(transactionDateFrom[index]) to DateValue(transactionDateTo[index]) then
                (
                    found=true;
                    repeat := "Yes";                    
                )
                else
                (
                    redim preserve transactionCodes [count(transactionCodes)+1];
                    redim preserve transactionDateFrom[count(transactionCodes)+1];
                    redim preserve transactionDateTo[count(transactionCodes)+1];
                    transactionCodes[count(transactionCodes)]:= transactionCode;
                    transactionDateFrom[count(transactionCodes)]:= fromDate;
                    transactionDateTo[count(transactionCodes)]:= toDate;
                )
            )
        );
        
        repeat
    )
    
    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 30, 2019 at 06:16 PM

    There is no way to do this in Crystal if you're linking tables together for your report. Instead, you'll have to use a Command (SQL Select statement) that pulls ALL of the data for the report and will calculate the value for the Repeat column.

    See my blog post https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/ for information about the correct way to use Commands in Crystal. If you get stuck, you can post the SQL from your report (Database menu >> Show SQL Query) along with the type and version of database you're connecting to and I might be able to help.

    -Dell

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.