Finding all records from base records and its derivatives

[dleewms]

Hi,

I'm using Crystal Reports version 10 on MS SQL Server.

I've been asked to find all costs associated with a particular order.

The user will enter a request number and this request number is linked to only one order (the base order) which contains only the material cost. However, the labor and outside costs are in separate orders all of which contain some variation of the base number.

Allow me to provide an example. Request Number 8530 is linked to Order number 10875. The material costs for order 10875 is $1200. The Labor cost ($850) is in order G10875 and its request number is 8735. The outside costs ($400) is in order G10875-sb which has a request number of 9101.

The report is grouped by the Request Number of the base order. I am able to get the material cost from the base order with no problem, but need to also show the labor and outside costs, which are in separate orders. For example

REQUEST # ORDER # COST
8530 10875 $1200
G10875 $ 875
G10875-sb $ 400

Anyone have any ideas how I can accomplish this? Thanks for your assistance.

DLee

 

[lbass]

Please identify the tables and fields you are working with by using the convention {table.field}, and explain how the tables are linked (if there are more than one). How do you know which order numbers go with which request numbers? Is there a third field that they have in common?

-LB

 

[dleewms]

Good afternoon,

Primarily, I am working with the tblOrders table. tblOrders contains a separate record for all orders (base and derivative). Each order contains a foreign key for the Request number which is defined in the tblRequest table. This is a one (Request) to many (Work Orders) relationship. They are joined through an Inner Join. My fields are tblOrders.OrderNo, tblOrders.Cost and tblRequest.RequestNo.

I was thinking that I would have to create a formula which loops through each order number and strips it of all nonNumeric characters and group it by this "Number". Then place the actual order number and cost in the Details section. Make sense? I just need help writing the "For loop" and possibly the array that would go with it.

I'm open to any other suggestions.

Thanks,
DLee

 

[lbass]

If you simply link {tblRequest.RequestNo} to {tblOrders.RequestNo}, then why can't you simply place {tblOrders.cost} in the detail section and insert a sum on it at the RequestNo group level?

-LB

 

[dleewms]

If I summed the costs and grouped them on RequestNo, each order (base and it's derivatives) would be in its own group as the RequestNo for the base order is different from the RequestNo of the derivative orders.

Thanks anyway!

 

[lbass]

On what field do you then link the two tables?

-LB

 

[dleewms]

They are linked throught the RequestNo. All the records are returned. I only link to the Request table to display the base order's actual Request Number, and not just the key value which is stored in the Orders table. I don't need to see the request numbers of the derivative order numbers, though they are available if I need to see them.

In the example below, the first data line is for the base order. The second and third lines are the derivative orders. I would need to group all orders that contain 10875 somewhere in the Order number field together.

REQUEST # ORDER # COST
8530 10875 $1200
G10875 $ 875
G10875-sb $ 400

 

[lbass]

Are there more than one requestno fields in either of the tables? If there are, please show how each displays in your example. If you are linking on request #, as in 8530 above, then there is a match in both tables. I don't see why you wouldn't get the desired results as I suggested earlier.

-LB

 

[dleewms]

The previous suggestion doesn't work because I don't need a sum per request number. In the example above, perhaps it would have been better to display the other request numbers. I didn't because the user only wants to see the request number of the base order, which in the example above is OrderNO 10875.

Allow me to try to simplify.

I can create the report with just one table, the orders table. The report will contain three fields from the Orders table, RequestKey, OrderNo and Cost. I need to sum the costs that come from associated ORDERS. Associated orders have five numbers in common, but they also contain alpha and other non-numeric characters before and/or after these five numbers. I need to strip the OrderNo field of these non-numeric characters and group the associated orders by the common 5 numbers that they share.

Thanks again,
DLee

 

[lbass]

Okay, but on the surface, it seems unnecessary.

Are the base order numbers always 5 digits in length? When letters are added is there always only one letter preceding the numbers? When there is a suffix, is it always 3 characters in length? If these are not consistent, please show sample data that represents the variation that can occur in the field.

-LB

 

[dleewms]

Unfortunately, there doesn't seem to be very much consistency.

The base order nubmer can be up to 8 digits in length and may contain leading zero's. The OrderNo field has a length of 15.

The prefix is consistently one to four alpha characters in length.

Ninety percent of the time the suffix starts with a hyphen (-). However, the other 10% it doesn't have the hyphen, just the alpha characters.

Here are some examples order numbers. 000001015 is the base order number. The rest are its derivatives.

000001015
DLAP1015-GT
DLAP1015-INT
DLAP1015FAB
DLAP1015-CELL08
G1015
G1015-GT

Thank you!

 

[lbass]

Try:

stringvar x := {table.orderno};
stringvar array y := split(x,"-");
stringvar z := "";
numbervar i;
numbervar j := len(y[1]);
for i := 1 to j do(
if isnumeric(y[1]) then
z := z + y[1]
);
val(z)

-LB

 

[dleewms]

That did the trick. Thanks so much LB.

DLee