Agree Jens it is the simplist and the easist way of doing it.
> Hello there, > > There is another "trick" that you could use ... :-) > > Do not load records directly into InventTrans, since this table is one of > the key tables in Ax. Instead, during quite a lot of implementations, I > simply load data into InventJournalTable and InventJournalTrans. > > Once the data is in the journal, you simply use the built-in function to > copy a journal. Once the journal is copied, the corresponding journal > lines > are now updated in such a way that you will be able to book the journal > without problems. > > I use the same approach when importing sales orders/lines and purchase > orders/lines. I simply import them as journals, after which I let Ax > handle > the rest when changing order type from Journal to Sales Order / Purchase > Order. > > Hope this helps. > > /Jens > > _____ > > From: Axapta-Knowledge-Village@yahoogroups.com > [mailto:[EMAIL PROTECTED] On Behalf Of madhubabu_r > Sent: Sunday, October 07, 2007 2:26 PM > To: Axapta-Knowledge-Village@yahoogroups.com > Subject: [Axapta-Knowledge-Village] Re: Excel templates for Uploading > opening Stock > > > > Hi Ashwani, > > Thanks for such a detailed information which would be useful to all > our fellow implementors. > > I could successfully upload the opening stock using > InventJournalTable, InventJournalTrans and InventTrans. I have not > included InventDim as the DimGroupIds of all the items already exists > in InventDim table. > > I have to include InventTrans as I was getting an error that there > should be transaction with status "Ordered". I have included > InventTrans table with status Ordered of all the items. > > I have uploaded and then posted the Movement Journal. Now when you see > the on-hand inventory you will see the items in Ordered as negative > witht he quantity uploaded. Then I went to InventSum table and changed > all those negative entries to "0". > > I have tested master scheduling and other functionalities. Its working > fine. > > Please reply whether this is ok. As you have used a word > "malfunctioing" I am worried. > > Awaiting your reply. > > Thanks and best regards, > > Madhu Babu Rapolu > > --- In Axapta-Knowledge- > <mailto:Axapta-Knowledge-Village%40yahoogroups.com> > [EMAIL PROTECTED], ASHWANI JAIN > <[EMAIL PROTECTED]> wrote: >> >> Hi Madhu, >> >> Uploading the opening stock using manual activity is really a > tedious job, but yes we can do a partial time saving for the creation > of journal (Invent journal) and journal lines (Invent journal trans). >> >> At the time of creation of record in journal lines (Inventory > journal Trans), system automatically search for inventdim and if not > found, then create a new record in InventDim, else allocate the > available one on the InventJournalTrans. After the creation of new > record in InventDim, same numbr is allcoated to respective transaction > based on Dimension combination. >> >> Different inventJournalTrans lines can have same inventDim id. >> >> So in addition to above two tables, you have to import another third > table "InventDim". >> >> Now the question arises, how to pick / define the correct InventDim > in the import file in Table InventJournalTrans, as this table does not > have Dimensions (config, color, size, warehouse, location etc...) >> >> Because system allocates, this on the basis of unique combination of > dimension, so in excel using concatenate formula, create a new column > and put formula in that to get value. Now there must be unique values > in this field (refer above bold sentence). >> >> Filter out the duplicates, and now you should have single record of > each type. >> Take the current data of InventDim table to Excel by copy paste. Do > same concatenate column in this worksheet also in excel. >> >> now using the Vlookup formula of Excel, check in earlier inventdim, > what is the inventdim of those records. you will find some records / > rows in excel where you will not get any inventdim. >> >> Take those records in new worksheet for import in inventdim. When > you import these records, system will create the inventdim id for > those records. Put these new alocated number along with the previous > that you find in the InventJournalTrans. Now you can import > InventJournal and InventJournalTran Tables. >> >> After importing them, ensure that all lines will be having inventdim > id and cross validate for the dimensions (config, color, size, > warehouse, location etc...) in them for 5 - 10 records. If found > satisfactory, then you just have to click the Post button. >> System will post the same in inventTrans and inventsum, to update > the inventory. >> >> We should not have to import the records in InventTrans / InventSum, > because these tables are the heart of Dynamics Ax, which may later on > result in malfunctioning of the system. >> >> This whole lenghty explaination appears to be difficult / > complicated, but once you do it, you will find it as the easiest and > quicker exercise. >> >> In case you need any further assitance, do write to me. >> >> Regards, >> Ashwani Jain >> >> >> ----- Original Message ---- >> From: nigel_cox2003 <[EMAIL PROTECTED]> >> To: Axapta-Knowledge- >> <mailto:Axapta-Knowledge-Village%40yahoogroups.com> > [EMAIL PROTECTED] >> Sent: Wednesday, 26 September, 2007 10:07:34 PM >> Subject: [Axapta-Knowledge-Village] Re: Excel templates for > Uploading opening Stock >> >> Forgive me, madhubabu_r, if it's just a typo on your part, but you >> list InventJournalTable and InventTrans as the tables you uploaded. >> Shouldn't this be InventJournalTable and InventJournalTrans as Sumit >> Loya listed? >> >> --- In Axapta-Knowledge- [EMAIL PROTECTED] ups.com, "Sumit Loya" >> <loya.sumit@ ...> wrote: >> > >> > Hi Madhu, >> > >> > Am not exactly sure why this error is coming. >> > The best way to load opening balances is thru Movement journal only. >> > >> > I wud not guess the reason for this error as this error information >> is not >> > descriptive enough.. >> > >> > u can try and to debug and see from where this error is coming and >> on what >> > condition. >> > >> > Do let me know if u come to know anything on this front. >> > >> > One more thing if you are posting Movement journal then u need to >> have data >> > in InventJournalTable (Movement journal header) and >> InventJournalTrans >> > (Movement journal line). You need not create records in InventSum. >> It will >> > be taken care while posting the Movement journal also enure that the >> > Inventory dimensions id that u will provide in InventJournalTrans >> exists >> > before you post the records. >> > >> > Regards, >> > Sumit >> > >> > On 9/26/07, madhubabu_r <madhubabu_r@ ...> wrote: >> > > >> > > Hi Sumit, >> > > >> > > Finally shud i take the following tables in the excel templates: >> > > InventJournalTable >> > > InventTrans >> > > InventSum??? >> > > >> > > The tables I have uploaded are InventJournalTransa and >> InventTrans. I >> > > have managed all the number sequences in excel template itself. I >> > > could upload the data into the Movement Journal Lines. Movement >> > > Journal was also posted. I could see the onhand stock. But when I >> was >> > > trying to create a Sales order/Purchase Order /any inventory >> > > journals - just after entering the item and i click save I am >> getting >> > > an error message - "Illegal Reference" and I cannot proceed >> further. >> > > >> > > Any answers for this?? >> > > >> > > Regards, >> > > >> > > Madhu >> > > --- In Axapta-Knowledge- [EMAIL PROTECTED] ups.com<Axapta-Knowledge- >> Village%40yahoogrou ps.com>, >> > > "Sumit Loya" >> > > <loya.sumit@ > wrote: >> > > > >> > > > Hi, >> > > > >> > > > I wudnt say only InventJournalTrans and InventDim.. >> > > > >> > > > All item transactions are also mirrored in InventTrans table and >> > > also >> > > > the on-hand (stock detalis) are stored in InventSum table >> (Onhand >> > > > table). >> > > > >> > > > Regards, >> > > > Sumit >> > > > >> > > > On 9/25/07, madhubabu_r <madhubabu_r@ > wrote: >> > > > > Hi friends, >> > > > > >> > > > > Manually entering opening stock in Movement Journal must be >> the >> > > most >> > > > > laborious job with all those Excise related entries also >> > > included. In >> > > > > my current project there are about 10000 items as opening >> stock. I >> > > > > would like to know which tables I have to consider for excel >> > > upload of >> > > > > these opening entries. >> > > > > >> > > > > Awaiting your valuable responses friends. >> > > > > >> > > > > Thanks and best regards, >> > > > > >> > > > > Madhu Babu Rapolu >> > > > > >> > > > > >> > > > >> > > >> > > >> > > >> > >> > >> > [Non-text portions of this message have been removed] >> > >> >> >> >> >> >> Why delete messages? Unlimited storage is just a click away. > Go to http://help. > <http://help.yahoo.com/l/in/yahoo/mail/yahoomail/tools/tools-08.html> > yahoo.com/l/in/yahoo/mail/yahoomail/tools/tools-08.html >> >> [Non-text portions of this message have been removed] >> > > > > > > > [Non-text portions of this message have been removed] > >