Does EM record who allocates a SKU item?
I don't see a field for it in the Stock_SKU_Item_Allocations table.
(FYI, I'm back to trying to create a report for who allocated what SKU to which job)
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
That table wouldn't have exactly what you want anyway, you only want manually allocated stuff right?
You would need to pull all items from "PO Item Details" where [Transfer Flag] = 2 or 4.
For your information, here are the valid values for that field:
Const $EM_PO_Item_Allocation_Type_Original = 0
Const $EM_PO_Item_Allocation_Type_AssemblyTransfer = 1
Const $EM_PO_Item_Allocation_Type_ManualTransfer = 2
Const $EM_PO_Item_Allocation_Type_OnhandAdjust = 3
Const $EM_PO_Item_Allocation_Type_StockAllocationToJob = 4
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hm, I don't see the PO_Item_Details table. Is it listed in the Enterprise Manager>dbo>Tables? (I bought "Crystal Reports 10 For Dummies" book yesterday. I shall not be conquered!)
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Do you mind if I email you a report? (or post it up here if that works) It only includes records from 12/29/1211 through 2/17/2012 and I can't figure out why.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I changed the record selection and I get (see attached) Did I miss a step?
Also, I'm not quite sure which of the tables you want me to delete? PO_item_Allocation_Transfers or Purchase_Order_Item_Allocations? (well, I guess that it can't be PO_item_Allocation_Transfers since your instructions include linking it)(did you mean for me to delete any tables?)
You don't need [PO Item Allocations], all of the same data from it is in included in the view [PO Item Details].
Note the link between [PO Item Allocations Transfers] and [PO Item Details] in on [PO Item Allocations Transfers].[New Allocation ID] = [PO Item Details].[Allocation ID]. You had a link to [PO Item Details].[ID], which is the ID of the purchase order item (not the allocation of it).
I attached the modified report. You will need to go up to Database->Set Datasource location and point the database to yours instead of mine.
Done, thank you!
I am going to spend a little more time on this looking at my first try and yours to see if I can figure out WHY yours works. I've got a feeling that my 2 biggest problems are not knowing the reasoning behind your setup (location of fields and their purpose) and how Links work. (I think I need to buy the "Database Development For Dummies" book)
Are your other shops able to figure this stuff out? Is it just a lack of database experience on my part?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Well, I reeeeaaaallllly apriaciate what a learning experience this is and the time you're putting in to help me learn. If you come to Orlando sometime let me know. Dinner's on me!
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Ok, I'm back on this. I'm getting a crash whenever I run the report. CR runs the report fine from my PC looking to the report on the EM server. I'd post the crash notes but EM is still generating a crash report. I think it's first checking the status of the entire internet before finishing :)
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Does EM record who allocates a SKU item?
I don't see a field for it in the Stock_SKU_Item_Allocations table.
(FYI, I'm back to trying to create a report for who allocated what SKU to which job)
That table wouldn't have exactly what you want anyway, you only want manually allocated stuff right?
You would need to pull all items from "PO Item Details" where [Transfer Flag] = 2 or 4.
For your information, here are the valid values for that field:
Const $EM_PO_Item_Allocation_Type_Original = 0
Const $EM_PO_Item_Allocation_Type_AssemblyTransfer = 1
Const $EM_PO_Item_Allocation_Type_ManualTransfer = 2
Const $EM_PO_Item_Allocation_Type_OnhandAdjust = 3
Const $EM_PO_Item_Allocation_Type_StockAllocationToJob = 4
Hm, I don't see the PO_Item_Details table. Is it listed in the Enterprise Manager>dbo>Tables? (I bought "Crystal Reports 10 For Dummies" book yesterday. I shall not be conquered!)
It is a view, it should work just like a table.
Enterprise Manager>dbo>Views probably.
Next Question: What field in which table can I find the internal description for a SKU item?
Nevermind, found it!
Do you mind if I email you a report? (or post it up here if that works) It only includes records from 12/29/1211 through 2/17/2012 and I can't figure out why.
You can do either.
Here it is. Thanks alot man.
Last edit: Paul 2012-08-27
OK, first change your record selection query to:
{PO_Item_Allocation_Transfers.Qty Transfered} > 0 and
{PO_Item_Details.Transfer Flag} in [2, 4]
As it is, order of operations means the first "or" gets evaluated between the first two terms, then and AND with the third.
Next, you don't need PO Item Allocations in your database query at all, so remove it. Link like this:
[PO Item Allocations Transfers] LEFT JOIN USERS
[PO Item Allocations Transfers] LEFT JOIN [PO ITEM DETAILS]
[PO ITEM DETAILS] LEFT JOIN [JOBS]
To set the join direction right click the arrow and go to link properties.
Now set your record sort to sort on [PO ITEM ALLOCATION TRANSFERS].[TRANSFER DATE CREATED] descending.
I changed the record selection and I get (see attached) Did I miss a step?
Also, I'm not quite sure which of the tables you want me to delete? PO_item_Allocation_Transfers or Purchase_Order_Item_Allocations? (well, I guess that it can't be PO_item_Allocation_Transfers since your instructions include linking it)(did you mean for me to delete any tables?)
Last edit: Paul 2012-08-29
You don't need [PO Item Allocations], all of the same data from it is in included in the view [PO Item Details].
Note the link between [PO Item Allocations Transfers] and [PO Item Details] in on [PO Item Allocations Transfers].[New Allocation ID] = [PO Item Details].[Allocation ID]. You had a link to [PO Item Details].[ID], which is the ID of the purchase order item (not the allocation of it).
I attached the modified report. You will need to go up to Database->Set Datasource location and point the database to yours instead of mine.
Done, thank you!
I am going to spend a little more time on this looking at my first try and yours to see if I can figure out WHY yours works. I've got a feeling that my 2 biggest problems are not knowing the reasoning behind your setup (location of fields and their purpose) and how Links work. (I think I need to buy the "Database Development For Dummies" book)
Are your other shops able to figure this stuff out? Is it just a lack of database experience on my part?
{Paul's inner monolog as I look at your report}
"Dang it! Why didn't I try brackets around 2, 4 in the formula editor? I tried parenthesis. Doh"
The only ones doing any report work (I think) are the Cohens, and they hire comp sci students from the local college to do IT work for them.
Well, I reeeeaaaallllly apriaciate what a learning experience this is and the time you're putting in to help me learn. If you come to Orlando sometime let me know. Dinner's on me!
No problem.
I added a date range selector if anyone wants it.
You know, this was also added EM: when you click on a SKU consumption item in Jobs or Finance, it will give you a list of who allocated what/when/why.
Yes, thank you. What it doesn't do but my report does is answer the question "What job did I just allocate material to?"
Hm, this report crashes EM now. I'll have to mess with it later... Got to get a job out to the saw.
Ok, I'm back on this. I'm getting a crash whenever I run the report. CR runs the report fine from my PC looking to the report on the EM server. I'd post the crash notes but EM is still generating a crash report. I think it's first checking the status of the entire internet before finishing :)
Did you try this with the newest build? If it still crashes, can you post the rpt file?
Assuming it has changed from what you posted above, I mean.