How to combine records with similar value in different fields

[beacon5]

Hi everybody,

[Crystal Reports 2008]

Right off the bat, let me mention that I didn't create the database I'm having to work with and don't have access to modify the tables, create views, etc.

That being said, here's goes nothing.

I'm working with a table that is the back-end for four forms on the front-end. This table contains ALL of the fields from each of the four forms, but doesn't share the data between rows...a new row is created for each form.

One of the fields in each of the forms is a field that holds an order number. However, the field on each of the forms is named differently and has its own field in the table. So, four forms with a different order number field on each form means there are four fields that hold the same value.

For instance:

Form 1 has values Patient ID, Patient Name, Referral Order Number, and a bunch of other values

Form 2 has values Patient ID, Patient Name, Appointment Order Number, and a bunch of other values

Form 3 has values Patient ID, Patient Name, Consult Order Number, and a bunch of other values

Form 4 has values Patient ID, Patient Name, Review Order Number, and a bunch of other values.

Each time you complete a form, a new row is created. The forms are setup to be filled out in order: Referral, Appointment (one or more), Consult, and Review. Because each of the forms has a different placeholder for the order number, which is common between the forms, but not automatically passed to subsequent forms, how can I combine these records so I can pull the Referral, Appointment, Consult, and Review for a single order number that appears in multiple rows?

I know I can set this up in my SQL to say "WHERE Ref_OrderNumber = 123456 OR App_OrderNumber = 123456...", but this will still pull each row as a separate instance. If I've entered data into each of the four forms, then I'll have four rows of data when what I really want is one row where the same order number appears in each instance.

I was thinking that it might be possible to do this with multiple embedded subreports, but that was before I found out that you can't have a subreport in a subreport.

Does anyone know if this is possible and, if so, how I can go about setting it up?

Thanks,
beacon

 

[lbass]

How are the forms distinguished in the database? Is there a field {table.form}? You could try using a command as your sole datasource. Go to the database expert->your datasource->add command (above the table list), and then set up the command like this:

Select 'Form 1' as WhichForm,table.`Patient`ID`,table.`Patient`Name`, table.`Referral Order Number` as Order Number, table.`other field1`
from table
where [add your criteria]

Union

Select 'Form 2' as WhichForm,table.`Patient`ID`,table.`Patient`Name`, table.`Appointment Order Number` as Order Number, table.`other field1`
from table
where [add your criteria]

Union

Select 'Form 3' as WhichForm,table.`Patient`ID`,table.`Patient`Name`, table.`Consult Order Number` as Order Number, table.`other field1`
from table
where [add your criteria]

union

Select 'Form 4' as WhichForm,table.`Patient`ID`,table.`Patient`Name`, table.`Referral Order Number` as Order Number, table.`other field1`
from table
where [add your criteria]

This would put the fields in the same ordinal position in each union statement in one field. By using "union" instead of "union all" you would get distinct rows. Not sure if the other fields all correspond, but I'm guessing they do. You can use the {command.whichform} to distinguish forms if you need to.

-LB

 

[beacon5]

That's the problem...I don't really know how the forms are distinguished. All I know about the setup is that the tables use an Oracle database, that I don't have any access to directly, and the forms are a part of a web-based Java app, that I also don't have direct access to.

There are four forms and every field on each of the forms is in one table, but the number I need to write reports for is available in four separate fields, so when I include anything else that appears in each row, like the Patient ID, it shows a separate record for each. I tried using the "Select Distinct Records" option under the Database menu, but it still shows multiple records because the order number is in four separate places.

I'm not confident that anything can be done to resolve this through report writing, but I thought I would bring it here in case someone had ever run into the same issue with the same limited access.

I'm not very familiar with how Views work (are they just a query?), but would my administrators be able to create a calculated field that copies the order number so it would show up under the same column on each row?

Thanks,
beacon

 

[lbass]

That's what the above command would do. Did you try this? It should work.

-LB

 

[beacon5]

I wouldn't personally be able to set this up because I don't have the administrator access I think I would need to be able to set it up.

If there's no way to resolve it without doing what you described, I'll get with the admins for the database and see if they will try this for me.

Thanks,
beacon

 

[fisheromacse]

what lbass is suggesting, does not require any special database permissions. It is done from within crystal.
in the Crystal menus --->>> Go to the database expert->your datasource->add command

 

[beacon5]

Wow, I'm extremely sorry. In my hastiness, I read through lbass' post too quickly and didn't realize that she was talking about the 'Add Command'. I had seen the 'Add Command' before, but had never done anything with it, so I think my brain just dismissed. Please forgive me.

Thank you lbass and fisheromacse for your help and patience,
beacon