Count of a field to be inserted in a temp table

Finance and Operations/AX Technical Forum
1

Hi all,

I have created a temporary table .I need to insert the count of another field in to the newly created temporry table.

I wrote following job to do that. But it was throwing an error.

static void updateLoa(Args _args)
{
caeLoa caeloa;
caeSecurityProj _caeSecurityProj;
TmpCaeLoa loa;
;
/insert into #LocalTempTable( loa,cnt) select caeloa,COUNT(caeloa) from CAESECURITYPROJ
group by caeloa in sql/

while select caeloa,COUNT(caeloa) from _caeSecurityProj
group by caeloa
{
loa.clear();
loa.caeLoa = _caeSecurityProj.caeLoa;
loa.cnt = _caeSecurityProj.COUNT(_caeSecurityProj.caeloa);
}
}

Kindly let me know how to write the above statement in X++.

Thanks

2

What do you expect from this code: _caeSecurityProj.COUNT(_caeSecurityProj.caeloa)?

Tables don’t have any count() method by default, so unless you wrote it by yourself, you should get a compilation error.

If you call an aggregation method on a field, AX saves the result to the same field, because it can’t create new columns at runtime. But you can’t get count(caeloa) at all in this case, because it’s already used for the grouping value. Use caeloa and count(RecId), for example, and read the aggregated value from caeSecurityProj.RecId.

3

Thanks Martin for your response. I wanted the count of the fields to be inserted in the temp table. For example in sql insert into #LocalTempTable( loa,cnt) select caeloa,COUNT(caeloa) from CAESECURITYPROJ
group by caeloa.

So i wrote a job to insert the field caeloa and the count of caeloa in to the temp table. Then i will refer the temp table for updating some field with the count. But i am unable to insert the count of the field.Please let me know if am not clear.

Thanks.

4

Please let me know what is not clear in my previous answer. I thought I explained both what’s wrong with your code and how to do it right.