automating Phys. Inventory count using reports

mwalsh · 2008-11-17

Quick summary - new to forums (in general, never had to post anywhere before), not completely new to Navision or C/AL but enough to where I cant solve this problem. I am a programmer on a two year full time contract with a client of mine who runs Navision 5. Their Microsoft Partner was unable to complete the project (or even start it) after they took about $78,000.00, needless to say, i've been doing all the work myself. The new Navision partner wants $40,000.00 to step in and finish up, which isnt giong to happen. So herein lies the dilema. The problem - I am stuck trying to correct an inventory mistake made by the previous Navision partner, they posted the last 5 years of invoices against my current, and correct inventory count, and now I cant reverse it. Using a report, could someone help me, cycle through all the items (there are 4,600) and set the Phys. Inventory Count (Qty. on Hand) to 0... OR, go through the last five years of invoices, previous to 9/22/08 and do an adjustment against the inventory to correct the numbers, thereby making it appear the last five years had never affected the inventory in the first place?

I am stumped and days away from the client dumping Navision for their previous system after all this time and money. Can someone please help?

If it makes a difference - we have a full developer license as well in the event that it cannot be done through reports.

Thank you!
Matthew R. Walsh

Savatage · 2008-11-17

how was the correct amount entered? A 1 time Dataload of the correct Qty on Hand using a dataport to the Item Journal?

& then what happened? invoices added? So when you drill into an items qty on hand what do you see?

a dataload line and a bunch of Entry Types = Sales?

Have you tried an Phy Inv Journal -> functions calculate inv? no filters should pull up every item when you could zero out the inv.

There are much more experienced implementors that probably know how to get you on the right track.
Basically - has Nav been used?? Do you want to start fresh??

mwalsh · 2008-11-17

Yes, NAV has been used for the past four months.

I assembeled all the data from the old system, which was sprawled out across three different databases, and about 160 different tables. I improted items, customers, contacts, etc. through dataports.

When the items were imported, Qty. on hand was 0

I then got the current inventory count for each item from the old system, imported it into an item journal, posted it, thus reflecting the good inventory amounts.

Orders were imported after this was done, the Microsoft Partner came out (this is the last time we spoke to them) and told us we had to post all the invoices for the last 5 years or historical data wouldnt exist. So, they posted it, inventory went out of whack, now almost all items are in the negatives.

When I drill into an items Qty. on Hand, I see if we've sold that item but I also see each time we've sold that item for the last 5 years, i also see the positive adjustment made when I originally imported the inventory.

The physical inv. journal calculation would work, yes, but we'd lose the last four months of inventory if resetting back to zero. Also, we have 4,600 items, at least 80,000 invoices containing roughly 1 - 30 items per invoice, so for me to go through and press F2 to validate each item in that physical inv. journal window, is sort of counter productive.

I am looking for the fastest, easiest way to adjust the inventory by adjusting against what happened the day of the posting. Preferably something in code, since I have to do this for our system in Germany as well (they posted there too).

The 2nd Microsoft Partner that came to the office, took a copy of the data and analyzed it, said they could write a routine to straighten it out. Backing those invoices inventory adjustments out is the only real way to fix this since we have about four months into the system now.

Does that clarify things at all?

themave · 2008-11-17

here is what I would do as an end user, without access to programing,

1. run a physical inventory journal, and make a processing only report,

that runs on the item journal line table.

filter
Item Journal Templat Name = PHYS. INVE (or what ever your defualt physical inventory journal template name is)

then

Qty. (Phys. Inventory) = 0
validate

this should set the counted qty to zero on the journal for all the items

Post the journal on a date with no other activity, then after you have posted it, you can see the g/l entries that were made and make one big correcting journal entry for the totals.

Now you can redo the import of your items that you did in the first place to get your correct beginning balances.

mwalsh · 2008-11-17

Thanks for your reply.

I'm not an end-user, I have access to programming, I have a full license.

Resetting everything to 0 -- That would still cause the last four months of inventory to be wrong.

I still think the best method is figuring out a way to just back out the transactions that were posted, through an adjustment, the inventory has to be perfect because our clients (BMW, Mercedes, Lamborghini, GM, Ford, etc.) want EXACT inventory for reorder points.

any other suggestions that retain the last 4 months of inventory transactions?

Savatage · 2008-11-17

here's a quick report I thru together that creates totals per item for item ledger entries.

It then exports it to excel

so you can check a few to make sure your filters are correct (type, dates, location, etc.)
- create a dataport & import the values (if correct) into the item ledger entry - run a test report to get the totals so if you need to make g/l adjustments at least you have a number now.

http://savatage99.googlepages.com/Repor ... elExpo.fob

Since it's a ton of extra sales with no puchases to put against it - it shows your QOH as negative.

So you'll probably have to multiply the qty by -1 to get it positive. It would be another type of dataload add it to the descriptions (Sales order History Inventory correction) or someting like that.

DaveT · 2008-11-17

Hi,

There are a number of solutions - I'm still unclear where the 5 months of invoicing fit it. So:

1. You could run an inventory valuation report as at 4 month ago and adjust off the figures given there. You could even take a copy of the report and use it to update the Phy Inv Journal to the difference.

2. Run a report off the posted invoice & credit memo tables and total - again update the differnece into the Phy Inv Journal. (Was there any other movements?)

3. The inverse (and probably the best) - take the initail qty, adjust for the last 4 months transaction and update the Phy Inv Journal.

The goods news is that the programming is the same part - validating the results is the hardest - I hope you have help

themave · 2008-11-17

mwalsh wrote:

Thanks for your reply.

I'm not an end-user, I have access to programming, I have a full license.

Resetting everything to 0 -- That would still cause the last four months of inventory to be wrong.

I still think the best method is figuring out a way to just back out the transactions that were posted, through an adjustment, the inventory has to be perfect because our clients (BMW, Mercedes, Lamborghini, GM, Ford, etc.) want EXACT inventory for reorder points.

any other suggestions that retain the last 4 months of inventory transactions?

I thought when you said "Using a report, could someone help me, cycle through all the items (there are 4,600) and set the Phys. Inventory Count (Qty. on Hand) to 0... " you wanted a method to reset to zero, which is what I was trying to accomplish. I did answer your specific question. sorry I misunderstood.

In any case, programmer or not, do you know the net qty of each item the Nav part imported that you do not want in the system, if so, then simply create a dataport to import these items in an item journal, making the type negative adjustment, and post the journal, if you don't have the specific summary for each part, then export the item ledger entries for the transactions, to access, use that to make your journal lines. and import it back into an item journal, it would not be very difficult, but it would also not use programming, which is seems is the only solution you are looking for. good luck.

some times programing is not needed. If I can be blunt here, you have a solution center that can do it for you, why not just pay for it, since you don't know how to do it yourself? I am trying to be seriouse.

Savatage · 2008-11-17

I posted a export report above..I think they are a bit wearly with solutions centers since they already spent 78K and the solution center was the one that screwed it up. now another wanted 40k to fix it - i think that's what the orig post said.

Should not be too hard to figure out how to get the correct qty.

1. The qty was correct
2. the history was added on some date and screwed it up. (While Live?)
3. calculate the actual "live" sales

if you know the date of those transactions (history) you should be able to calc it's amount.
Then reverse it out.

bbrown · 2008-11-17

Savatage wrote:

I posted a export report above..I think they are a bit wearly with solutions centers since they already spent 78K and the solution center was the one that screwed it up. now another wanted 40k to fix it - i think that's what the orig post said.

The way I read it is that the first solution center charged $78K and that a second one wants $40K to resolve the issues.

mwalsh · 2008-11-17

Is this trying to go through the item ledger? Because there is nothing in the item ledger.

Everything has already been posted against the G/L and A/R. Isnt there some way that I can just (even if its dirty) remove those posted transactions?

The code wouldnt run for me - throws this error: "Field no. 5403 is not defined in the Item Ledger Entry Table"

*sigh*

Savatage · 2008-11-17

tag teaming :roll: It sounds like a tough spot. In one hand you want the first center to fix what it messed up. On the other hand you're worried what other mistakes will they make.

Ahh that's different then if it's just a bunch of entries. I assumed you have each car set up as it's own item.

5403 = bin code. Anyway if you have no item ledger entries then you don't need that export report..

Savatage · 2008-11-17

your first post mentions an inventory error - yet you have no item ledger entries.

Do you mean Inventory error as meaning your g/l inv amount is wrong? What kind of history is that?
now i'm confused

DaveT · 2008-11-17

Hi,

First to get the report to work change the sorting key - Harry developed thisa on a US version (and a good job too).

Savatage · 2008-11-17

Thanks, only spent 2 minutes - I was thinking of getting the item table too so you could filter out blocked item n such.

Savatage · 2008-11-17

mwalsh wrote:

Is this trying to go through the item ledger? Because there is nothing in the item ledger.

sorry this line thru me.

I think what they are saying is that if the history was posted all on the same DAY!!! that you could run the Phy inv journal just for that DAY - therefore giving you the amount you need to get rid of. Of course tesing this on a copy of the database is always the best. if not possible try just for 1 item.

Figure out how much was subtracted due to history being added. Now run the report (calculate Inventory) using the posting date as a filter for the day the history was added.. or range (if that's the case). See if it's the same amount. If it is then spot check the list at random to see if all those entries are correct too. Then you'll be happy about the mass changes you're about to make.

Think about making a backup before doing this (if you don't have a test database copy) as a safety net.

DaveT · 2008-11-17

Hi,

A number of issues:

1. Correcting the inventory will not (unless you're extremely luckly) fix the values posted to the G/L

2. There is a difference in trying to solve this cost effectively and having the experience to see all the pitfalls e.g. how will the item costs be effected?

3. I can understand your frustration but be careful not all partners are bad.

Now to the issue at hand:

1. You know your starting inventory (e.g. 50 )
2. You can get the qty posted in the 5 years using Harry's report ( e.g. -289 )
3. You can use a report/dataport to set the value into the Phy Inv Journal

The problem is bigger than just fixing quantities and the problem you have is someone taking ownership to fix it - that's why (I suspect) the price is so high.

Savatage · 2008-11-17

It's probably not possible to just make a fob to fix the problem. Not enough info. About which entries are real and which are history. when was the history added & when was the correct dataload made. The orig partner shhould have never let you go live with bad data. IMHO

\:D/ Perhaps David Singleton to the rescue I think he like chalanges like this.

He's Nav's Harry Tuttle

mwalsh · 2008-11-17

Savatage wrote:

mwalsh wrote:

Is this trying to go through the item ledger? Because there is nothing in the item ledger.

sorry this line thru me.

I think what they are saying is that if the history was posted all on the same DAY!!! that you could run the Phy inv journal just for that DAY - therefore giving you the amount you need to get rid of. Of course tesing this on a copy of the database is always the best. if not possible try just for 1 item.

Figure out how much was subtracted due to history being added. Now run the report (calculate Inventory) using the posting date as a filter for the day the history was added.. or range (if that's the case). See if it's the same amount. If it is then spot check the list at random to see if all those entries are correct too. Then you'll be happy about the mass changes you're about to make.

Think about making a backup before doing this (if you don't have a test database copy) as a safety net.

I dont know if that will work either - the posting dates arent the same...

When i pull the filters off in Item Ledger Entries for a given item, theres a huge list of all the invoices that hit, the posting dates are all different, dating back to 2004, but all with the Document No. "START OF NAVISION" but they are positive adjustments with negative quantities?

I have no idea what these people did or why it looks like this.

Can I just do a dirty SQL delete on all those item ledger entires that I dont want? Does anyone know the reprocussions of this?

DaveT · 2008-11-17

Hi,

Please dont start deleting data - especially outside of Nav as you will get inconsistenties all over the place.

mwalsh · 2008-11-17

DaveT wrote:

Hi,

A number of issues:

1. Correcting the inventory will not (unless you're extremely luckly) fix the values posted to the G/L

2. There is a difference in trying to solve this cost effectively and having the experience to see all the pitfalls e.g. how will the item costs be effected?

3. I can understand your frustration but be careful not all partners are bad.

Now to the issue at hand:

1. You know your starting inventory (e.g. 50 )
2. You can get the qty posted in the 5 years using Harry's report ( e.g. -289 )
3. You can use a report/dataport to set the value into the Phy Inv Journal

The problem is bigger than just fixing quantities and the problem you have is someone taking ownership to fix it - that's why (I suspect) the price is so high.

Now I see - sorry, head is fried.

Let me try that in the A.M. and i'll get back with you, just one thing though - I tried importing using a dataport into the Phys. Inv. Journal and it wouldnt let me, told me i'm not allowed to import there. I'll try it again tommorow, but thats where I gave up last time.

Agreed about the ownership comment - I wouldnt want this mess either

lol

mwalsh · 2008-11-17

DaveT wrote:

Hi,

Please dont start deleting data - especially outside of Nav as you will get inconsistenties all over the place.

I figured, thats why I asked first

mwalsh · 2008-11-17

Savatage wrote:

It's probably not possible to just make a fob to fix the problem. Not enough info. About which entries are real and which are history. when was the history added & when was the correct dataload made. The orig partner shhould have never let you go live with bad data. IMHO

\:D/ Perhaps David Singleton to the rescue I think he like chalanges like this.

He's Nav's Harry Tuttle

The project was started before I was working here, on October 2008, they said they could assemble and import data and have everything done by Jan 1st 2008.

They finally threw in the towel in October of this year, they made a lot of meetings, and stirred up a lot of drama, but never any action.

They didnt let us go live with bad data - they never had a test database created, they were doing it on a live production database, if you can believe that. These guys, had two companies created in one database, they called each company a "database" even though, as well know, they are just SQL tables with different naming prefixes.

So, they made changes in a live production database, set up the G/L, the A/P, A/R, vendors, etc. and realized later after I asked the question and wondered why there were inconsistencies in the data and why half the time I didnt see their changes... That they had everything completely %^&*(&R%@^*&#&(@#^&#@*^& up... royally.

Anyways, long story short, it was a bad situation, doomed from the beginning. We were thier first Navision data importation customer... nice huh?

mwalsh · 2008-11-17

Have a good night everyone, i'll try suggestions in the morning and get back to you.

Thank you!

DaveT · 2008-11-18

Hi,

We were thier first Navision data importation customer... nice huh?

Not good - You will probably need to review the full data set.

Anyway, one for the morning...

Filter down the transactions i.e. type Sale and Document No. = "START OF NAVISION" using Harry's report and check the output in Excel. If you're happy with the figures then the following code will update the Phy Inv journal (assuming no locations/bins and batch DEFAULT). Replace the code in the Item Ledger Entry, GroupFooter (8) - OnPreSection() with

PhyInvJnl.RESET;
PhyInvJnl.SETRANGE( "Journal Batch Name", 'DEFAULT' );
PhyInvJnl.SETRANGE( "Item No.", "Item Ledger Entry"."Item No." );
IF PhyInvJnl.FIND( '-' ) THEN
  BEGIN
    PhyInvJnl.VALIDATE( "Qty. (Phys. Inventory)",  PhyInvJnl."Qty. (Calculated)" - "Item Ledger Entry".Quantity );
    PhyInvJnl.MODIFY;
  END;

Also comment out the code in the Item Ledger Entry - OnPostDataItem() trigger.

Good luck [-o<

mwalsh · 2008-11-18

Alright, i'll give it all a try. Sounds logical, i'll see where i'm at with it in a bit.

Would anyone like to fill in for our Dynamics NAV partners and make some changes for us regarding report templates, check templates, etc.

It's a name your hourly rate kind of thing, so if anyone is interested, contact me.

Savatage · 2008-11-18

mwalsh wrote:

They didnt let us go live with bad data - they never had a test database created, they were doing it on a live production database, if you can believe that. These guys, had two companies created in one database, they called each company a "database" even though, as well know, they are just SQL tables with different naming prefixes.

So, they made changes in a live production database, set up the G/L, the A/P, A/R, vendors, etc. and realized later after I asked the question and wondered why there were inconsistencies in the data and why half the time I didnt see their changes... That they had everything completely %^&*(&R%@^*&#&(@#^&#@*^& up... royally.

Another reason to always get references and call & talk to each & every one of them. Let a startup company be the guinea pig. To think everything was fine until history was added. I think we were 2nd or 3rd installation for the company we used. Still not alot of experience but we added no history just open entries. If we needed history we logged on to our old system to get the info (both accessible from your PC). After 6 months you realize that you barely ever log onto the old system anymore and after a year..never.

no test database??? - making changes in the test and not realizing it's effecting the live???? uugh!

mwalsh · 2008-11-18

Savatage wrote:

It's probably not possible to just make a fob to fix the problem. Not enough info. About which entries are real and which are history. when was the history added & when was the correct dataload made. The orig partner shhould have never let you go live with bad data. IMHO

\:D/ Perhaps David Singleton to the rescue I think he like chalanges like this.

He's Nav's Harry Tuttle

When I run your report... it says field number 5403 doesnt exist in the Item Ledger Table -- i think something wrong here, field numbers go (5401... 5402.... 5404... 5405...) could you tell me what your field number 5403 is in your item ledger table, maybe I can re-add it...

mwalsh · 2008-11-18

Savatage wrote:

mwalsh wrote:

They didnt let us go live with bad data - they never had a test database created, they were doing it on a live production database, if you can believe that. These guys, had two companies created in one database, they called each company a "database" even though, as well know, they are just SQL tables with different naming prefixes.

So, they made changes in a live production database, set up the G/L, the A/P, A/R, vendors, etc. and realized later after I asked the question and wondered why there were inconsistencies in the data and why half the time I didnt see their changes... That they had everything completely %^&*(&R%@^*&#&(@#^&#@*^& up... royally.

Another reason to always get references and call & talk to each & every one of them. Let a startup company be the guinea pig. To think everything was fine until history was added. I think we were 2nd or 3rd installation for the company we used. Still not alot of experience but we added no history just open entries. If we needed history we logged on to our old system to get the info (both accessible from your PC). After 6 months you realize that you barely ever log onto the old system anymore and after a year..never.

no test database??? - making changes in the test and not realizing it's effecting the live???? uugh!

well thats the thing with references, they called the four references that the company was working with (just integration/installation/setup, no importation) and they said everything was fine. But as a business owner, would you hand out references that werent a sure thing?

The history is a requirement of GM and BMW, we didnt have a choice, I imported the history, that was no problem, they reposted the invoices. We had to import the history because I have built an extremely complex shopping cart system that integrates into NAV and gives all kinds of statistical information to the customer (GM, BMW) as well as take and process new customers, orders, payments, multilanguage capabilities, etc. So - again, not a lot of choice, I just wish the experience was less painful.

I sound like a 70 year old nagging old hag... lol... But seriously, it's frustrating.

Savatage · 2008-11-18

DaveT wrote:

Hi,
First to get the report to work change the sorting key - Harry developed this on a US version (and a good job too).

So apparently by this post - if you change the sort of the report to a key of your own it should work.
These have been answered - perhaps you missed some of the posts - re-read to see if you missed any of the discussion.

Savatage wrote:

5403 = bin code.

Ok changed the sort - try this one
http://savatage99.googlepages.com/Repor ... elExpo.txt

jversusj · 2008-11-18

i still do not quite understand what the offending partner did, exactly.

To load history into NAV, you can easily load the data into Sales Orders and Post it all using the same dates as the original transaction (with warehousing disabled) and the system would consume inventory and post all the G/L entries as defined by the G/Lposting set-up just like any run of the mill transaction. we did this at a conversion we did from C5 to NAV 4 at my old job. we didn't even have a developers license and were able to get it in accurately. the caveat is that we had to do a lot of posting from an accounting perspective to get the books balanced to the go-live values. This could be big work or small work, depending on how much history you decided to bring over (did you bring just invoices, or also credit memos and payments, etc).

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

did your partners go this route? did they do something different to load history.

so, as the other posters have asked, can you get a list of the Item Ledger Entry records that were created by the posting of the historical documents? If so, you would want to post an equal but opposite qty entry to get your stock back in line, posting to the same date as your initial inventory load.

Sorry if i completely misunderstand what went on and if this does not help you at all.

Howdy, Stranger!

Quick Links

Categories

automating Phys. Inventory count using reports

Comments