Count of group by statement AX2012 R3

Question

I want to count group by statement values and write control.

There is a table which have 3 fields; ItemId, TaxGroup and TaxItemGroup. Same itemId must have same taxgroup and taxItemGroup values. If lines which have same ItemId have different tax values, i should throw an exception.

I wrote this code but this return number of all records. How can i write this control?

while select count(RecId) from ActivityLineExtraLcl
        group by Itemid, TaxGroup, TaxItemGroup
            where ActivityLineExtraLcl.Amount != 0
if(ActivityLineExtraLcl.RecId > 1 )
      throw error("Same ItemIds can't have different values!");

Why do you think grouping by ItemId, TaxGroup and TaxItemGroup and evaluating the number of records in each group will tell you if there are ItemIds that have different tax values? — FH-Inway, Jun 21 '21 at 18:50

score 4 · Accepted Answer · answered Jun 21 '21 at 21:58

The logic you're attempting won't work as written and it doesn't really make sense. You can try something like the below. This identifies the actual items. Your code (if it worked) just looks to see if the entire table ActivityLineExtraLcl has any issues.

ActivityLineExtraLcl        ActivityLineExtraLcl;
ActivityLineExtraLcl        ActivityLineExtraLclExists;

while select ItemId from ActivityLineExtraLcl
    group by ItemId
    where ActivityLineExtraLcl.Amount != 0
exists join ActivityLineExtraLclExists
    where ActivityLineExtraLclExists.ItemId         == ActivityLineExtraLcl.ItemId          &&
          ActivityLineExtraLclExists.Amount         != 0                                    &&
         (ActivityLineExtraLclExists.TaxGroup       != ActivityLineExtraLcl.TaxGroup        ||
          ActivityLineExtraLclExists.TaxItemGroup   != ActivityLineExtraLcl.TaxItemGroup)
          
{
    error(strFmt("Item '%1' has different tax values", ActivityLineExtraLcl.ItemId));
}

Count of group by statement AX2012 R3

1 Answers1