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.