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
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
Set the Property of the Field and point your view Method Written above.
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
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
Hi Diliraj,
Please see the blog:
community.dynamics.com/.../exchange-rate-calculation-in-computed-column-of-view
Does it meet your requirement?
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
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
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
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 290,802 Super User 2024 Season 2
Martin Dráb 229,129 Most Valuable Professional
nmaenpaa 101,154