8 Replies Latest reply: Sep 10, 2015 8:18 AM by Liron Baram RSS

    Get the currency value i need

    Kristian König

      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

        • Re: Get the currency value i need
          Liron Baram

          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&currency code key

          and work with him

            • Re: Get the currency value i need
              Kristian König

              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.

                • Re: Get the currency value i need
                  Liron Baram

                  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)

                    • Re: Get the currency value i need
                      Kristian König

                      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.

                        • Re: Get the currency value i need
                          Liron Baram

                          can you upload a sample data

                          all i need the relevant fields from the 3 tables with one row of dummy data

                          • Re: Get the currency value i need
                            Jonathan Dienst

                            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.