Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Bringing Exchange Rate...
Finance | Project Operations, Human Resources, ...
Answered

Bringing Exchange Rate in Computed Column in a View

(0) ShareShare
ReportReport
Posted on by Dilliraj Pudi Dasar... 86

Hi, 

I am trying to Bring Exchange Rate for the Transaction through Computed Column in View, 

Code of the Computed Method is 

static client server str getTransactionExchangeRate()
{
ExchRate exchRate;
changecompany(curExt())
{
//exchRate = ExchangeRateHelper::getExchangeRate1_Static(Ledger::current(),
//SysComputedColumn::returnField(tableStr(TestCommissionSaleslineView),identifierStr(SalesLine),fieldStr(SalesLine,CurrencyCode)),
//DateTimeUtil::date(str2Datetime(SysComputedColumn::returnField(tableStr(TestCommissionSaleslineView),identifierStr(SalesLine),fieldStr(SalesLine,CreatedDateTime)),123)));

exchRate = ExchangeRateHelper::exchRate(SysComputedColumn::returnField(tableStr(TestCommissionSaleslineView),identifierStr(SalesLine),fieldStr(SalesLine,CurrencyCode)),
DateTimeUtil::date(str2Datetime(SysComputedColumn::returnField(tableStr(TestCommissionSaleslineView),identifierStr(SalesLine),fieldStr(SalesLine,CreatedDateTime)),321)));

}
return strFmt('%1',exchRate);
}

I tried the GetExchangeRate1_Static which was throwing a different error then i tried the normal one. 

Am i missing anything. 

I facing this error why doing Synchronization of View 

Infolog diagnostic message: Infolog diagnostic message: 'The accounting currency has not been specified for ledger dat, and is required to retrieve exchange rate information.' on category 'Error'.
Unexpected error occurred while execute computed column method: Unexpected error occurred while execute computed column method: getTransactionExchangeRate on view/ data entity TestCommissionSaleslineView Microsoft.Dynamics.Ax.Xpp.ErrorException
at Dynamics.AX.Application.ExchangeRateCalculation.`handleError(String _error, String _faultCode) in xppSource://Source/Currency\AxClass_ExchangeRateCalculation.xpp:line 676
at Dynamics.AX.Application.ExchangeRateCalculation.handleError(String _error, String _faultCode)
at Dynamics.AX.Application.ExchangeRateHelper.`defaultAndValidateToCurrency() in xppSource://Source/Ledger\AxClass_ExchangeRateHelper.xpp:line 54
at Dynamics.AX.Application.ExchangeRateHelper.defaultAndValidateToCurrency()
at Dynamics.AX.Application.ExchangeRateCalculation.`getExchangeRate1() in xppSource://Source/Currency\AxClass_ExchangeRateCalculation.xpp:line 353
at Dynamics.AX.Application.ExchangeRateCalculation.getExchangeRate1()
at Dynamics.AX.Application.ExchangeRateHelper.`exchRate(String _currencyCode, Date _exchRateDate, UnknownNoYes _exchRatesTriangulation, Boolean _showerror, NoYes _governmentExchRate, Boolean @_exchRateDate_IsDefaultSet, Boolean @_exchRatesTriangulation_IsDefaultSet, Boolean @_showerror_IsDefaultSet, Boolean @_governmentExchRate_IsDefaultSet) in xppSource://Source/Ledger\AxClass_ExchangeRateHelper.xpp:line 385
at Dynamics.AX.Application.ExchangeRateHelper.exchRate(String _currencyCode, Date _exchRateDate, UnknownNoYes _exchRatesTriangulation, Boolean _showerror, NoYes _governmentExchRate, Boolean @_exchRateDate_IsDefaultSet, Boolean @_exchRatesTriangulation_IsDefaultSet, Boolean @_showerror_IsDefaultSet, Boolean @_governmentExchRate_IsDefaultSet)
at Dynamics.AX.Application.ExchangeRateHelper.exchRate(String _currencyCode, Date _exchRateDate)
at Dynamics.AX.Application.TestCommissionSaleslineView.`getTransactionExchangeRate() in xppSource://Source/Test\AxView_testCommissionSaleslineView.xpp:line 35
at Dynamics.AX.Application.TestCommissionSaleslineView.getTransactionExchangeRate()
at TestCommissionSaleslineView::getTransactionExchangeRate(Object[] , Boolean& )
at Microsoft.Dynamics.Ax.Xpp.ReflectionCallHelper.MakeStaticCall(Type type, String MethodName, Object[] parameters)
at Microsoft.Dynamics.AX.DataAccess.StatementGenerator.UnboundField.get_ComputedValue()
Database synchronization failed.

One more doubt i have is Why Computed Column it always expects the static method to return string. (str)  then what is the user other type of ComputeColumsn where as the returns types should match 

Any Advise and help, thanks

Dilliraj

  • Verified answer
    Dilliraj Pudi Dasarathan Profile Picture
    Dilliraj Pudi Dasar... 86 on at
    RE: Bringing Exchange Rate in Computed Column in a View

    Finally I achieved it. These are the Steps to achieve it. Thought it would be useful for someone who are in need.

    1.  Create 3 methods  in Extension Class of SysComputedColumn

    a.  Create the Method like this in Extension Class of SysComputedColumn

    . public static server str getExchangeRateType()

       {

           str expression;

           expression = 'select DEFAULTEXCHANGERATETYPE from LEDGER where PRIMARYFORLEGALENTITY = ' +

               int642Str(CompanyINfo::find().RecId);

           return expression;

       }

    Explanation of Method A it returns the ExchangeRangeType Recid for the Default Company you are in .

    b. Another Method like this

    public static server str getAccountingCurrency()

       {

           str expression;

           expression = 'select ACCOUNTINGCURRENCY from LEDGER where PRIMARYFORLEGALENTITY = ' +

               int642Str(CompanyINfo::find().RecId);

           return expression;

       }

    Explanation of Method B it returns the AccountingCurrency  for the Default Company and the Ledger which is active  in the company which you pull the data in

    c. Another Method like this.

    public static server str getExchangeRate(TableName _viewName,

                                           str FromCurrencyCodeDS,

                                           str FromCurrencyCodeField,

                                           str TransactionDate)

       {

           str expression;

           expression = 'select top 1 Round(ExchangeRate1/100,2) from ExchangeRateEffectiveView EREV where EREV.FromCurrencyCode = ' +

           SysComputedColumn::returnField(_viewName,

           FromCurrencyCodeDS,

           FromCurrencyCodeField) +

           ' and EREV.ToCurrencyCode = ' +

           SysComputedColumn::getAccountingCurrency() +

           ' and EREV.EXCHANGERATETYPE in (' +

           SysComputedColumn::getExchangeRateType() +')'+

          ' and( EREV.ValidFrom < '+

               SysComputedColumn::returnField(_viewName,

           FromCurrencyCodeDS,

           TransactionDate) +

          ' and EREV.ValidTo > '+

               SysComputedColumn::returnField(_viewName,

           FromCurrencyCodeDS,

           TransactionDate) +')'+

           ' Order by EREV.ValidFrom desc';

    Explanation of Method C, Contains 3 Parameters.

    with ViewName , Datasource1 inside the View and Datasource1 currency field and Datasource1 TransactionDateTime Field inside the view

    2. Create a Method in your  View or DataEntity.

    static client server str getTransactionExchangeRate()

    {

    return SysComputedColumn::getExchangeRate(tableStr(TestCommissionSaleslineView),

    identifierStr(SalesLine),

    fieldStr(SalesLine,CurrencyCode),

    fieldStr(SalesLine,CreatedDateTime));

    }

    3. In your View Create a

    pastedimage1589390350726v1.png

    Set the Property of the Field and point your view Method Written above.

    pastedimage1589390389647v2.png

    Build your Project. Synchronise your Project so the View in your Project is Recreated in the Database with this new field.

    You will get the  Exchange Rate for the Transaction you did on that Date.

    I hope you will like it.  If it works for you .

    Please LIke my Answer ,

    Thanks

    Dilliraj

  • Dilliraj Pudi Dasarathan Profile Picture
    Dilliraj Pudi Dasar... 86 on at
    RE: Bringing Exchange Rate in Computed Column in a View

    Hi Will,

    I thought of Same Idea , but it is not that simple as explain inthe Post.

    We need to have multiple SysComputedColumn Methods defined.

    1. find the Ledger Records and Get AccountingCurrency and DefaultExchangeRatetype

    2. Query ExchangeRateEffectiveView  to pass the above 2 and the Transaction Currency and Date to filter in ValidFrom and ValidTo  to get the ExchangeRate  will get the actual Record.

    To Thank you Above Example you have given a similar kind with the advanced steps will help to achieve to get the exchange Rate.

    Thanks

    Dilliraj

  • WillWU Profile Picture
    WillWU 22,350 on at
    RE: Bringing Exchange Rate in Computed Column in a View

    Hi Diliraj,

    Please see the blog:

    community.dynamics.com/.../exchange-rate-calculation-in-computed-column-of-view

    Does it meet your requirement?

  • Dilliraj Pudi Dasarathan Profile Picture
    Dilliraj Pudi Dasar... 86 on at
    RE: Bringing Exchange Rate in Computed Column in a View

    Is it possible to use ExchangeRateEffectiveView and build a SQL Expression to get the exchange rate via Computed column. I should try this.

    thanks

    Dilliraj

  • Dilliraj Pudi Dasarathan Profile Picture
    Dilliraj Pudi Dasar... 86 on at
    RE: Bringing Exchange Rate in Computed Column in a View

    Dear Nikolaos,

    Thanks for your reply , Can you Tell me how i can achieve to get Exchange Rate used for the Transaction on that Date in a View.

    Am sure someone could have had this requirement before.

    Thanks

    Dilliraj

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,156 on at
    RE: Bringing Exchange Rate in Computed Column in a View

    Computed column is not the same thing as a display method. In display method you have x++ code that is executed in runtime and that retrieves the value that you want to show.

    In computed column the logic is executed in db synchronization time, and your code should actually return a T-SQL query which fetches the value that your computed column should should return.

    During runtime, computed columns are executed in SQL Server, not inside Dynamics, so in runtime there's no x++ execution in them.

    docs.microsoft.com/.../walkthrough-add-a-computed-column-to-a-view

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans