Item Ledger Entry Table

kwajahathydro

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

udayrmer

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

Chinmoy

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

bbrown

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

udayrmer

If you want Item ledger entries in excel then from Item ledger entry table, export to excel

kwajahathydro

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

Chinmoy

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

udayrmer

udayrmer wrote:

If you want Item ledger entries in excel then from Item ledger entry table, export to excel

Item Ledger Entry Table

David_Singleton

Create a view that joins the Item Ledger Entry to the Value Entry table.

kwajahathydro

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

sprabhucppl

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

kwajahathydro

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