How to put detail records in fixed formÉ

[Former Member]

Hi,

I am trying to build a crystal report based on an existing printed form. The form includes elements that look like the following:

Activity

Hot Oiler - 4 mbtu 7mbtu

Pressure Pumping - 5K 10K 15K

Travel - 5K 10K 15K

Standby - 5K 10K 15K

Each Activity also has a quantity, a rate, and a total (quantity * rate) associated with it.

The user used to circle the applicable value on the paper form (for example, if it was a 4 mbtu hot oiler vs. a 7 mbtu hot oiler).

In the application and database the user will enter line items in the work order that specify if it is a Hot Oiler 4 mbtu or a Hot Oiler 7mbtu. Similarly, the user will create a record that is either Pressure Pumping 5k or Pressure Pumping 10K or Pressure Pumping 15K. And so on.

The application is capturing all of this information. I want the crystal report to still display each of these elements as they are laid out on the current paper form however because these are detailed records I do not know how to place them in the report properly. If we take the first line in the form, I created the formula:

+if {field_tickets_has_resources.resources_id} in [150, 151] then

"Hot Oiler - " & "4 mbtu" else

if {field_tickets_has_resources.resources_id} = 163 then

"Hot Oiler - " & "7 mbtu" else

"Hot Oiler - "+

I created another formula for the second line in the form:

+if {field_tickets_has_resources.resource_roles_id} = 15 then

"Pressure Pumping - 5K" else

if {field_tickets_has_resources.resource_roles_id} = 18 then

"Pressure Pumping - 10K" else

if {field_tickets_has_resources.resource_roles_id} in [22, 24] then

"Pressure Pumping - 15K" else

"Pressure Pumping -"+

And so on. However, I cannot place each of these formula beside each other in a details line because it places the values horizontally rather than vertically in the report. I cannot place these in a header because they are actually detailed records.

I hope this makes sense. Any help, as always, is appreciated!

Mark

[former_member292966]

Hi Mark,

I recommend a similar apporach but the first formula will be created so we can group the records by the Activity type. For example all the Hot Oilers then Pressure Pumping, etc.

We group on this formula then a second formula will build a list of the quantities.

One final formula in the Group Footer will display everything on a single line. We can then hide the Group Header and Detail section because we only want to see one line per actvity type.

Create a group on this formula:

@ActivityGroup

If {field_tickets_has_resources.resources_id} <> 0 Then 
   "Hot Oiler" 
Else If {field_tickets_has_resources.resource_roles_id} <> 0 Then 
         "Pressure Pumping";

This formula will define and reinitialize a string variable for each group. This goes into the Group Header and can be suppressed.

@Initialize

WhilePrintingRecords; 
StringVar myQuantity := "";

This formula goes into the Detail section. Based on your first formula I'm guessing the Activity Type is based on which field is populated. I assume only one field per record can have a value that isn't 0.

@Quantity

WhilePrintingRecords; 
StringVar myQuantity; 
         
If {field_tickets_has_resources.resources_id} <> 0 Then 
   (Select {field_tickets_has_resources.resources_id} 
      Case 150  :myQuantity := myQuantity & "4 mbtu "
      Case 151  :myQuantity := myQuantity & "4 mbtu "
      Case 163  :myQuantity := myQuantity & "7 mbtu ";)
Else If {field_tickets_has_resources.resource_roles_id} <> 0 Then 
   (Select {field_tickets_has_resources.resource_roles_id} 
      Case 15   :myQuantity := myQuantity & "5K "
      Case 18   :myQuantity := myQuantity & "10 "
      Case 22   :myQuantity := myQuantity & "15K "
      Case 24   :myQuantity := myQuantity & "15K ";)

This formula goes into the Group Footer and builds your string.

@Display

WhilePrintingRecords; 
StringVar myQuantity; 

{@ActivityGroup} & "-" & myQuantity;

All the sections except for the Group Footer can be formatted to hide.

Hope this helps,

Brian

[Former Member]

Hi Brian,

Thanks for this. As I work through this I'm realizing that this approach looks like it will just produce a result set if there are values present in the work order document. I'd like to present all possible data values even if they do not exist in the work order/database.

It's a way for workers to look at the report/form like a checklist. So, in the event, they forgot to include something in the screen of the application the printout will reveal that to them.

The form values look like the following:

Hot Oiler Working - 4mbtu 7mbtu

Pressure Pumping - 5K 10K 15K

Travel - 5K 10K 15K

Travel Hot Oiler - 4mbtu 7mbtu

Standby - 5K 10K 15K

2nd Operator

Support Unit

Chemical Pumping

Next to each one of the options above will be 3 column values: quantity (hours); rate/hour; and total

So, a guy could enter, on a work order, a Hot Oiler (first field) Working (second field), 4mbtu (3rd field), 10 hours (4th field), $100/hr (5th field), $1,000 (calculated value).

A second record on the work ticket could be Pressure Pumping (first field) and 5K (second field), 8 hours (third field), $50/hour (fourth field), $400 (calculated field).

And so on. But I'd like to have the above list at least displayed on the report even if there isn't a value in the database. For example, say a "2nd Operator" and "Standby" options weren't used in the work order in the application at least they'd still be displayed in name but there wouldn't be any related quantity, rate, or totals for them.

Does this clarify things a bit more as to what I'm hoping to achieve?

thx!

Mark