Item Ledger Entry Table

kwajahathydro
kwajahathydro Member Posts: 88
edited 2012-07-20 in NAV/Navision Classic Client
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

Comments

  • udayrmer
    udayrmer Member Posts: 171
    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
  • Chinmoy
    Chinmoy Member Posts: 359
    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
    bbrown Member Posts: 3,268
    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.
  • udayrmer
    udayrmer Member Posts: 171
    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
  • kwajahathydro
    kwajahathydro Member Posts: 88
    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
    Chinmoy Member Posts: 359
    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 Member Posts: 171
    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
  • David_Singleton
    David_Singleton Member Posts: 5,479
    Create a view that joins the Item Ledger Entry to the Value Entry table.
    David Singleton
  • kwajahathydro
    kwajahathydro Member Posts: 88
    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
    sprabhucppl Member Posts: 58
    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
    kwajahathydro Member Posts: 88
    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

Categories