Navision vs Sql Server table data

charles.roos

hi,

i created sql query:

select * from dbo."IT 05_nov 2007$Vendor Ledger Entry"
where "Vendor No_" = 'DELL' and "Document Type"=2
and "document No_" = 'OA01942'

Output:


timestamp Entry No_ Vendor No_ Posting Date Document Type Document No_ Description Currency Code Purchase (LCY) Inv_ Discount (LCY) Buy-from Vendor No_ Vendor Posting Group Global Dimension 1 Code Global Dimension 2 Code Purchaser Code User ID Source Code On Hold Applies-to Doc_ Type Applies-to Doc_ No_ Open Due Date Pmt_ Discount Date Original Pmt_ Disc_ Possible Pmt_ Disc_ Rcd_(LCY) Positive Closed by Entry No_ Closed at Date Closed by Amount Applies-to ID Journal Batch Name Reason Code Bal_ Account Type Bal_ Account No_ Transaction No_ Closed by Amount (LCY) Document Date External Document No_ No_ Series Closed by Currency Code Closed by Currency Amount Adjusted Currency Factor Original Currency Factor Remaining Pmt_ Disc_ Possible Pmt_ Disc_ Tolerance Date Max_ Payment Tolerance Accepted Payment Tolerance Accepted Pmt_ Disc_ Tolerance Pmt_ Tolerance (LCY) Amount to Apply IC Partner Code Applying Entry Reversed Reversed by Entry No_ Reversed Entry No_ Prepayment Reference No_

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

----

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

0x000000000008554C 573306144 DELL 2003-06-26 00:00:00.000 2 OA01942 Tellimus OT00220 USD -22656.37000000000000000000 0.00000000000000000000 DELL IMP AARE OSTUD 0 0 2003-06-26 00:00:00.000 2003-06-26 00:00:00.000 0.00000000000000000000 0.00000000000000000000 0 573357376 2003-12-31 00:00:00.000 -1063.57000000000000000000 0 4331 -14326.18000000000000000000 2003-06-26 00:00:00.000 271023875 OARVE USD -1063.57000000000000000000 0.07916076918936205920 0.07423960088790562660 0.00000000000000000000 1753-01-01 00:00:00.000 0.00000000000000000000 0.00000000000000000000 0 0.00000000000000000000 0.00000000000000000000 0 0 0 0 0

(1 row(s) affected)



Then i filtered out that record in Navision:
Vendor No. Posting Date Amount Amount (LCY) Currency Code Document Type Description Document No. Debit Amount (LCY) Credit Amount (LCY) Debit Amount Entry No. Purchase (LCY) Inv. Discount (LCY) Buy-from Vendor No. Vendor Posting Group Employee/LOB Code Worktype/Accountmanager Code Purchaser Code User ID Source Code On Hold Applies-to Doc. Type Applies-to Doc. No. Open Due Date Pmt. Discount Date Original Pmt. Disc. Possible Pmt. Disc. Rcd.(LCY) Positive Closed by Entry No. Closed at Date Closed by Amount Applies-to ID Journal Batch Name Reason Code Bal. Account Type Bal. Account No. Transaction No. Closed by Amount (LCY) Document Date External Document No. No. Series Closed by Currency Code Closed by Currency Amount Adjusted Currency Factor Original Currency Factor Remaining Pmt. Disc. Possible Pmt. Disc. Tolerance Date Max. Payment Tolerance Accepted Payment Tolerance Accepted Pmt. Disc. Tolerance Pmt. Tolerance (LCY) Amount to Apply IC Partner Code Applying Entry Reversed Reversed by Entry No. Reversed Entry No. Prepayment Reference No. Remaining Amount Original Amt. (LCY) Remaining Amt. (LCY) Credit Amount Original Amount
DELL 26.06.03 -1 682,00 -22 656,37 USD Invoice Tellimus OT00220 OA01942 0,02 22 656,38 573306144 -22 656,37 0,00 DELL IMP AARE OSTUD No 26.06.03 26.06.03 0,00 0,00 No 573357376 31.12.03 -1 063,57 G/L Account 4331 -14 326,18 26.06.03 271023875 OARVE USD -1 063,57 0,079160769189362 0,074239600887906 0,00 0,00 0,00 No 0,00 0,00 No No No 0,00 -22 656,37 0,00 1 682,00 -1 682,00
[/b]


I want to find how to retrieve with sql the "Vendor Ledger Entry".Amount=-1 682,00 field value. I don't see Amount field in sql-table, also i don't see value -1 682,00 there. Where is that Amount value there in sql-table?

kriki

[Topic moved from Navision forum to SQL General forum]

This is because it is a flowfield. This is a virtual field that is calculated on-the -fly by Navision.
You would need to query the SIFT-table (or the SIFT-view in 5.0SP1).
Maybe it is easier (the structure of the SIFT-table/view can easily change or disappear when doing some performance-tuning on SQL) to query directly the table+field that is read to calculate the flowfield.

ara3n

you need to do a join on Detailed Vendor Ledg. Entry. Where
DetailedVndLedger."Vendor Ledger Entry No." = Vendorledger."Entry No."
and
DetailedVndLedger."Entry Type" =
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)'


And you will need to sum the DetailedVndLedger.Amount field because it's one to many relationship.