class get month only from field date

(0) Share

Report

Posted on by murtafiah

497

i have script such as below :

tmpFixedAssetNew.JanuaryMST = (select sum(AmountMST) from assetTrans
where assetTrans.AssetId == assetBook.AssetId
&& assetTrans.BookId == assetBook.BookId
&& (assetTrans.TransType == AssetTransType::Depreciation
|| assetTrans.TransType == AssetTransType::DepreciationAdj)
&& mthOfYr(int TransDate) == 1).AmountMST;

but error with script this "mthOfYr(int TransDate) == 1"

why?

do you know it?

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (0)

Sohaib Cheema 49,438 User Group Leader on at

Like (0)

Report

Hi,

you cannot use x++ functions inside select statement.

try to create a customized view and then you can directly use where clause with specific column of month number or name.

Was this reply helpful? Yes No
murtafiah 497 on at

Like (0)

Report

hi mr sohaib,

i was change to be mthOfYr(AssetTrans.TransDate) == 1 but still error.

why?

:(

Was this reply helpful? Yes No
Suggested answer

Chaitanya Golla 17,225 on at

Like (0)

Report

Hi,

Use transdate as parameter instead of int TransDate, so it would be mthOfYr(TransDate) == 1.

Declare transdate before using it.

Refer following link:

(mthOfYr Function [AX 2012])

msdn.microsoft.com/.../aa603608.aspx

Was this reply helpful? Yes No
Sohaib Cheema 49,438 User Group Leader on at

Like (0)

Report

as I said, previously you will.

because where clause would not allow you use of illegal expressions such as x++ functions around a field table.

create a view and use that as table.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
I think you have to filter with range instead using mthOfYr by using start date and end date.

how about like this?

tmpFixedAssetNew.JanuaryMST = (select sum(AmountMST) from assetTrans where assetTrans.AssetId == assetBook.AssetId && assetTrans.BookId == assetBook.BookId && (assetTrans.TransType == AssetTransType::Depreciation || assetTrans.TransType == AssetTransType::DepreciationAdj) && assetTrans.TransDate >= startDate && assetTrans.TransDate <= endDate).AmountMST;

where the startDate and endDate is from the month that you want to filter.

Was this reply helpful? Yes No
murtafiah 497 on at

Like (0)

Report

Hi Mr. Yonathan,

i want show sum(amountMST) only january or february to be 2 column is january and february.

How?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Sorry Murtafiah, I am not barely understand your question, please be more specific and clear.

Was this reply helpful? Yes No
Chaitanya Golla 17,225 on at

Like (1)

Report

Hi,

As Yonathan stated, you can use daterange(i.e start date as 1/1/2017 and end date as 1/31/2017) to get the values for January. If you want to get values for February you can loop the same query by changing the values of start date and end date.

Was this reply helpful? Yes No
murtafiah 497 on at

Like (0)

Report

Hi all,

My problem was solve,

thanks all, for the support

:)

Was this reply helpful? Yes No