mibuso.com

Microsoft Business Solutions online community
It is currently Wed May 22, 2013 12:23 pm

All times are UTC + 1 hour [ DST ]




Post new topic Reply to topic  [ 12 posts ] 
Author Message
 Post subject: Item Ledger Entry Table
PostPosted: Mon Jul 02, 2012 12:09 pm 
Offline

Joined: Mon Jan 30, 2012 11:03 am
Posts: 61
Country: Singapore (sg)
Hi,

I have a strange issue. I take navision's item ledger entry table number (32) into Excel. I go to Excel, click on Data, select from other source and choose MS SQL SERVER and i key in the navision server name, user id and password and select my live database name, click next and choose item ledger entry table from my company (i have two companies in one database) and click finish and make it as a pivot table, i can see all the fields but fields like Cost Amount (Actual) and etc in this table, why is this so????

My management wants me to construct a pivot table that could be refreshed on it own rather than manually copying and pasting the info from navision to excel, i thought of making use of Item Ledger Entry Table, where I can filter records by item cat code, product grop code and some other fields which we have managed to customize but Item ledger entry table when pulled in excel from other source (SQL) doesnt show all its fields, any clues

Regards
KH


Top
 Profile E-mail  
 
 Post subject: Re: Item Ledger Entry Table
PostPosted: Mon Jul 02, 2012 12:18 pm 
Offline

Joined: Sat Aug 08, 2009 6:28 am
Posts: 158
Location: gujarat
Country: India (in)
Hi,

because these fields are flow fields, these are not stored in same table in sql, these are store in some other table,

http://dynamicsuser.net/forums/p/21080/97682.aspx

_________________
Uday Mer | MS Dynamics NAV Techno-Functional Consultant


Top
 Profile E-mail  
 
 Post subject: Re: Item Ledger Entry Table
PostPosted: Mon Jul 02, 2012 12:34 pm 
Offline

Joined: Wed Apr 07, 2004 9:39 am
Posts: 338
Location: Bangalore
Country: India (in)
I am not sure.. but if these are FlowFields, then the values will probably not be populated if you access the table directly from SQL Server. You have to probably create a form and populate the records and ask the user to export the data to excel from your form.

Chn


Top
 Profile E-mail WWW  
 
 Post subject: Re: Item Ledger Entry Table
PostPosted: Mon Jul 02, 2012 12:43 pm 
Offline

Joined: Tue Jun 07, 2005 5:24 pm
Posts: 2574
Location: MA
Country: United States (us)
Chinmoy wrote:
I am not sure.. but if these are FlowFields, then the values will probably not be populated if you access the table directly from SQL Server. You have to probably create a form and populate the records and ask the user to export the data to excel from your form.

Chn


Or retrieve the records from the appropriate table(s).

_________________
There are no bugs - only undocumented features.


Top
 Profile E-mail  
 
 Post subject: Re: Item Ledger Entry Table
PostPosted: Mon Jul 02, 2012 12:50 pm 
Offline

Joined: Sat Aug 08, 2009 6:28 am
Posts: 158
Location: gujarat
Country: India (in)
If you want Item ledger entries in excel then from Item ledger entry table, export to excel

_________________
Uday Mer | MS Dynamics NAV Techno-Functional Consultant


Top
 Profile E-mail  
 
 Post subject: Re: Item Ledger Entry Table
PostPosted: Tue Jul 03, 2012 7:11 am 
Offline

Joined: Mon Jan 30, 2012 11:03 am
Posts: 61
Country: Singapore (sg)
Hi All,

Thanks for the replies!

can someone tell me which table must i choose if i need to see those fields in my excel

Regards
KH


Top
 Profile E-mail  
 
 Post subject: Re: Item Ledger Entry Table
PostPosted: Tue Jul 03, 2012 7:23 am 
Offline

Joined: Wed Apr 07, 2004 9:39 am
Posts: 338
Location: Bangalore
Country: India (in)
You can straight away read the Item Ledger Entry table inside a form, but issue a CalcFields() for all the FlowFields, like you mentioned the Cost Amount (Actual) field.

Chn


Top
 Profile E-mail WWW  
 
 Post subject: Re: Item Ledger Entry Table
PostPosted: Tue Jul 03, 2012 7:27 am 
Offline

Joined: Sat Aug 08, 2009 6:28 am
Posts: 158
Location: gujarat
Country: India (in)
udayrmer wrote:
If you want Item ledger entries in excel then from Item ledger entry table, export to excel


Item Ledger Entry Table

_________________
Uday Mer | MS Dynamics NAV Techno-Functional Consultant


Top
 Profile E-mail  
 
 Post subject: Re: Item Ledger Entry Table
PostPosted: Tue Jul 03, 2012 12:47 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Tue Aug 14, 2001 7:01 am
Posts: 5315
Location: Prague
Country: Czech Republic (cz)
Create a view that joins the Item Ledger Entry to the Value Entry table.

_________________
David Singleton
Dynamics NAV Freelancer
Dynamics Book
Go-Live International


Top
 Profile E-mail WWW  
 
 Post subject: Re: Item Ledger Entry Table
PostPosted: Wed Jul 04, 2012 2:36 am 
Offline

Joined: Mon Jan 30, 2012 11:03 am
Posts: 61
Country: Singapore (sg)
Hi David,

thanks for your reply! I am not sure on how to do it, can you give me some steps on how to get this out with help of view and joining the tables (item ledger entry and value entry table)

Regards
KH


Top
 Profile E-mail  
 
 Post subject: Re: Item Ledger Entry Table
PostPosted: Sat Jul 07, 2012 12:32 pm 
Offline

Joined: Mon Aug 10, 2009 5:31 pm
Posts: 40
Location: Tamilnadu
Country: India (in)
Hi KH,


The Value Entry table have a field Item Ledger Entry Number you can link ILE with this "Entry No." field. or you can directly group the data by Item Category Code and Product Group Code and sum up the fields "Cost Amount (Actual)" and "Cost Amount (Expected)".

Rgds
Prabhu


Top
 Profile E-mail  
 
 Post subject: Re: Item Ledger Entry Table
PostPosted: Fri Jul 20, 2012 2:54 am 
Offline

Joined: Mon Jan 30, 2012 11:03 am
Posts: 61
Country: Singapore (sg)
Hi All,

If I filter all remaining qty left for all items doing show all, copy all records to excel. I dont see cost per unit on the item ledger entries so what i did was to take qty and cost amount, I divide and get cost per each unit and then i insert another column in excel and then take cost per unit times remaining qty to get the correct cost and when i sum up all the costs actual as per my calculation the item ledger entry doesnt talk to valuation report, valuation report shows one total and then item ledger entries shows one total.

i want to do a pivot table where what items are left, means remaining qty with costing, brands, item cat code, product grp code, and etc and when i sum up all the costs amounts, the total amount what i see must tally to valuation report.

Pls Help!

Regards
KH


Top
 Profile E-mail  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 12 posts ] 

All times are UTC + 1 hour [ DST ]


Who is online

Users browsing this forum: sudhakarsv and 25 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to: