x Want to participate? Join the group to interact
Options
  • State Not Answered
  • Replies 6 replies
  • Subscribers 113 subscribers
  • Views 2541 views
  • Users 0 members are here
Browse Forums
Announcements
293
topics
370
replies
Business Object Interface
1k
topics
7k
replies
Core Financial Modules
2k
topics
8k
replies
Customization and Productivity
3k
topics
12k
replies
Distribution, Manufacturing, and Applications
2k
topics
7k
replies
General Discussion
1k
topics
3k
replies
Partner Cloud
45
topics
92
replies
Payroll, Time Tracking, and Job Cost
777
topics
1k
replies
Reporting and Analytics
962
topics
3k
replies
Sage CRM for Sage 100
164
topics
357
replies
Technical and Installation
2k
topics
9k
replies
Year-end Forum
89
topics
237
replies
Sean Gucken

Connecting SO_OrderTierDist to SO_InvoiceTierDist in Crystal

Posted By

Has anyone had any luck connecting these two tables together? I am trying to create a daily sales report that has profit based on the lot number. The problem I am running into is the OrderTierDist clears when the sales order is being invoiced and pushes the lot info into the InvoiceTierDist and we frequently get and order, ship, and invoice in the same day. I have tried doing a left outer join from the SO history tables to the invoice tables but as soon as I add any field from the invoice tables, not data populates.

  • 0
    Have you tried the A/R history files and not the ones from S/O?
  • 0 in reply to BShockley
    The AR History tables do not have any of the data I need. I need all Sales Orders (open and currently being invoiced) and I need the TierDist from the SO invoice tier distribution table. At the time of report run, the SO will be invoiced but the batch will not be updated so the AR History table will not have anything I need.
  • 0 in reply to Sean Gucken
    When I have difficulty linking tables, I often just drop a sub-report onto the line, and pass appropriate parameters so the results are what I want to see. No LoJ complications because blanks from the sub-report don't affect the main report.
  • 0 in reply to Kevin M
    Are you trying to get profit item by item or by invoice.order total?

    If invoice/order, I believe that the header record has the sales and cost of sales totals (or at least they do for an invoice, maybe not for an order - not sure).
  • 0

    You can only do this if you distribute the Lot/Serial items in Sales Order Entry otherwise it is not possible because cost is not determined until the sales order is shipped/invoiced and the Lot/Serial Number is selected.

  • 0 in reply to BigLouie

    The lot is distributed at SO entry, however when the sales order gets invoiced, the SO Lot gets cleared from the SO table and put into the SO Invoice table. Below is what I am trying to do:

    1. I group by Sales Order
    2. Sum the extended price on each item in the SO_SalesOrderHistoryDetail table
    3. I sum the extended cost of each item in the TierDist and add an additional cost based on the lot (we give back a percentage of the sold price to the vendor)
    4. I subtract the summed extended price from the extended cost to get the profit.
    5. I don't really care much about profit on each item, or even on each order, just the final number.
    6. My issue arises when an order that is entered and invoiced at the same time, the SO_OrderTierDist gets cleared when the SO gets invoiced (even though the invoice batch isn't updated).

*Community Hub is the new name for Sage City