Search code examples
crystal-reportscrystal-reports-xi

Crystal Reports XI Earliest Data for the Record in the Database

Currently am working on a report that pulls setups on an account, this does require a bit of back-story.

A user can make a setup on an account, but another user can come in and use the "EDIT" action code, which then cancels the previous setup, creating a new one, with either different pay options, a new account in the setup, anything really. The way we pay offices is based on who had the original setup.

The report I am making needs to pull the original creator of the setup, but with the most recent setup information.

It would be like accountNumber, oldUser, oldUserSupervisor, newSetUpDate, newSetUpAmt would be the columns gathered.

These columns come from one table called "Setups"

When querying the the table, if you don't have any restrictions, it would show all the past setups and current, but with an inactive flag, isActive = "N" if it is inactive and isActive = "Y" if it is...

What I need to do is get the original setup user, then pull the information about the new setup and make that one line like listed above.

I have tried using variables to go through each record, but it always returns the latest information.

This is my variables used in formula workshop (I did initialize them prior yes, in the header)

Shared DateVar createDate;
Shared BooleanVar countNext;
Shared StringVar previousRehab;
Shared StringVar cUser;

// Checking for the correct create date
If ( countNext = true ) Then
    createDate := {CreateDate}
Else
    createDate := createDate;

// Checking for the correct user
If ( countNext = true ) Then
    cUser := {User}
Else
    cUser := cUser;

//Checking for the correct cancel code
If ( {cancelReason} = "EDIT" ) Then
    countNext := False
Else
    countNext := True;

It is supposed to scan through each record, then save the information if it matches then stop the search and print the record. Currently it always displays the most recent information, without touching the past.

Solution

  • This way you will stuck up in a mess.

    follow below process:

    Assuming account is common for old and new.

    1. Create a group using account number

    2. place these columns oldUser, oldUserSupervisor, newSetUpDate, newSetUpAmt in detail section.

    3. For every column create an array to store all the data and reset the array at group header. Follow below process.

    In group header create formula @Reset

    Shared Stringvar Array olduser="";
Shared Stringvar Array oldUserSupervisor="";
Shared Srtingvar array newSetUpDate="";
Shared Stringvar Array newSetUpAmt="";

    Now details create 4 different formulas for 4 columns and write as below. I am showing for one column extend to 4 columns for date and amount make sure to convert to string before storing in array:

    Create a formula @storeolduser

    olduser:=olduser+databasefield;
1

    Like this create 4 formulas for 4 columns

    In report footer extract all 4, first element of array as old information and last element of array has latest information

    olduser[1] //will give first user access
olduser[Ubound[olduser]] //will give last user accessed

    Extract all in group footer and show as per your requirement