On the Items form, Sales tab, there are values for Purchased YTD, Manufactured YTD, Used YTD, and Sold YTD. These are great, but we would also like to see the previous year to date values so that we can compare them. Has anyone else done this?
So this is what I got to work for anyone else looking to do this!:
(SELECT SUM(IsNull(mat.qty, 0)) FROM matltran mat WHERE mat.item = Item AND mat.trans_type='R' AND mat.ref_type='P' AND mat.trans_date BETWEEN '2020/01/01' AND '2020/12/31')
I have done it for a 5 year look back. If you have the data in the system already, it could be as easy as just creating some additional derived IDO Properties that sum the various transaction tables for the desired date ranges. If you have to import the data from another system, then that is where it gets ugly, because then you have to provide a mechanism to import and maintain the data, as well as sourcing the counts from historical tables and the active transactional tables.
I just want for previous year (2020). I am Saas, so I don't have access to the SQL tables which makes it hard to know exactly what tables I need to look at. For items, I want to see specifically Purchased LAST YEAR and Manufactured LAST YEAR. Any direction you can provide is appreciated.
I can look at my notes and see if I can offer any suggestions. You know you can download the installer and setup a local Hyper-V or VirtualBox instance and have access to the same data structure using SSMS hat exists in the cloud for development reference correct?
Still working on this. I have added a derived field to my IDO (extended SLItems). The expression I am using for the field is:
ISNULL(SELECT TOP 1 SUM(IsNull(poi.qty_received,0)) FROM poitem poi JOIN item itm ON itm.item = poi.item WHERE poi.rcvd_date BETWEEN '2020/01/01' AND '2020/12/31'),0)
When I try to run the Items form now, I get an error "Incorrect syntax near the keyword 'SELECT'. Anyone see what I may have wrong?
Check your groupings - parenthesis have to match up you have 3 ( and 4 ). good luck.
Too many ) at the end.
ISNULL(SELECT TOP 1 SUM(IsNull(poi.qty_received,0))
FROM poitem poi
JOIN item itm ON itm.item = poi.item
WHERE poi.rcvd_date BETWEEN '2020/01/01' AND '2020/12/31'), 0)
Try this instead. You needed an additional "(" after ISNULL.
ISNULL((SELECT TOP 1 SUM(IsNull(poi.qty_received,0)) FROM poitem poi JOIN item itm ON itm.item = poi.item WHERE poi.rcvd_date BETWEEN '2020/01/01' AND '2020/12/31' ),0)
Thank you all for the feedback... I have made progress, but now I get a really large number which is the same for every item. I think it is sum'ing all PO receipts and not just the one for the item record I am on. What am I missing?ISNULL((SELECT TOP 1 SUM(IsNull(poi.qty_received,0))FROM poitem poiJOIN item itm ON itm.item = poi.itemWHERE poi.item = Item AND poi.rcvd_date BETWEEN '2020/01/01' AND '2020/12/31'),0)
Just another thought on this... Where this is for analysis, would you not be better off creating a DataView where you have more control over aggregate functions and can use parameters to limit what items are being analyzed? Also would allow users to export and print the data.
I would also do it based on Material Transactions and not SLPoitems. You may find you need to move PO data to history at some point and that could break your derived fields based on SLPoitems, because your data would be split between SLPoitems and SLPoitemhs.
My goal is to have a tab on the Items form where this information is readily available per Item. I do want to look at Material Transactions for manufactured items, so if I could get the PO information there too, then this may be a better solution. I will look at mattrans.
This is for Purchase Order (=P) Receipts (=R)
Thank you!
Thank you for sharing this
Thanks everyone. This is what the final project looks like:
Anyone who wants to do something like this and needs help, just message me.
Thanks for sharing, I noted and message later
Thank you so much
I love this - thank you for sharing!
This is very helpful. Thank you for sharing!
Would you be willing to share the form or IDO code you used to create this - we now have a project in place to recreate this same thing for each year.
I updated these a while back to make them relative so I didn't have to manually change the dates each year. If you want to do this, you can use something like this (previous Year): (SELECT SUM(IsNull(mat.qty, 0)) FROM matltran mat WHERE mat.item = Item AND mat.trans_type='R' AND mat.ref_type='P' AND DATEPART(YYYY,mat.trans_date) = DATEPART(YYYY,DATEADD(year,-1,GETDATE())))
