Retaining Original Approval Dates

[mwake]

I'm using CR 2008 with an Oracle 10g database as my data source. I've developed a registration detail report which tracks licenses for drug products. I am using a registration entry table which contains entry types (ex. Product, Package, Manufacturer, etc), submission and approval dates, and also a license id. I am linking the Reg Entry table to a License table via the license id. It contains the license number, expiration date, original license id, and submission and approval dates for the license. When a license is renewed, a new entry is created in the License and Reg Entry tables, using the new license id. The new license record contains the original license id, so when I run the report after a license is renewed, they want to see the new license number with the new expiration date, but the want to see the original approval date of original license entry, not the approval date of the new license

Reg_Entry table License table
Entry_ID 50 License_ID 1234
Entry_Type Product License_# ABCD
Approval Date 1/1/2000 Approval Date 12/12/1999
License_ID 1234 Original License ID null

Entry_ID 51 License_ID 5678
Entry_Type Product License_# EFGH
Approval Date 1/27/2012 Approval Date 1/27/2012
License_ID 5678 Original License ID 1234

So I need a formula that says if IsNull(Orig_License_ID)=False then Reg_Entry.Approval Date where License.Original_License_ID=Reg_Entry.License_ID and Entry_Types are the same, so that I retain the original approval date with the new license renewal data. After renewal,the report should show only 1 product record(the original license entries will be flagged obsolete when the license expires)with the new license #, a new expiration date, but show an approval date of 1/1/2000. No mater how many renewlas take place, I need to always retain the original approval date. I know there is an easy way to do this, but I'm having a brain freeze..

 

[MCuthill]

mwake,

Though I am not 100% this is the solution (Friday PM, my brain may have the same issue as yours at the moment)... but syntax-wise I'll take a swing at the IF statement.

IF Not(IsNull(Orig_Licence_ID)) ...

**is the syntax for IsNull()=False.

*kickstarts brain*
Could you provide the structure of the report?
I think you may be able to "cheat" and simply grab the Minimum({Table.ApprovalDate}) -- assuming the original approval date is always the earliest in a group.

My apologies for not being more of a help on this one, but hopefully not a detrimental post!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[lbass]

Create a formula {@InitDate} like this:

if isnull({License.Original_License_ID}) then
{Reg_Entry.Approval_Date}

Then insert a group on {License.License_ID} and insert a minimum on {@InitDate} at the group level. Sort in descending order by Reg Entry. Approval Date, and drag the fields into the group header, along with the minimum of {@InitDate} and then suppress the other sections.

-LB