x Want to participate? Join the group to interact
Options
  • Replies 9 replies
  • Subscribers 98 subscribers
  • Views 2593 views
  • Users 0 members are here
Browse Forums
Announcements
150
topics
113
replies
General Discussion
5k
topics
13k
replies
Payroll & Tax
631
topics
887
replies
Reporting and Analytics
573
topics
1k
replies
Service Alerts
1
topics
0
replies
Software Development Kit (SDK)
434
topics
671
replies
Year End
83
topics
128
replies
MBRussell

Inventory Adjustments Journal Field Import descriptions are incorrect

Posted By

Here is the field description for the "unit cost" field:
Numeric (Real) - Unit cost for the line of distribution; it gets ignored for negative inventory adjustments, but gets used for positive inventory adjustments. (Note: If this field is not included during import, it is calculated for you based on Quantity and Amount values.)

Here is the field description for the "amount" field:
Numeric (Real) – Amount for the line of distribution calculated during the import process. A positive amount denotes a debit, while a negative amount denotes a credit. This field is used for exporting only.

Here is the reality:
The unit cost field is not used by Sage when doing inventory adjustment imports.  If you use this field doing a import, the result will be that all of the negative inventory adjustments will have a negative value and quantity impact and all of the positive inventory adjustments will have no value while impacting the quantity.  This means you have an inventory adjustment with only debits (expense) to the P&L.  In order to properly do imports that have the desired financial impact, you need to use the amount field to value the overall transaction even though the instructions say the field is for export only.  Sage will calculate the unit cost for the transaction.  However, the "amount" field for quantity increases needs to be negative and not positive.  The positive amount being a debit means a debit to the P&L and not to inventory as you would expect, and a credit means a credit to the P&L and not to inventory.  So you will have a positive quantity adjustment and a negative in the "amount" field.  The instructions are misleading at best and backwards at worst.   

I have brought this up to Sage several times and they do not seem concerned about correcting their documentation.

  • Yes, we learned this years ago when importing Invty Adjustments from a CSV file. (thanks "StevenC")

    Positive Invty Adjustments must be entered as a Negative Value and include the "Unit Cost".  

    Trust you've found to Export a report first to make sure you have the right columns. 
    Ten columns to import:
    Item ID | Reference | Date | Job ID (blank) | Reason | Nbr of Items | GL Source | Unit Cost | Quantity | Amount

  • in reply to JPHart

    Thanks, and yes we have the columns to import.  We had to go though the inventory adjustments for a long time and adjust the inventory entry as we were basing our import on the instructions.  I finally sat on the phone with Tech Support for about 2 hours forcing them to use their sample company to figure out what was going on only to learn the missing piece about the negative amounts with our latest import.  I had searched the forums and no one had posted a warning, and felt that we needed to in order to save others the pain.  Also, Sage may get the idea that they should fix their help feature for their users.

  • in reply to MBRussell

    Yes, I feel your pain! 

  • I published a new knowledgebase article today, What fields are required to import the Inventory Adjustment Journal? In the cause section I mention the fields that need correcting, and I'm having our principal support analyst submit a defect to get the fields corrected in the Help topic. I agree with MBRussell, and I'm making it explicitly clear that the Amount field needs to be set to negative in all cases. For negative Qty adjustments, apparently the program knows to switch the credit to a debit based on the sign for the Qty field. Having the Amount field set to positive on import will cause your worst inventory footing nightmares to come true. Smirk 

    JPHart is incorrect, however. Grinning The Unit cost field is not required. While I did get differing (and funky) import results during testing for the test cases I had when the Amount was positive AND I had included the Unit Cost column, the program ignored the value of the Unit Cost field in all cases. It calculates unit cost on import based on qty and amount. 

  • in reply to John Parr

    Excellent, and thank you.

  • in reply to MBRussell

    You're welcome. Apologies that it took so long to get someone to take the proper initiative to get it changed. The reason it was never given priority is that we don't actually support importing for any reason other than rebuilding the company to change the structure of the fiscal year or change the accounting method. That being said, in my mind that makes it even more important that our documentation is complete and correct, for that very reason. 

  • in reply to John Parr

    I am surprised that Sage has such a limited expectation for importing.  I have over 8,000 SKUs in inventory and take quarterly physical inventories.  Importing is the most efficient way to record the 600+ inventory adjustments needed every 3 months.  We have company credit cards.  I have gotten our credit card company to produce a CSV file for the 250+ credit card transactions monthly that let me import the credit card activity.  I even use importing for to record my month-end journal entries closing the books.  We also have an on-line store and use importing to record those activities.  I would expect that there is a lot more importing going on, especially for larger companies, to record the activity going on outside of the Sage system.

  • in reply to MBRussell

    Just expressing a personal opinion here, and not as an official representative of the company on this matter, but I've always thought that our support for importing doesn't go far enough. I can certainly see it from the company's side too though.Once you go down that path it opens a whole can of worms. It takes support personnel with significant expertise to support importing on that level, and support is expensive. 

    We're beginning to start supporting stuff like designing custom financial statements, though, so maybe we're heading down that path with professional services, so never say never, I guess. 

  • in reply to John Parr

    Never say never...an expression I routinely use.

    I certainly get the problem with supporting it, but you can also alienate your user base by ignoring something that is routinely used.  My advice would be:

    1. Stress the backup requirement before performing an import.  The current method is pretty good.
    2. Add to the documentation that after completing the import to pull a journal (I look at the cash disbursement journal for the credit card import and the inventory adjustment journal for inventory adjustments) and look to see if the results are as expected so the restore can immediately be run if necessary.

*Community Hub is the new name for Sage City