SBX - Search With Button

SBX - Forum Post Title

PHYSICAL INVENTORY JOURNALS

Microsoft Dynamics NAV Forum

addision1 asked a question on 13 Jan 2017 12:31 PM
My Badges

Question Status

Suggested Answer

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?

 

Reply
Suresh Kulla responded on 13 Jan 2017 12:58 PM
Suggested Answer

Yes, you can by developing a custom xmlport to import the items from your excel sheet to match the entries based, item no. location code or any other columns uniquely identifies the record and update the calculated inventory.

Reply
Maneesha De Silva responded on 13 Jan 2017 1:52 PM
My Badges
Suggested Answer

Hi , 

Yes you can , just export to EXCEL and edit you want and copy all and "paste rows" to the lines  

Reply
Amol Salvi responded on 13 Jan 2017 11:17 PM
My Badges
Suggested Answer

You can write XMLPORT to update the physical quantities in journal

Reply
Alex A responded on 14 Jan 2017 9:01 PM
My Badges
Suggested Answer

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).

Reply
Adam@360BV responded on 19 Jan 2017 10:25 AM
Suggested Answer

Yes you can. Try to use the configuration package and import your data into the appropriate table. Good luck, A

Reply
addision1 responded on 14 Feb 2017 5:10 PM
My Badges

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?

Reply
addision1 responded on 23 Feb 2017 1:08 PM
My Badges

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.

Reply
Preben Borg responded on 23 Feb 2017 2:54 PM
My Badges

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.

Reply
Amol Salvi responded on 23 Feb 2017 8:37 PM
My Badges
Suggested Answer

MS already provided rapidstsrt for importing data

Reply
Preben Borg responded on 24 Feb 2017 3:51 AM
My Badges

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.         

Reply
GabrielK responded on 10 Aug 2018 12:55 PM

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

Reply
Suresh Kulla responded on 13 Jan 2017 12:58 PM
Suggested Answer

Yes, you can by developing a custom xmlport to import the items from your excel sheet to match the entries based, item no. location code or any other columns uniquely identifies the record and update the calculated inventory.

Reply
Maneesha De Silva responded on 13 Jan 2017 1:52 PM
My Badges
Suggested Answer

Hi , 

Yes you can , just export to EXCEL and edit you want and copy all and "paste rows" to the lines  

Reply
Amol Salvi responded on 13 Jan 2017 11:17 PM
My Badges
Suggested Answer

You can write XMLPORT to update the physical quantities in journal

Reply
Alex A responded on 14 Jan 2017 9:01 PM
My Badges
Suggested Answer

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).

Reply
Adam@360BV responded on 19 Jan 2017 10:25 AM
Suggested Answer

Yes you can. Try to use the configuration package and import your data into the appropriate table. Good luck, A

Reply
Amol Salvi responded on 23 Feb 2017 8:37 PM
My Badges
Suggested Answer

MS already provided rapidstsrt for importing data

Reply

SBX - Two Col Forum

SBX - Migrated JS