×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Need help with setting a value to 0

Need help with setting a value to 0

Need help with setting a value to 0

(OP)
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.

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close