0

[SOLVED] NL Complicated Formula

Jet Reports Historic Posts

July 15, 2015 10:23

None

Follow

Hello,

For one of my reports I have to get a total for "Remaining Amt. (LCY)" from "Vendor Ledger Entry" table. But, each remaining amount has to be converted into another currency, using the exchange rate valid at the posting date of each open document…
Is there a way to put all these conditions into one single (complicated) Jet formula?

So far, I think it should resemble something like this:
=-NL("SUM","Vendor Ledger Entry","=NF(,""Remaining Amt. (LCY)"")/NL(""Last"",""Currency Exchange Rate"",""Relational Exch. Rate Amount"",""Starting Date"",NP(""DateFilter"",,NF(,""PostingDate""))","Document Type","Invoice|Credit Memo")
But of course that Excel could not evaluate the calculated field…

Any ideas? Or maybe it's mission impossible and I should stay with the old fashion way (rows and totals).

Thanks!
Elena

• Facebook
• Twitter
• LinkedIn

7 comments

• 0
  
  Jet Reports Historic Posts July 17, 2015 08:05

  So… the silence would mean "mission impossible"? :D

• 0
  
  Jet Reports Historic Posts July 29, 2015 07:56

  I think this is possible, but I don't have the resource to test your formula.
  
  The easiest way to do this is to sum al the different currencies separatly and than add a second column to convert them to the desired currency.
  
  Does this make sense to you?

• 0
  
  Jet Reports Historic Posts July 30, 2015 22:32

  Hi Elena,
  
  As you have found out, your specific function doesn't work because you are telling it to sum up the field named ""=NF(,""Remaining Amt. (LCY)"")/NL(""Last"",""Currency Exchange Rate"",""Relational Exch. Rate Amount"",""Starting Date"",NP(""DateFilter"",,NF(,""PostingDate""))" (or at least what that would evaluate to) which doesn't exist.
  
  As Danny mentioned, depending on the report, try to simplify the problem and have separate columns for what it is you are summing.

• 0
  
  Jet Reports Historic Posts July 31, 2015 11:32

  A different approach…
  
  The Vendor Ledger Entry table also has a "Remaining Amount" field. That is the remaining amount in the currency code for that entry. Now, I'm not sure if that is this is the currency you want to use, and if it holds the value against the exchange rate at the posting date even after applying other entries. So, you please check your data to see if it will work for you.
  
  Does this help?

• 0
  
  Jet Reports Historic Posts August 03, 2015 16:49

  Hi Elena,
  
  I thought I would play around with this a bit more because the more I looked at the, the more I thought it should work. I found you are missing a ) from your function which for me caused the error.
  
  =-NL("Sum","Vendor Ledger Entry","=NF(,""Remaining Amt. (LCY)"")/NL(""Last"",""Currency Exchange Rate"",""Relational Exch. Rate Amount"",""+Starting Date"",NP(""datefilter"",,NF(,""Posting date"")))","Document Type","Invoice|Credit Memo")
  
  After the Posting Date, there should be three closing parentheses , not just two. Took me a while to see that.

• 0
  
  Jet Reports Historic Posts August 06, 2015 06:35

  Thank you all for your feedback!
  
  danny bood - this is the way the report works at the moment - a column for the entries and one with the various currency exchange rates, with subtotals (EACH transaction uses the currency exchange rate from it's posting date, so I can't do any prior sums). The thing is that I have many transactions (it's a historical report, and the history is long) and I was hoping that replacing a whole list with a single formula would simplify the reports (and maybe run faster).
  
  hansfousert - for this report I need the remaining amount in a specific currency (no matter what the transaction's currency is), calculated at the exchange rate from the Posting Date. There is no place in NAV that I can take this info… just calculate it.
  
  JET-btr - I will try your suggestion with the extra bracket
  
  
  Elena

• 0
  
  Jet Reports Historic Posts August 06, 2015 07:30

  JET-btr - Success! :)
  
  Thank you so much!