mibuso.com
Home› SQL General
Options

Field AMOUNT in table CUSTOMER LEDGER ENTRY

puprichtpupricht Member Posts: 9
edited 2012-02-28 in SQL General
Dear mibusos,

we are just migrating from Navision 4.x (Native Database) to Microsoft Dynamics NAV 2009 (MSSQL)
It's up to me to adjust some small interface routines written in VB.....

The original source code against the native databse uses:
SELECT Entry_No_, ...... , Amount FROM Cust__Ledger_Entry..... WHERE .....

to retrieve the required data.
(lots of underlines due to the "Identifiers" Option of the ODBC-Driver is set to "A-Z,0-9,_")

When I try to run the adapted statement against the MSSQL-Server
SELECT [Entry No_], ......, Amount FROM [dbo].[Cust_ Ledger Entry]..... WHERE .....

this leeds to error because the amount-field is unknown.
Amount seems to be some kind of a virtual field in Navision 4.0, that can be seen (and selected!!!) in Navision 4.0 but cannot in NAV 2009?

Confused asking for help

Peter

Answers

  • Options
    DenSterDenSter Member Posts: 8,304
    That field is a flowfield into the detailed customer ledger entry table. Flowfields are calculated at runtime. To get to the value in SQL Server you'll need to figure out the relationship between the customer ledger entry and the detailed ledger entry, and join those two based on that relationship.
    Daniel Rimmelzwaan
    RIS Plus, LLC
    MVP - Business Apps
  • Options
    David_SingletonDavid_Singleton Member Posts: 5,479
    pupricht wrote:
    Amount seems to be some kind of a virtual field in Navision 4.0, that can be seen (and selected!!!) in Navision 4.0 but cannot in NAV 2009?

    No that is incorrect. The change was made form Ver 2.60 to 3.01 so if you have amount in the CLE then you must have 2.60 objects. Or maybe some customization was made to calc the sum from the Detail entry and populate the CLE.

    Best is to fix it the way Daniel suggests.
    David Singleton
  • Options
    DenSterDenSter Member Posts: 8,304
    David Singleton wrote:
    you must have 2.60 objects
    Not saying this is not correct, but the OP said they are moving from 4.0 Native to 2009 SQL. My guess would be that they are using the NAV ODBC driver to access the native database, so in that case it would make sense that the Amount field would indeed be available in that query going into the Native database, and not in the SQL Server database.
    Daniel Rimmelzwaan
    RIS Plus, LLC
    MVP - Business Apps
  • Options
    David_SingletonDavid_Singleton Member Posts: 5,479
    DenSter wrote:
    David Singleton wrote:
    you must have 2.60 objects
    Not saying this is not correct, but the OP said they are moving from 4.0 Native to 2009 SQL. My guess would be that they are using the NAV ODBC driver to access the native database, so in that case it would make sense that the Amount field would indeed be available in that query going into the Native database, and not in the SQL Server database.

    Yes you are right.

    I was only looking at the statement of 4.00->2009 which I now see is irrelevant. Its of course the change from ODBC to SQL that made the difference.
    David Singleton
  • Options
    puprichtpupricht Member Posts: 9
    Thank you all,

    looking at your replies, everything seems to be so obvious.
    But I've never (let's say very seldom) seen Navision from the "normal" users application view.
    (I even don't have a navision client installed on my system)

    Coming from a german version (with the ODBC driver set to german) it's difficult for me even to find out all the english table and fieldnames now........

    Yes Daniel, it was the ODBC driver doing the calculations for me and pretending a real amount field.
    Well, I hope a join and a sum will do the same job soon....thank you.

    At the moment there is only one difficulty left to finish the conversions....
    It is related to the option-fields in NAVISION but I will post a new topic for this


    Thanks again

    Peter
  • Options
    puprichtpupricht Member Posts: 9
    To those who saved me a lot of searching and guessing here.....

    ....perhaps you may give me helping hand again?
    My hopefully last stumbling block before finishing the adaptation can be found at:

    "Is there an "Option-Field-Translation-Table"?"
    http://www.mibuso.com/forum/viewtopic.php?f=33&t=52061

    Peter
  • Options
    DenSterDenSter Member Posts: 8,304
    To save you a little time, the Amount field in the "Cust. Ledger Entry" table is a SUM on the "Amount" field in the "Detailed Cust. Ledg. Entry" table, linked by a number of fields:

    "Cust. Ledger Entry No." equals the "Entry No."

    "Entry Type" filtered on option values
    Initial Entry|Unrealized Loss|Unrealized Gain|Realized Loss|Realized Gain|Payment Discount|'Payment Discount (VAT Excl.)'|'Payment Discount (VAT Adjustment)'|Payment Tolerance|Payment Discount Tolerance|'Payment Tolerance (VAT Excl.)'|'Payment Tolerance (VAT Adjustment)'|'Payment Discount Tolerance (VAT Excl.)'|'Payment Discount Tolerance (VAT Adjustment)'

    "Posting Date" filtered by the value of the FlowFilter value stored in the "Date Filter", which is an internal mechanism that dictates the value of flowfields. It is linked to the "Posting Date" in both tables, but you'll have to work with a NAV developer to have them explain this to you in more detail.
    Daniel Rimmelzwaan
    RIS Plus, LLC
    MVP - Business Apps
Sign In or Register to comment.