BAQ Record selection help

Yahoo Archive
1
>>tried that and it gives me 16 line...
I've run into this same problem with invoices & the misc tables.
When using only a BAQ, I never found a way to avoid record multiplication.
I always ended up handling all the duplication in Crystal reports. Not pretty but it worked.

I haven't done AP AR invoices in SQL but I think you could get close with Common Expression Tables.
or worst case using subviews?
2

I'm trying to write a report based on AP Invoice info, and I'm struggling to get a BAQ or SQL query to get all the records.


The problem arises from the fact the the APInv records are in unrelatable tables. There's the APInvHed, APInvDtl, and APInvMisc.  I'll ignore the APInvTax. records for now (as I could have the AP entry folks do them as Misc lines).


Every APInvDtl belongs to an APInvHed.  That's a simple inner join.

Every APInvMisc belongs to an APInvHed, but some also belong to an APInvDtl.  Misc charges that apply to the whole invoice have an InvoiceLine = 0. Misc charges that are specific to a invoice line have the APInvMisc.InvoiceLine = APInvDtl.InvoiceLine.

 

An Example Invoice 54321

Line 1, Part xyx, $123, no misc charges

Line 2, Part pdq, $234, with 1 misc charge (Expdite, $100)

Line 3, Part abc, $345, with 2 misc charges(Expedite $100, Setup $75)

Line 4, Part lmn, $456, no misc charges

Invoice misc charge. (Fuel Surcharge $66)


The above would make (1) APInvHed record, 

(4) APInvDtl records, (APInvDtl.InvoicesLine: 1, 2, 3, 4)

(4) APInvMisc records, (APInvMisc.InvoiceLine: 0,2,3,3)


Here's another look inside the Misc table

InvLine MiscNum  Item ...

  0       1      Fuel Surcharge

  2       1      Expedite

  3       1      Expedite

  3       2      Setup 

(note no entries for InvDtl lines 1 or 4, and there is no APInvDtl with line 0)


I'd like to get 

Line  MiscNum Item           DtlAmt   Misc Amt

  0      1    Fuel Surcharge           $66

  1           XYZ             $123

  2           PDQ             $234

  2      1    Expedite                 $100

  3           ABC             $345

  3      1    Expedite                 $100

  3      2    Setup                    $75

  4           LMN             $456


I'm actually doing this in CR, so I've got even more options for table joins.


I thought a Left outer join Hed->Dtl and another left outer join of Hed->MIsc would work, but it doesn't.


Any help would be appreciated.


Calvin

3
I haven't tried this but I would think you could do an inner join from APInvHed -> APInvDtl

Then just a left-outer APInvHed -> APInvMsc joining only by company and invoicenum would give you all associated misc charges to the invoice which you could then group however you want in crystal to put under the correct line?


4
I tried that and it gives me 16 lines - (4) of each Dtl Lines 1-4 , each having a (4) Misc Record (0.1, 2.1, 3.1, and 3.2)*

* that's a combination of Misc.InvLine, and Misc.MscNum - just for reference here.  Not actually combined in the query.

DtlLine  MiscLine MiscNum 
  1                          0                 1
  1                          2                 1 
  1                          3                 1
  1                          3                 2
  2                          0                 1
  2                          2                 1 
  2                          3                 1
  2                          3                 2
  3                          0                 1
  3                          2                 1 
  3                          3                 1
  3                          3                 2
  3                          0                 1
  3                          2                 1 
  3                          3                 1
  3                          3                 2
  4                          0                 1
  4                          2                 1 
  4                          3                 1
  4                          3                 2


I can supress lines where the DtlLine <> Misc line, but that also suppresses DtlLine 1 &4 which have no associated misc line.  It also supresses MiscLine 0 which has no associated DtlLine.