Duplicate Data from same datatable

[bstafford21]

I am using Crystal 9 and built a report for my Sales Dept.

I have a database that holds my SO (Sales Order) numbers and another database that holds the WO (Work Order) numbers and a 3rd database that holds the Part numbers and then I have a 4th that has the Material Component numbers.

My report works as it should and gives me the information needed and is correct, however the problem is:

1. Material Component database for some Parts have more than one (1) material. When this occurs I get the SO for that part listed twice so the information is double in alot of the cases.

How do I write the formula to suppress or omit the second Material Component that shows up. This item is not needed and I only need the information for the 1st main Material Component only that comes up.

Any help would be greatly appreciated.

Thanks
Bill

 

[synapsevampire]

Well the information isn't doubled, you'll find that soemthing differs in each row, it's commonly termed record or row inflation.

So there are 2 different materials for each sales order.

You could group by the sales order (from the main menu, Insert->Group), then place your fields in the sales order group footer, and suppress the details.

So you'll get the first material in the database for that sales order.

-k

 

[bstafford21]

Hi,

I can show you a better example so the idea for this report is as follows:

Due Date SO # Part # Material Used
22/09/2006 12345 111-0011 98765432
22/09/2006 12345 111-0011 12345678


What I need is to suppress the 2nd line because it is the same as the 1st line. This part 111-0011 is made of 2 materials 1.(98765432) & 2.(12345678), but there is not need for the 2nd material to be added in or even shown for the total Sales.

There is other columns for sales price & cost Price & Qty Delivered & Customer Order Qty.

How do I filter out and suppress it so the Sales Price and Qty and Cost are not added up again in the total of the report. I have aan Insert Summary and the amounts are wrong because it is adding in the 2nd line.

I hope that makes more sense and we are talking the same idea then.

Thanks
Bill

 

[lbass]

You could concatenate the three fields:

{table.date} & {SO} & {PartNo}

Then you could go to the section expert->details->suppress ->x+2 and enter:

{@concat} = previous({@concat}

However, the suppressed records will still contribute to your totals, so you will need to use running totals where you summarize a field, evaluate on change of field: {@concat}, reset never or on change of group depending upon your needs.

Alternatively, you also might considering the materials field from the report and then use "select distinct
records".

-LB

 

[bstafford21]

Hi LB,

I understand almost all you have here, except the concatenate, I have never used this option and do not understand what it is.

However if the totals will still add in, then that option would not work for me.

I have used Running Totals and understand that and can use this option.

What was the Alternative you are thinking of concerning the Materials Field and using Select Distinct Records?

Thanks

Bill

 

[lbass]

I left out a word, I meant to say,"Alternatively, consider removing the materials field and then go to database->select distinct record.

I meant for you to create a new formula called {@concat} and then you could use this to suppress records you don't want displayed and also use it in the evaluation section of your running totals.

-LB

 

[bstafford21]

I have removed the Materials Database now.

Went to Select Distinct Record and it is grayed out and cannot select this option.

How do I get this option to work?

Bill

 

[lbass]

I think it's grayed out for stored procedures or maybe if you are using a command. But regardless, what happens with the records with the table/field removed? Does it eliminate the extra records?

-LB

 

[bstafford21]

Hi LB,

The extra duplicate records are removed when I take out the Components.dta table. This I knew would happen and the reason is that the Components.dta is related and linked to the Part.dta so if I am pulling up data with Part & Components in my report, I will always get double information when the Part has more than 1 component attached to it. That is how the part is made in Production alot of times with 2 material components.

So what I need is a way to remove the 2nd line and I am not understanding how to do the method you have suggested to me.

" You could concatenate the three fields:"

{table.date} & {SO} & {PartNo}

Then you could go to the section expert->details
->suppress ->x+2 and enter:

{@concat} = previous({@concat}


If this will still add in the cost and sales price to this suppressed component it will not work for the purpose of my report.

Another way I can do this is still to allow the 2nd or 3rd or... to be listed with the Part.dta table would be to eliminate the Cost Price and Sales Price of the extra components.

This Cost & Sales Price is the Part.dta database Prices and has nothing to do with the Components themselves, the components are listed just telling what the Part.dta is made of, and the Prices are for the actual part.

So because all components are listed I get duplicate Prices and need to eliminate any duplicate Prices that come up for that part. Maybe this would be a better route to take.

I hope this more clearly explains what I am trying to accomplish.

Thanks for your help LB.

Bill

 

[lbass]

You need to use both elements of my suggestion together--first, the conditional section suppression, and second, the running total that uses the formula {@concat}.

-LB

 

[bstafford21]

Hi LB,

Finished the report and used running totals and conditional section suppression.

Thanks for the help again.

Bill