automating Phys. Inventory count using reports

Savatage

jversusj wrote:

iif they had loaded history before you went live, they would have posted all the sales transactions. you would then do a phys. inventory to wipe out the negative stock (set to zero), and then another physical inventory (or item journal) to load your initial state. that would have put you right at the stock level you should have been at, with history and g/l postings.

as mentioned above if all offending entries have "START OF NAVISION" you should be able to filter them out to get an amount you need to reverse

mwalsh

Savatage wrote:

jversusj wrote:

iif they had loaded history before you went live, they would have posted all the sales transactions. you would then do a phys. inventory to wipe out the negative stock (set to zero), and then another physical inventory (or item journal) to load your initial state. that would have put you right at the stock level you should have been at, with history and g/l postings.

as mentioned above if all offending entries have "START OF NAVISION" you should be able to filter them out to get an amount you need to reverse

Script still wont run

Savatage

Viwe the keys in the Item Ledger Entry that YOU have. Pick one that has item # as the start of the key. Now change the report to use that key (Sort) instead of mine. either that simply create a new report based on the ILE that groups the item #. Now add the excel export code from my report to yours.

mwalsh

Savatage wrote:

Viwe the keys in the Item Ledger Entry that YOU have. Pick one that has item # as the start of the key. Now change the report to use that key (Sort) instead of mine. either that simply create a new report based on the ILE that groups the item #. Now add the excel export code from my report to yours.

Sorry :x
I just dont know enough about this Navision stuff to put it all together and make it work. If this doenst work, are you available for hire at all?

Savatage

I don't do that for hire thing.

Anyway it's simple
Go to Tools->Object Designer->Click On Table->Goto item Ledger Entry (Table 32)->Click Design->View->Keys

Replace the Line in the report

DataItemTableView=SORTING(Item No.,Completely Invoiced,Location Code,Variant Code);

with a key that exists in your database. Pick a checked one that starts with Item No. ..Probably the 2nd key

Copy & Paste it in the dataitem table view of the report.
http://savatage99.googlepages.com/Repor ... elExpo.txt

Ps what happens when you run calc inventory in the phys inv journals if you filter on "Start of Navision"?

mwalsh

Savatage wrote:

I don't do that for hire thing.

Anyway it's simple
Go to Tools->Object Designer->Click On Table->Goto item Ledger Entry (Table 32)->Click Design->View->Keys

Replace the Line in the report

DataItemTableView=SORTING(Item No.,Completely Invoiced,Location Code,Variant Code);

with a key that exists in your database. Pick a checked one that starts with Item No. ..Probably the 2nd key

Copy & Paste it in the dataitem table view of the report.
http://savatage99.googlepages.com/Repor ... elExpo.txt

Ps what happens when you run calc inventory in the phys inv journals if you filter on "Start of Navision"?

Not doing the for hire thing -- you're a smart man lol. Thats why i'm in the mess I am...

Anyways...

that makes a lot more sense now, I'll dot hat.

I cant run calc inventory by filtering by "Start of Navision", "Start of Navision" is the value of "Document No." and I cant filter on that field, won't let me add it in the filter list. So, to answer your question... I dont know.

Savatage

Ahhh - I see the report only lets you filter on item table not I.L.E.
I'm an end user that's why the Hire thing is not for me. There are better people at setting up, developing, etc. than I.

Are these start of navision entries entered in the same location? or did they create some History Location or something like that..I'm leaning towards they did not but I figured I'd ask.

mwalsh

Yeah

They were all posted as Orders in the system, they werent all imported as START OF NAVISION, START OF NAVISION is the starting inventory quantities, the correct inventory quantities. Our company doesnt invoice, we only have orders that come in, the orders stay orders (pay in advance), the partners posted all the orders against the system, thus messing up the START OF NAVISION correct inventory importation.

Savatage

were they all posted on the same day?
do they have anything in common that you can identify them with?
External Doc no? anything?

can you identify the real orders from the history orders some way?
since you can get the Start Of Navision Qty - one or the other (real/history) will get you to your goal of a correct inventory.

Else the final choice is to zero the QOH & do a cycle count and re-enter the correct QOH - & start from there

mwalsh

At this point, i've killed enough of everyones time... How do I zero out the QOH easily? can it be done programatically? You cannot import into the Phys. Item. Ledger Entry table or whatever it is.

ugh. sorry.


Also - no identifying information, yes posted all on the same day, but the posting data reflects that of the actual order/invoice.

mwalsh

The only way I could think how to fix this, is if I could do something like:

IF Order.PostingDate < '9/22/2008' THEN
... output quantity and item number of each item in the invoice line to an excel file ...
END IF

then I can write a script to go through, group all the item numbers together, and consolidate the quantities that were purchased
so, item 120 may appear on sales lines maybe 75 times at 2 quantity each, well the consolidated would be Item 120 sold 150 times before 9/22/2008

That way I could IN THEORY, import that list into the Item Journal Ledger table (or whatever) and post that adjustment, to bring it out properly


That is my thought. Alas, I dont know C/AL to be able to do it... err

Savatage

mwalsh wrote:

How do I zero out the QOH easily? can it be done programatically? You cannot import into the Phys. Item. Ledger Entry table or whatever it is. Also - no identifying information, yes posted all on the same day, but the posting data reflects that of the actual order/invoice.

When you mean reflect the order/Invoice is the document date accurate & the posting date - Or did they just make the Doc Date accurate and the posting date the same for all?

easily clean up...
Phy . Inventry Journal -> Functions -> Calculate inventory (Fill options tab)
Qty. (Phys. Inventory) you will go down the line with zero's

Run a test report so you have a hard copy.
You can always try this first on a copy of the database first to see if it's the road you want to take.

bbrown

Calculate the physical journal then write a process report to validate the "physical quantity" to zero. Then post.

Savatage

mwalsh wrote:

The only way I could think how to fix this, is if I could do something like:
IF Order.PostingDate < '9/22/2008' THEN
... output quantity and item number of each item in the invoice line to an excel file ...
END IF
then I can write a script to go through, group all the item numbers together, and consolidate the quantities that were purchased
so, item 120 may appear on sales lines maybe 75 times at 2 quantity each, well the consolidated would be Item 120 sold 150 times before 9/22/2008
That way I could IN THEORY, import that list into the Item Journal Ledger table (or whatever) and post that adjustment, to bring it out properly
That is my thought. Alas, I dont know C/AL to be able to do it... err

I thought that's what we've all been talkng about
The export to excel report groups by item number - you can filter on it like
Posting Date > 09/22/08
Type = Sale
Document No. <> Start of Navision

Then it will provide you with the info you're looking for.

mwalsh

Savatage wrote:

mwalsh wrote:

The only way I could think how to fix this, is if I could do something like:
IF Order.PostingDate < '9/22/2008' THEN
... output quantity and item number of each item in the invoice line to an excel file ...
END IF
then I can write a script to go through, group all the item numbers together, and consolidate the quantities that were purchased
so, item 120 may appear on sales lines maybe 75 times at 2 quantity each, well the consolidated would be Item 120 sold 150 times before 9/22/2008
That way I could IN THEORY, import that list into the Item Journal Ledger table (or whatever) and post that adjustment, to bring it out properly
That is my thought. Alas, I dont know C/AL to be able to do it... err

I thought that's what we've all been talkng about
The export to excel report groups by item number - you can filter on it like
Posting Date > 09/22/08
Type = Sale
Document No. <> Start of Navision

Then it will provide you with the info you're looking for.

Thats what we were talking about - but it wont work because you cant re-import into the phys. inventory table... right? It doenst work for me... I asked that about two pages ago

mwalsh

mwalsh wrote:

Savatage wrote:

mwalsh wrote:

The only way I could think how to fix this, is if I could do something like:
IF Order.PostingDate < '9/22/2008' THEN
... output quantity and item number of each item in the invoice line to an excel file ...
END IF
then I can write a script to go through, group all the item numbers together, and consolidate the quantities that were purchased
so, item 120 may appear on sales lines maybe 75 times at 2 quantity each, well the consolidated would be Item 120 sold 150 times before 9/22/2008
That way I could IN THEORY, import that list into the Item Journal Ledger table (or whatever) and post that adjustment, to bring it out properly
That is my thought. Alas, I dont know C/AL to be able to do it... err

I thought that's what we've all been talkng about
The export to excel report groups by item number - you can filter on it like
Posting Date > 09/22/08
Type = Sale
Document No. <> Start of Navision

Then it will provide you with the info you're looking for.

Thats what we were talking about - but it wont work because you cant re-import into the phys. inventory table... right? It doenst work for me... I asked that about two pages ago

Let me play for a minute, i'll be back.

Savatage

Once you get the item #'s & qty's into excel you can create a dataport to import the values into the
Item Journal Line Table. Yeah I see what you mean now.. How about just using the Item Journal Instead of the Phy inv Journal.

As long as you have a batch named "Default" you can create a simple dataport like this.
http://savatage99.googlepages.com/Datap ... lTable.txt

Just need item# & Quantity

I didn't fill location - not sure how you have it set up and you'll have to make it positive or negative. If you know which way it's supposed to go you can set that in the dataport too.

Last I always enter an item manually then Run the table. Make sure all the fields that get filled manually are also getting filled for the items entered using the dataport. Else something's missing ( a validation or an assigned field)

DaveT

Savatage wrote:

Posting Date > 09/22/08

Hi Harry,

Should this be < 09/22/08 ?

@ Mwalsh

If you can confirm the parameters I can modify Harry's first report and post it here.

mwalsh

DaveT wrote:

Savatage wrote:

Posting Date > 09/22/08

Hi Harry,

Should this be < 09/22/08 ?

@ Mwalsh

If you can confirm the parameters I can modify Harry's first report and post it here.

Yes, that is correct. I appreciate it.

mwalsh

Thanks everyone for helping -- But obviously, like I said earlier there were only a few days to spare, so we decided collectively to drop Navision completely and move back to our old system because the problems and errors were too far and wide.

I really appreciate everyones help - Happy Thanksgiving.

Savatage

too bad the implimentation went so bad - I think you would have liked Nav

jversusj

ouch... in tough times like these that's a hard pill to swallow - "we just spent $XXXXXXX on this new ERP and we're going to scrap the project." that's not good for business.

mwalsh

Yeah well... it was either, lose $100,000.00 - $140,000.00 or lose some of our main customers... Ford, BMW, GM and GM Europe. I'm sure you can imagine those figures.

I wish the implementation was better, I really do, maybe in the future we'll retry NAV, but for now, it just isnt viable, we have a lot of making up to do with the people who pay the bills.

Again, thanks guys.

jversusj

yeah, i completely understand. at the end of the day, if you have no customers, you don't need an ERP! I mostly meant bad for business in that your IT department will have this albatross around their neck everytime they request funds for a project...
Q: "can we have $XXXXXXX to do such and such"
A: "will such and such actually work? remember the NAV implementation."