How to Import Standard Cost
You could write a Calculated Field using .NET script to pull the Item's Standard cost from IV00101 or you could create a SQL Lookup column to get the value from IV00101 so you aren't having to write .NET code.
Hi Lorren: I didn't think about using the MSSQL Lookup column. That's a good idea. A few moments ago, before I got this message with your brilliant idea, I used another approach that tested successfully. But, I want to get your opinion, just the same. I created a SmartConnect Task for "after the document succeeds" that uses the T-SQL script below. Does my reasoning seem sound? In other words, did I do the right thing? 🙂
update IV10001 set IV10001.UNITCOST = IV00101.STNDCOST from IV10001
inner join IV00101 on IV10001.ITEMNMBR = IV00101.ITEMNMBR
Your thinking is correct but are you sure you want to update every single inventory transaction that way? If not, you may want to restrict it to just the IV document being imported and have your task run on Document Success.
Hi Lorren: Yes, this task is only going to be in this one map that the user has a concern about. For transactions imported, through this map, the user always wants the standard cost for each line item. And, the task runs my T-SQL script only if the Document Succeeds. Does my logic seem sound? John
I would say that your logic will cause a problem because if there are other IV transactions, they will get updated with Standard Cost as well because you aren't restricting it to a specific IV Adjustment Transaction. Either restrict it to the specific transaction you just created or use the SQL Lookup in the map itself.
I may not be explaining this well. The client wants to pull the Standard Cost from Item Maintenance no matter what the item is, in this one Inventory Transactions map. As long as the map does that, that will work for the client. What am I missing?
You are explaining it just fine.
What you are missing is that your SQL Statement will update EVERY inventory transaction that is not posted but is sitting in the inventory transaction work table.
If I create an inventory adjustment and save it to a batch and then you run this map, your SQL statement will change the Unit Cost of my inventory transaction.
You need to restrict which inventory transaction is being updated by your SQL statement.
I get what you're saying, now, Lorren. OK, then, I could update the script to say "where BACHNUMB = 'whatever'". Correct?
That would work perfectly.