BAQ to select PartTran records for a Part and any Part that it is an alternate for

Trying to make a dashboard to display part transactions (like the Part Tran History, but without the running sum), for a user selected part, and any other part that has the user entered part number as its substitute.

For example

  • Part XYZPDQ was replaced by RM-1234
  • RM-1234 was entered as an alternate for XYZPDQ
  • There was a time period where both Parts were used

If user enters RM-1234 as the parameter input, they should see:

TranNum  TranDate    PartNum      TranType  TranQty   Job
98765    07/11/2018  RM-1234      STK-MTL   50        4001
98760    07/05/2018  XYZPDQ       STK-MTL   15        3991
98660    07/01/2018  RM-1234      PUR-STK   1000
98600    06/20/2018  XYZPDQ       STK-MTL   25        3980
98500    06/10/2018  XYZPDQ       STK-MTL   20        3970
98432    06/01/2018  XYZPDQ       PUR-STK   1000

I know the PartSub table holds the alternate parts. But I can’t figure out how to get parttran records based on:

  (PartTran.PartNum = Part.PartNum AND Part.PartNum = UserParam)
    OR (PartTran.PartNum = PartSub.PartNum AND PartSub.SubPart = UserParam)

I was able to get the reults by using a subquery setup as a union. But that always puts unioned results at the end. Setting the Sort order in the BAQ doesn’t seem to mix the main and sub query.

Why don’t you do a couple of CTE queries with a Union?

Mostly because I’m unexperienced with CTE’s.

Plus, I got it working with just a Union and no CTE’s :slight_smile:

1 Like