Setrange or Setfilter to the nearest date

Business Central/NAV Developers Forum
1

hi all… i want to ask how to Setrange or Setfilter to the nearest date (for Currency Exchange Rate)

for example we input currency at

1 November = 1000

8 November = 1100

So if the transaction created at 2 November, it should be using 1000

how to setrange that?

like we want to convert from amount to amount (LCY), where we can find that code in NAV ?

thanks for the information

2

If you want to convert an amount from a foreign currency into the local currency, you can call the function ExchangeAmtFCYToLCY that you find in the Curreny Exchange Rate table (330).

If you want to find an example of its use, you can see the OnValidate trigger of the Outstanding Amount field of Sales Line table (37).

Marco

3

Hi Stan,

Dunno if this is the smoothest way to obtain your filter task, but it should work

IF ExchRateRec.GET(MyCurrCode,PostDate) THEN BEGIN
// There is an exchange rate on the actual date, and you use that one…
END ELSE BEGIN
// You’ll now gradually extend your daterange by 1 day, until you find an exchange rate to use.
ExchRateRec.SETRANGE(“Currency Code”,MyCurrCode);
FilterFromDate := PostDate;
FilterToDate := PostDate;
FOR i := 1 TO 100 DO BEGIN // 100 is the max number of days you can extend the daterange.
FilterFromDate := CALCDATE(’<-1D>’,FilterFromDate);
FilterToDate := CALCDATE(’<1D>,FilterToDate);
ExchRateRec.SETRANGE(“Starting Date”,FilterFromDate,FilterToDate);
IF ExchRateRec.FINDSET(FALSE,FALSE) THEN BEGIN
// You’ve now found the exchange rate with the starting date closest to the posting date.
// NOTE! If you’ve got 2 exchange rates within the daterange, using FINDSET will always give you the earliest on,
// whereas if you want to latest one, you have to use FINDLAST
END;
END;
END;

4

how about

ExchangeRate.SETRANGE(“Starting date”,01010000,PostDate);
ExchaneRate.FINDLAST;
5

Wouldn’t this give him the last record before posting date?
I understood that he wanted the closet one, no matter if it’s before or after posting date.
So if the posting date is nov. 7., the rate used should be 1100.

6

That would be pretty bad business logic, so I assumed it was a language issue. But if he really wanted the nearest, then I ould use

ExchangeRate.RESET;
ExchangeRate.“Starting date” := PostDate;
If ExchangeRate.FIND(‘=’) then
ClosestDate := ExchangeRate.“Starting date”
else begin
Closestdate := 01010000D;
if ExchangeRate.FIND(‘<’) then
ClosestDate := ExchangeRate.“Starting date”;
if ExchangeRate.FIND(‘>’) then
if (postdate - ClosestDate) > ExchangeRate.“Starting date” - postdate then
ClosestDate := ExchangeRate.“Starting date”;
end;

This only does three hits on the database.

But I can’t see the logic in wanting the nearest, in exchange rates you always want the last one prior to or equal to the current date.

7

OK, so a smoother way than mine did exist… [:D]

I agree with u David, that it’s not logical to use the nearest in exchange rates.
The name of the date field (Starting Date) should give a hint that u want the last before.