BradCustom
IS-IT--Management
Thanks for the help!
I'm using Crytal Reports 2016 and I'm trying to set the value of a field to zero if the sum of FG_Trans_Qty doesn't equal the Invoiced quantity. The trouble I'm having is there are many variations in the shipping records. Most times there is one record for each invoice quantity. However, there a couple of instances where there are multiple lines that makeup the invoiced quantity.
The report structure is as follows:
Groups
Customer
Invoice_Detail
Invoice_Detail_Line
Tables
Customer
Delivery
Invoice_Detail
Invoice_Header
Job
Material
Material_Trans
Packlist_Detail
Table Links
Invoice_Header > Invoice_Detail {Document}
Invoice_Header > Customer {Customer}
Invoice_Detail > Material_Trans {SO_Detail}
Invoive_Detail > Delivery {Invoice}
Invoice_Detail > Material {Material}
Material_Trans > Delivery {SO_Detail}
Material_Trans > Job {Job}
Delivery > Packlist_Detail {Packlist}
Delivery > Packlist_detail {SO_Detail}
To find the revenue for each line I use the following formula @LineRevenueTotal
This formula works for Case 2 but not for Case 1 because in Case 2 the Shipped quantity is equal to one of the invoiced lines. In Case 1 none of the lines equal the shipped quantity however, the sum of the shipped lines equals the Invoiced quantity.
I've tried changing the @LineRevenueTotal to evaluate the Sum of Shipped Quantity but Crystal Reports throws an error. Below is one formula I tried but the result is "A summary has been specified on a non-recurring field" Details; @LineRevenueTotal. Below is the formula that causes this error.
@MatTransReverse
ABS ({Material_Trans.Quantity})
Thanks for you help!
Please let me know if you need more information.
I'm using Crytal Reports 2016 and I'm trying to set the value of a field to zero if the sum of FG_Trans_Qty doesn't equal the Invoiced quantity. The trouble I'm having is there are many variations in the shipping records. Most times there is one record for each invoice quantity. However, there a couple of instances where there are multiple lines that makeup the invoiced quantity.
Code:
Case 1. Invoiced_Qty = 5 Shipped Qty = 5
FG_Trans_Qty = 1
FG_Trans_Qty = 1
FG_Trans_Qty = 1
FG_Trans_Qty = 1
FG_Trans_Qty = 1 (Sum =5)
Case 2. Invoiced_Qty = 4 Shipped Qty = 4
FG_Trans_Qty = 1
FG_Trans_Qty = 4 (Sum = 5)
This is the example where the FG_Trans_Qty for the line with 1 should be set to 0. The reason two lines are showing in this example is because the SO_Detail is the same.
This is because 4 pieces were shipped on one invoice and 1 on another invoice.The report structure is as follows:
Groups
Customer
Invoice_Detail
Invoice_Detail_Line
Tables
Customer
Delivery
Invoice_Detail
Invoice_Header
Job
Material
Material_Trans
Packlist_Detail
Table Links
Invoice_Header > Invoice_Detail {Document}
Invoice_Header > Customer {Customer}
Invoice_Detail > Material_Trans {SO_Detail}
Invoive_Detail > Delivery {Invoice}
Invoice_Detail > Material {Material}
Material_Trans > Delivery {SO_Detail}
Material_Trans > Job {Job}
Delivery > Packlist_Detail {Packlist}
Delivery > Packlist_detail {SO_Detail}
To find the revenue for each line I use the following formula @LineRevenueTotal
Code:
if isnull ({Material_Trans.Quantity}) then {Invoice_Detail.Amount}
else
if {@MatTransReverse}<>{Delivery.Shipped_Quantity} then 0
else
{Invoice_Detail.Unit_Price}*({Material_Trans.Quantity}*-1)This formula works for Case 2 but not for Case 1 because in Case 2 the Shipped quantity is equal to one of the invoiced lines. In Case 1 none of the lines equal the shipped quantity however, the sum of the shipped lines equals the Invoiced quantity.
I've tried changing the @LineRevenueTotal to evaluate the Sum of Shipped Quantity but Crystal Reports throws an error. Below is one formula I tried but the result is "A summary has been specified on a non-recurring field" Details; @LineRevenueTotal. Below is the formula that causes this error.
Code:
if isnull ({Material_Trans.Quantity}) then {Invoice_Detail.Amount}
else
if Sum ({@MatTransReverse}, {Invoice_Detail.Document_Line})<>{Invoice_Detail.Quantity} then 0
else
{Invoice_Detail.Unit_Price}*({Material_Trans.Quantity}*-1)@MatTransReverse
ABS ({Material_Trans.Quantity})
Thanks for you help!
Please let me know if you need more information.