Problem Using string Variables in Select Statement within Methods

Finance and Operations/AX Developer Forum
1

Hi guyz am new in AX and am trying to work on a Localized payroll and am currently experiencing some problem with the select statement below

while select GetPeriodTrans where GetPeriodTrans.EmployeeCode == hremployee.EmplId &&
GetPeriodTrans.TransactionCode == “_transCode” &&
GetPeriodTrans.PeriodMonth == _intMonth && GetPeriodTrans.PeriodYear == _intYear
{
TransAmount = GetPeriodTrans.Amount;
}

if i leave out _tranCode am able to get records however if i include _transCode, the select statement is doesn’t

return any records. I have confirmed that the _transCode which is a parameter in the method has a value.

Is there another way of dealing with string parameters as use them in select statements?

Any Help??

2

If _transCode is a variable - directly assign it don’t put it in " "(quotes).

3

if i try that am getting the error below.

container and unbounded string (text) fields are not allowed in
a WHERE expression

this is the entire function that am using

Real fnGetTransAmount(EmplTable hrEmployee,str _transCode, int _intMonth,int _intYear)
{
Real TransAmount;
prPeriodTransactions GetPeriodTrans;

while select GetPeriodTrans where GetPeriodTrans.EmployeeCode == hremployee.EmplId &&
GetPeriodTrans.TransactionCode == “_transCode” &&
GetPeriodTrans.PeriodMonth == _intMonth && GetPeriodTrans.PeriodYear == _intYear
{
TransAmount = GetPeriodTrans.Amount;
}
}
Return TransAmount;
}

4

HI Kimmi,

TransactionCode is the enum field in the table GetPeriodTrans

If it is of enum type then convert as

GetPeriodTrans.TransactionCode == str2enum(EnumType, _transCode)

Naresh Kolli

5

Hi naresh

transcode is a string. I have confirmed it has the correct value but when i use it the select statement doesn’t return

any value

6

Hi Kimmi

What i was saying is ,in the table GetPeriodTrans the field TransactionCode is of type enum.When u compare the enum type field with the string it will show the erroe as what you said…Check the below example you can understand when comparing the enum field with the string.

static void str2enum(Args _args)
{
str s = ‘cust’;
LedgerJournalTrans ledgerJournalTrans;
;
select ledgerJournalTrans
where ledgerJournalTrans.AccountType == s;
}
Error message is
Container and unbounded sring(text) fields are not allowed in a where expression.

In the same job if u write the select statement as below then it will execute

select ledgerJournalTrans
where ledgerJournalTrans.AccountType == str2enum(AccountType,s);

Here what i means to say is that in your case you comparing two differnet data types…so it is howing the error…

This is what my understading from the problem…

Naresh Kolli

7

Naresh

Thanks for the reply. However I have confirmed that these two variables am comparing are both string

GetPeriodTrans.TransactionCode == “_transCode” &&

problem is someone was trying to tell me to get rid of the quotes ( " ") on the variable _transCode thats basically why i was getting this error container and unbounded string (text) fields are not allowed in a WHERE expression

to my main problem on my while select statement when i use the “_transCode” variable the statement doesn’t return any records and i have ascertained that there is a record in the table with the TransactionCode.

what could i be doing wrong in my while statement in the function below?

Real fnGetTransAmount(EmplTable hrEmployee,str _transCode, int _intMonth,int _intYear)
{
Real TransAmount;
prPeriodTransactions GetPeriodTrans;

while select GetPeriodTrans where GetPeriodTrans.EmployeeCode == hremployee.EmplId &&
GetPeriodTrans.TransactionCode == “_transCode” &&
GetPeriodTrans.PeriodMonth == _intMonth && GetPeriodTrans.PeriodYear == _intYear
{
TransAmount = GetPeriodTrans.Amount;
}
}
Return TransAmount;
}

8

Hi Kimmi,

everything in the method looks fine…

Just test it by wrting the same code in the job and pass the values which in the table,then can judge values in the table are wrong or method is not fetching the values.

while select GetPeriodTrans where GetPeriodTrans.EmployeeCode == ‘00001’&&
GetPeriodTrans.TransactionCode == ‘Trans001’ &&
GetPeriodTrans.PeriodMonth == 6 && GetPeriodTrans.PeriodYear == 2011
{
TransAmount = GetPeriodTrans.Amount;
}

Naresh Kolli

9

Have done the testing in the same function by replacing the _transCode with the actual value ‘BPAY’ and i was able to fetch the

record.

So now am sure the problem is the _transCode Variable but I just can’t pinpoint what’s the problem with it.

10

Don’t declare _ transCode variable as String in variable declaration. Use extended data type in the variable declaration.

If you don’t have an EDT for transactionCode - specify length of the variable during the variable declaration - like str 10 _transCode;

which will avoid the error what you are getting during the assignment in the select statement.

11

thanks Kranthi replaced transcode with an EDT and the problem is solved

12

hi… how are you??? iam learning dynamic ax 2009… tell me about ax… how is apartunities in ax 2009