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)
Can you explain a bit on how to put a filter on the subquery? Not sure I am getting it.
Thanks,
Shawn
Sorry,
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?
Not sure I made sense, lol.
You can useWindow Functions as Calculated Fields in the BAQ.
(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]
1 Like
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.
Hello @John_Mitchell,
I haven’t ran across windowing before.
Can you explain where this query goes? In my CalcDate subquery or the main query?
I appreciate you giving this information as it is something new to learn.
Thanks,
Shawn
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?
edit this would also work the tran number too. Just change the field type to int instead of date.
Hey @Banderson,
I am just not getting it.
Lets simplify it to get the concept across.
Let’s say I only want these fields right now.
PartTran.Company,
PartTran.PartNum,
PartTran. TranDate,
PartTran.TranType
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.
Any help is truly appreciated.
Thank you,
Shawn
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.
Then add company and part number, check the group by check boxes.
Then make a calculated field with the max(trandate)
When you test you should get a list of part numbers with the last tran date.
This will be your whole subquery. I wouldn’t add anything else to this, just use it like a table on your top level to get the information.
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.
So change your calc field to this.
I would change your formatting to this, since no-one expects commas in a tran number
Then you will end up with a list like this.
Make this part a subquery
Make a new top level
Bring in the subquery and bring in part tran again and join them like this.
Add whatever fields you want.
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.
Go to your PartTranFields subquery and add this criteria.
EDIT scratch that, you have to go another level down. Although this is giving me a timeout error.
AND FINAL EDIT
You need the criteria on both levels. That’s stops the time out issue since it’s not trying to match so much.
Sorry I haven’t posted in here for a couple of days.
I did a redesign that is based on what you have shown me and it looks like it is working the way they want for the most part.
Now that I have fufilled the requirements, they have changed them, lol.
They asked if I can also report the parts that have ONLY ADJ in their parts history.
I told them I can not do it on this report but I may be able to do it on a separate report.
So that is my next assignment. I have to think about it before I start.
I may have to open a new post on it as a separate issue if needed.
I will mark your post as the answer for me.
Thank you again for all your help. It is above and beyond what I hoped for online.
Shawn
Just copy your BAQ and uncheck the not in the filter. Them you will get only adj records. Easy Peasy!
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?
ohh, that’s different. I would approach that by doing a count of records. You can count the adj records, and you can count the not adj records. (this would be 2 subqueries). Then you can join them (outer join showing all of them with adj records) and the ones that come back null on the not adj records would be the ones that you are looking for.
If you want to just add that to the one that you have, try the mini-sub method that I linked to previosly. You should be able to get a count of the non-adj records and return them on your top level. Anything with a 0 is adj-only.