Hello, Using crystal 11 on a sql db.
I have to compare workorder to invoice details side by side by line type. ie Parts, Labor, Fees, Discounts. This information is stored in the same table defined by ordertype, so I had to alias table. Order_Invoice linked to Order_Work on Order_invoice_refid to Order_Work_id.
Every line type has a one to one relationship except for labor in the details no matter if it is a workorder or a invoice. The bugger is that for the labor, there can be multiple labor lines (one for each personnel)on a workorder but only one labor line on the invoice. ie in the workorder you would have three labor lines of 1 hour each. In the invoice one labor line of 3 hours.
Because of this, when I try to do a side by side comparison, I end up having duplicate details. I cannot sum/group by linetype (which would make it easier) because the client wants to see every part detail and every discount. I also am required to format this for excel export only.
Right now this is what I have:
Line WODesc WOqty WOCost WoTotal InvDes Invqty InvPrice InvTot
Part Light 3 .75 2.25 Light 3 4.25 12.75
Part Light 1 .10 .10 Light 1 .25 .25
Labor PM 1 25.00 25.00 PM 3 100.00 300.00
Labor PM 1 25.00 25.00 PM 3 100.00 300.00
Labor PM 1 25.00 25.00 PM 3 100.00 300.00
Then the part lines duplicate again for every additional labor line (
I need the result to be:
Part Light 3 .75 2.25 Light 3 4.25 12.75
Part Light 1 .10 .10 Light 1 .25 .25
Labor PM 3 25.00 75.00 PM 3 100.00 300.00
so that the customer can do a one on one profit/loss comparison in excel.
I need to sum/group the labor line type in the workorder, so it matches the invoice but the other line types must remain in detail. How can I do this?
Thank you
Julie
CRXI CE10 / RS2005 Sql DB
I have to compare workorder to invoice details side by side by line type. ie Parts, Labor, Fees, Discounts. This information is stored in the same table defined by ordertype, so I had to alias table. Order_Invoice linked to Order_Work on Order_invoice_refid to Order_Work_id.
Every line type has a one to one relationship except for labor in the details no matter if it is a workorder or a invoice. The bugger is that for the labor, there can be multiple labor lines (one for each personnel)on a workorder but only one labor line on the invoice. ie in the workorder you would have three labor lines of 1 hour each. In the invoice one labor line of 3 hours.
Because of this, when I try to do a side by side comparison, I end up having duplicate details. I cannot sum/group by linetype (which would make it easier) because the client wants to see every part detail and every discount. I also am required to format this for excel export only.
Right now this is what I have:
Line WODesc WOqty WOCost WoTotal InvDes Invqty InvPrice InvTot
Part Light 3 .75 2.25 Light 3 4.25 12.75
Part Light 1 .10 .10 Light 1 .25 .25
Labor PM 1 25.00 25.00 PM 3 100.00 300.00
Labor PM 1 25.00 25.00 PM 3 100.00 300.00
Labor PM 1 25.00 25.00 PM 3 100.00 300.00
Then the part lines duplicate again for every additional labor line (
I need the result to be:
Part Light 3 .75 2.25 Light 3 4.25 12.75
Part Light 1 .10 .10 Light 1 .25 .25
Labor PM 3 25.00 75.00 PM 3 100.00 300.00
so that the customer can do a one on one profit/loss comparison in excel.
I need to sum/group the labor line type in the workorder, so it matches the invoice but the other line types must remain in detail. How can I do this?
Thank you
Julie
CRXI CE10 / RS2005 Sql DB