Skip to content
+1-888-319-3663

COMMUNITY FORUM

How to Import Standard Cost

john.ellis@tribridge.com asked 8 years ago
A SmartConnect 2013 user imported GP Inventory Transactions (Increase Adjustments) through an ODBC-based map.  None of the fields in the source or in the line item mapping reflect cost.  The items in GP are all Periodic (i.e. standard cost) items.  But, the user found a case today where an item's transaction was imported with Current Cost and not Standard Cost.  It would be great to know why this happened.  But, instead of "going down that road", is there a way of creating a Calculated Field for the "Add line item" mapping to always import an item's standard cost as the Unit Cost?  That is the expected behavior in GP, anyway, even if the inventory transaction were manually entered.  It's just that, for this one item's transaction today, that was not the case.  Thanks!  Much appreciated!     John
Lorren replied 8 years ago

John,

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.

Lorren

johnellis replied 8 years ago

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

Lorren replied 8 years ago

John,

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.

Lorren

johnellis replied 8 years ago

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

Lorren replied 8 years ago

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.

Lorren

johnellis replied 8 years ago

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? 

Lorren replied 8 years ago

John,

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.

johnellis replied 8 years ago

I get what you're saying, now, Lorren.  OK, then, I could update the script to say "where BACHNUMB = 'whatever'".  Correct?

Lorren replied 8 years ago

John,

That would work perfectly.

Lorren

Answers
Best Answer
johnellis answered 8 years ago
Thanks, Lorren!

If you would like to submit an answer or comment, please sign in to the eOne portal.