Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
i have a problem i cant resolve on my own, hoping for help.
Situation is i have 3 tables, that are connected by:
Sales Line - has all information regarding the sales
connected via "Billing Number"
Sales Header - has the "currency code" value (for the Billing number)
connected via "currency code"
Currency on Day - has all 4 currencys (EUR,CAD,USD,CHF) and their value per Day (f. ex. 01.09.2015 has one value for that day for every currency)
I tried to connect the Currency on Day table via Day(Date) and currency Code. But i cant, because there would be loops.
The table i need looks like this:
Billing Number - Billing Date - Currency Code - Amount (expression= sum(neto))
I have it and it works fine, but now we started to sell stuff in foreign currency and my Database(Navision) gives me only the foreign currency amount and thats wrong for my reports.
My Question: What do i need to write into the Expression for Amount if i want to get the right numbers?
I want the expression to check the currency Code (also can be blank) and calculate = sum(neto) * Currency on Day(Billing Date) IF the currency code is NOT Blank.
I appreciate any help.
Thank you.
Kristian
hi
attach is a demo that works
but as i use aggr functions , it might cause performance issues
if you have a lot of data , if you can't join header and lines may be
you can add the currency field to the lines by left join and then work with a direct key to the currency table
hi
my first step would have been to merge the header and lines table to one table
this shouldn't create any data integrity issues
then you would be able to create the date¤cy code key
and work with him
Hi Liron,
ty for your answer. The problem is i never did this in the Start (should have..). To change this now, would be a huge amount of work rewriting almost every module and stats i finished.
This can be done earliest in summer. I need now a possibility somehow to get the numbers i need via expressions. If its possible of course.
so let me see if i understand the situation
currently you have the for any line\amount
the date is part of the line
we know the currency code through the connection to header
so the connection between the line and currency table display the currency rate for one coin for all the dates
so your expression should be something like
sum(amount)* if(CurrencyDate=BillDate,CurrencyRate)
The currency on day table - has Billdate, Exchange rate value and Currency
So basically yeah, the table has to show me every line and if currency code something else then blank, it has to get the exchange rate for this day in this currency and calculate.
can you upload a sample data
all i need the relevant fields from the 3 tables with one row of dummy data
Assuming the following:
SalesLine:
LOAD [Billing Number], // Assume [Billing Number]/[LineNo] is unique...
[LineNo],
neto,
...
SalesHeader:
LOAD [Billing Number], // Assume [Billing Number] is unique...
[Currency Code],
Day,
...
CurrencyOnDay:
LOAD Day, // Assume [Day] is unique...
EUR,
CAD,
USD,
CHF
...
Then add the code:
T_Convert:
NoConcatenate
LOAD [Billing Number],
LineNo,
neto
Resident SalesLine;
Join (T_Convert)
LOAD [Billing Number],
[Currency Code],
Day
Resident SalesHeader;
Join (T_Convert)
LOAD Day,
EUR,
CAD,
USD,
CHF
Resident CurrencyOnDay;
Join (SalesLine)
LOAD [Billing Number],
LineNo,
neto * Pick(Match([Currency Code], 'EUR', 'CAD', 'USD', 'CHF'), EUR, CAD, USD, CHF) As netoUSD,
Pick(Match([Currency Code], 'EUR', 'CAD', 'USD', 'CHF'), EUR, CAD, USD, CHF) As ExchRate
Resident T_Convert;
DROP Table T_Convert;
netoUSD contains the neto amount converted to USD. Note that the conversion may be division depending on how the rates are recorded, or you may want to load the rates all divided by USD (or the inverse), again depending on what the rates look like. The USD - USD rate should always be 1.
Here you go. If there is no "Currency" label, then its just sum(amount).
hi
attach is a demo that works
but as i use aggr functions , it might cause performance issues
if you have a lot of data , if you can't join header and lines may be
you can add the currency field to the lines by left join and then work with a direct key to the currency table