[RESOLVED] Newbie needs help - probably simple.

[drag0n_45]

Hello all,
This is my second Crystal Report. It took me about three weeks to get the first one to work properly, and I have absolutely no idea where to start on this one.

I wrote a program in Vb6 to store and manage all of our procedures. The procedures also contain forms (for example, a procedure could be how to use a certain program and it could have a form that is needed to request changes to that program). The procedures are stored in one table in the database, and the forms in another, linked in Crystal Reports X by a common key. Each form can contain multiple revisions, so at any given time there could be 5 or more instances of the same form, all with a different revision number. This is also true for each procedure.

I need to be able to display the procedure with the highest revision, then the information about the current procedure (seems simple enouhg,) but then I need to display all of the applicable forms for that procedure, with each form displayed being the highest revision number for that form only.

So, if I have a procedure OP-000 named Go To VBForums.com For Awesome Help, and it's the first revision dated 01/09/1987 and there's two forms associate with it: OP-000-01 named Moderator Privelage Request From, of which there are two revision: rev 0 and rev1. The other form could be OP-000-02 named Member Request Form, of whcih there may only be one revision: rev0.

I'd want that data to be displayed something like:

OP-000 Rev0 Go To VBForums.com For Awesome Help 01/09/1987

Forms:
OP-000-01 Rev1 Moderator Privelage Request Form
OP-000-00 Rev0 Member Request Form


I can handle the formatting, but notice that only the form with the highest revision number is being displayed? That's where I'm stuck.


It may also be of not that when I create the links in CRX I get the message saying "Your current configuration contains multiple starting points. Please be advised that this is generally not supported." I have no idea what that means, but just pressed ok.

[drag0n_45]

I manged to get this working, kind of. I created three groups, Procedure Number, Form Number, and Form Revision. I sorted form revision descending, so the highest revision would be on top, and created a maximum running total field that resets on FormNumber. I suppressed the details section if the running total field was greater than the current form's revision number.

However, when I add anything to the Group Header for the Procedure Number (except the pre-added section name), all the records start displaying once, twice, thrice, or sometimes even four times. there doesn't seem to be any order to it. I need to display the data for each procedure, but I don't understand why displaying information would matter.

[brucevde]

If I understand correctly. Try this

Group Header 1: Procedure Name
Group Header 2: Form Number
Details: Suppress the entire section
Group Footer 2: Display the Form Revision fields.

Sort the records on Form Revision Ascending.

Since the Details are suppressed only the last record per group, the record with the highest revision, will be available for printing in the Group Footer 2 section.

It may also be of not that when I create the links in CRX I get the message saying "Your current configuration contains multiple starting points. Please be advised that this is generally not supported." I have no idea what that means, but just pressed ok.

All tables must be linked together. If you have TableA linked to TableB but TableC is not linked to either, how would Crystal "know" which records to pull from TableC. Fields from TableC that are on the report would most likely be ignored.

[drag0n_45]

Hmm. I tried that initially - it's not that simple. I need to display the procedure information, and under it the form information. I think I got it working.. I'll post more when I'm 100%.

[drag0n_45]

OK...here's what I got...

two tables, procedures and forms

Group 1: Procedures.DisplayName (in other words, procedure number)
Group 2: Procedures.RevNumber (since there can be multiple rev numbers - this is sorted desc - it will bring the largest form number to the top)
Group 3: Forms.FormNumber
Group 4: Forms.RevNumber (since there can be multiple form numbers - this is sorted desc)


I've got two running total fields:

FormMax: On the Forms.RevNumber field, total type: maximum, reset on Forms.FormNumber (so that the rev number rests for each form)
This is placed in the Details section (where each form will be shown)

ProcedureRevMax: On the Procedures.RevNumber field, total type: maximum, reset on Procedures.DisplayName (so that the procedure rev number resets for each procedure).
This is placed in the Group 1 Header (for each procedure).


I have a formula field HideEntry with the following code:

if {#FormMax}>{Forms.RevNumber} or {#ProcedureRevMax}>{Procedures.RevNumber} then
(
true;
)
else
(
false;
)

And I suppress the details section if HideEntry=true.

Could this be simpler? Keep in mind that there can be multiple revisions of each form and procedure, and that the revision for the procedure and the forms are independent, but that I need to show the latest revision of all items.

[brucevde]

Could this be simpler? Without changing the database you mean?

The only other thing I can think of is Global variables. One to control hiding the unwanted Procedure sections, another for the Form sections. You would need to create at least 4 formulas and set the suppression formula for 2 sections so I don't think it is "simpler", just another method.

[drag0n_45]

Yeah, I'm thinking this will work better, LOL. Yes, I meant without changing the database. My personal idea to designing the databases is to make their layout simple and easy to comprehend, and then write the software around it.

[brucevde]

My personal idea to designing the databases is to make their layout simple and easy to comprehend Each to his own...

I would at least add another column to both tables, called ExpiryDate. Whenever a new Revision is inserted set its ExpiryDate to Null and then set the ExpiryDate of the previous record(s) to the current date. You could then query using "Where ExpiryDate Is Null" which ensures you only get the "current revision records" and you won't need any of the running totals or suppression of sections in Crystal.

Simple and easy to comprehend.

[drag0n_45]

Ah...that....is a wonderful idea. Well noted.