Field AMOUNT in table CUSTOMER LEDGER ENTRY

pupricht

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

DenSter

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.

David_Singleton

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.

DenSter

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.

David_Singleton

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.

pupricht

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

pupricht

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

DenSter

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.