I am trying to modify a non-moving inventory BAQ that was created previously.
It checks to see if there has been any transactions for any inventory that is older than a specified date.
It works great except for ADJ transactions. If someone does any type of ADJ, it makes the report think that something has been done to the item when it really has not.
Does anyone know how to filter out the ADJ in my BAQ?
Right now I use a subquery that return the max(PartTran.TranNum).
I would think it would be some type of expression I could use instead that would pull the newest tranNum that is NOT a TranType of ADJ*.
In your subquery, just put a filter on the part tran table. You have to put that on in the design canvas of the tables, there is no way to do that in the calculated field (that I know of)
I misunderstood by what you meant when you said filter.
That was the 1st thing I tried. My calculated field only returns the max(TranNum) so if I filter it here, it totally removes the part from query.
Is there a way to feed this subquery with a table sorted by TranNum so it can filter that table to get the correct trantype?
(MAX(PartTran.TranNum) OVER(PARTITION BY PartTran.PartNum)) as [Calculated_MaxNOADJ],
(LAST_VALUE(PartTran.TranNum) OVER (PARTITION BY PartTran.PartNum ORDER BY ADJ.TranDate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) as [Calculated_LASTNOADJ]
What he said. You just want to filter out the tran type.
If you only want the date (a single field), you can do a kind of subquery that is shown in this thread. It’s a pretty handy way to get stuff like this.
The windowing functions go in your calculated field. An example would be
(MAX(PartTran.TranNum) OVER(PARTITION BY PartTran.PartNum))
The last part in @John_Mitchell’s example is in the full SQL, the calculated field name in the editor handles that for you.
However, I think that this is what you would need here without the windowing functions. You just need a list of part numbers with a last tran date right? You can filter the parttran table by tran type, then do a max(date) in your calculated fields. Do you need the part tran number? Or where you just using that in as a proxy for the last transaction?
I would like to see only 1 record per partNum.
I would like to see only the records that do not start with the TranType of ‘ADJ’.
I would like that record to be the most recent record for that partNum.
How would I go about that?
I think once I understand that, I can add in the other fields one at a time and make sure it doesn’t break that core.
Your tran type in that list is going to mess you up because if you group by that you are going to get the last of each company, Partnum, and TranType.
But if you want the last transaction that is by Company, Partnum, then you need to leave the trantype out of the list. You can still remove the adj tran types from the list to search from, but if you need the trantype you will need to rejoin that table to get it. Alternatively, you can use the max trannum instead of the date because if it will be better for rejoining to the table later.
So the first thing you’ll do is bring in the PartTran table and filter like so.
Also, this leads me to believe that you are just filtering on the wrong level. You need to filter the PartTran table in the subquery, not the subquery in the top level, and not in the subquery criteria.
I am told I need to have the TranType show also. How would you do that?
I am fine doing that max(tranNum) instead of the date if I can pick the date back up when I get the tranType.
And now you should get your list. Note: I have well over 10000 part numbers, so my list now is different, but if I pared them down correctly, I should get the same list that I posted before.
All that being said, Can you post your original BAQ that you say is working except for the ADJ transactions? I would bet money that it’s a small change to make the work the way you want. I can take a look at if for you.
I need to return a list of parts that ONLY HAVE the ADJ as a transaction. These are parts that were brought into the system when we started but have not been used at all since then.
Unchecking just returns the top ADJ for each part. Any ideas how to get the ADJ only parts?