315
We have a large number of items in our inventory, thousands actually.
When completing a physical inventory, is there a way to import the corrected quantities of physical inventory lines into the calculated inventory from an excel spreadsheet before posting?
*This post is locked for comments
You can create a Dataport to import into Physical Inventory Journal.
DataItem Name
Item Journal Line <Item Journal Line>
*********************************************
Dataport - OnInitDataport()
//starting line no
lineno:=10000;
// find last used Document No.
no_series.RESET;
no_series.SETRANGE("Series Code",'IJNL-PHYS');
IF no_series.FIND('-') THEN
EVALUATE(docno,no_series."Last No. Used");
// create next Document No.
docno:=docno+1;
*********************************************
Item Journal Line - OnBeforeImportRecord()
"Journal Template Name":='PHYS. INVE';
"Journal Batch Name":='DEFAULT';
"Document No." := FORMAT(docno);
"Posting Date":=TODAY;
//chose as Positive Inventory (2)
"Entry Type":=2;
"Line No.":=lineno;
*********************************************
Item Journal Line - OnAfterImportRecord()
VALIDATE("Item No.","Item No.");
VALIDATE(Quantity,Quantity);
lineno:=lineno+10000;
My data file is a text document <Item No.><TAB><Quantity> something like this:
77756 25
32567 5
14526 24
Rapidstart is a great migration tool and yes I have also used rapidstart to import items into a revaluation journal to fix missing cost prices. But it is not something I would recommend common users to do nor do I expect this to be the way to fulfill this righter simple business requirement.
MS already provided rapidstsrt for importing data
Sadly enough Microsoft have never considered it important to make just a simple import option. With a small effort Microsoft could have made a simple csv import with an option to either update to already calculated lines or add additional lines to a physical item journal. The old Navision design pattern was only to fulfill 80% of business requirements and leave the rest 20% for partners or resellers to add to the customers solution.
To solve this problem I realized that to import into table 83 you can choose to import two ways depending upon the "item journal template" that you use. I found out that I get the "Yes"/"No" flag error when I try to import into the "phys. inventory journal" template, but not the "inventory adjustments" template.
What I did was a long but successful process to run a "physical inventory" process that showed the computed stock on hand, take those amounts to excel, compare them using a vlookup function to the inventory that was done as a hand count and get a return valueof there was a difference between what was counted and what was computed, then use that difference as a rapidstart import to table 83.
I had about 5 comparisons to make, but it was much better than plugging in the values for 39,000 items.
I have learned a good lesson here. Break the inventories up into small units and have many smaller cycle counts, or place the items into smaller categories that can be narrowed, and then use the physical inventory feature.
Do not try to complete a large inventory at once with thousands of items.
When I try to use the configuration package it gives the "Phys. Inventory must be set to "Yes" error. There is a "No" flag set somewhere.
How can I set the flag to "Yes" so that I can import these items and quantities from excel.
Could this be done at the SQL database table?
Yes you can. Try to use the configuration package and import your data into the appropriate table. Good luck, A
What you probably want to do is create separate Physical Inventory Batches so you can have several persons assigned to do the data entry. To do this, you take all your items and break them up by Bin Code ranges (or just break up all the items into groups) so that you can have each Physical Inventory Batch hold a certain range of items (or by bin code).
You run the Calculate Inventory function within each batch filtering by that Bin Code range (or by Item no ranges) so each Batch ends up with a group of items - but all batches together would have all of your items. If you want more detail about that let me know. It's really pretty simple to do and then you can have several people doing data entry at the same time.
OTHERWISE to answer your direct question:
The next easiest way to do this is (if you have everything in one batch - the DEFAULT batch - then just send all the data to excel. Then delete all the items out of that DEFAULT batch after you've sent everything to the spreadsheet so when you're ready you can copy the rows from the Excel sheet and paste them back into the journal (all rows in the journal need to be removed and then you right click on the far left and choose paste rows).
You can write XMLPORT to update the physical quantities in journal
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (