3 table Report Structure - Guidance Needed

[lhendrickson]

I am working with Crystal report 9.

Background: I am trying to create a report that summarizes AP clerk entry. A major piece of this entry is invoice coding. The coding is first entered on the purchase order (MMDIST), upon entering an invoice the AP clerk may decide to change the coding or may enter additional items that were not included on the purchase order (APDISTRIB). All invoice header information is included in a table named APINVOICE.

Problem: APINVOICE holds the PO Number and the invoice number. MMDIST only holds the PO number and APDISTRIB only holds the invoice number. I need to pull all the coding records from MMDIST for each PO Line number ONLY TO BE OVERRIDDEN if the AP clerk changed the coding for that line item which would show up in APDISTRIB.

Initial Tries: I tried to include these three tables in a subreport (due to lots of other info being included on the main report). If I link this subreport to the main report by PO number, data in APDISTRIB will not populate (b/c it doesn't hold the PO number), If I link the subreport to the main report by Invoice number, data in MMDIST won't populate. Thought of making 2 separate sub reports, but then not sure how to handle a PO line that was coded in MMDIST then the coding was changed in APDISTRIB. In this case I want the coding for APDISTRIB.

I apologize for my wordiness, just trying to properly explain the situation.

Any guidance you guys could give would be appreciated!!!

lhendrickson

 

[Ngolem]

you haven't given a complete picture. When a clerk over-rides an invoice to change or add items, there must be a field that indicates the clerk has changed the P.O.

How are new items identified? How are original items shown to be modified? Where are these fields located if they exist.

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[lhendrickson]

The PO Coding remains in MMDIST. If the Accounts Payable clerk changes the coding on a line within a PO, the coding change is recorded in APDISTRIB only. Also if the AP Clerk has an item on the invoice that was not on the PO, the new item will be entered and will be given a line number of zero in APDISTRIB.

Example:
MMDIST
Line # Cost Ctr PO# Amt GL Coding Item Desc
line 1 10-9000 125 $10.00 56000 Widgets
line 2 10-9555 125 5.00 67000 Wire Rope
line 3 10-9439 125 25.30 75000 Med Supplies

APDISTRIB
Line # Cost Ctr Amt Inv# GL Coding Item Desc
line 0 10-9549 3.55 V6DF 56560 Shipping Charges
line 3 10-9439 25.30 V6DF 65000 <-changed coding

*Line 0 on APDISTRIB represents a line that was not already on the PO.
*Line 3 on APDISTRIB is the change made.

APINVOICE
Inv # PO# Vendor # PO Date
V6DF 125 45895 3/05/04

The original coding does not change in MMDIST. I would like to link MMDIST, APDISTRIB, & APINVOICE so I can write a formula to take the coding for each line from MMDIST unless it sees coding for that same line in APDISTRIB (then take the coding from APDISTRIB).

We are creating this report to provide the AP clerk data before she "Matches" the invoice so that she can verify her work. When the invoice is "Matched" all of this data comes together in the APDISTRIB table. There are system constraints that require this report prior to the invoice "Match" <- which is why I have the problem.

Let me know if more info is required.

Thanks for your time,

Lhendrickson