Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 305 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 260 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Balance grouped by Document Currency
toLLto
Member Posts: 34
Hello NAV Experts,
I need some advice.
We need to calculate balances grouped by currency code (for each account). As GL entries are always in LCY (no info about document currency), my idea was to search for currency in other ledger tables (Customer Ledger, Vendor Ledger, Bank Acc. Ledger).
So I go through all GL Entries. Depending on field "Source Type" I look in other ledger tables and try to find any entry by actual document no. When I find documents in foreign currency I calculate exchange rate and GL Entry amounts in that currency.
Example result:
It works almost good. When I sum net change in LCY it's balanced (sum=0). The same should happen when I sum all EUR net change amounts. Unfortunelly I have here some differences and I try to figure it out.
My question - is this right direction what I'm doing? Do you have some other ideas how to approach this? How would you do that?
I need some advice.
We need to calculate balances grouped by currency code (for each account). As GL entries are always in LCY (no info about document currency), my idea was to search for currency in other ledger tables (Customer Ledger, Vendor Ledger, Bank Acc. Ledger).
So I go through all GL Entries. Depending on field "Source Type" I look in other ledger tables and try to find any entry by actual document no. When I find documents in foreign currency I calculate exchange rate and GL Entry amounts in that currency.
Example result:
Acc.No. Doc. Curr. Start balance End balance Net change LCY Start balance End balance Net change (LCY) 139-001 EUR -93 634,92 -205 785,29 -112 150,37 PLN -506 755,03 -1 006 755,02 -499 999,99 139-001 PLN 506 755,00 1 006 755,00 500 000,00 PLN 506 755,00 1 006 755,00 500 000,00
It works almost good. When I sum net change in LCY it's balanced (sum=0). The same should happen when I sum all EUR net change amounts. Unfortunelly I have here some differences and I try to figure it out.
My question - is this right direction what I'm doing? Do you have some other ideas how to approach this? How would you do that?
0
Comments
IMO, you won't get a satisfying result this way. When I understood this right, you want the transaction currency and amount in the original currency for all G/L accounts. You can try to pull them together by document no., source type, transaction no. and so on. This will give you the originating currency, but not the FCY amount. For this you would need to calculate, using the currency exchange rates table.
There are several problems with this:
1. The exchange rates are still editable. So, the FCY amounts are a guess, not more.
2. Rounding problem. This can't be mitigated as it is possible in NAV to post mixed-currency postings. Therefore a balance (FCY) is of little value, and won't be overall balanced.
You could also use this Add-On to retain the transaction currency and amounts. Then you will have the original amounts (and resulting exchange rates), and a few other nice features. A trial balance with currency is also included.
with best regards
Jens Glathe