X++ number duplicates in while select loop

I am attempting to run a loop where one column will have duplicates, usually not more than 2 of the same value. I need a way to count them so that I have a list of 1’s with an occasional 2 when there is a duplicate of the value. For example:

MyTable:

BatchNum CoilNum WireNum
1 B-1010 A-2020
2 B-1011 A-2021
3 B-1010 A-2022
4 B-1012 A-2023
5 B-1013 A-2024
6 B-1013 A-2025

I would like the results of the query to be like:

WireNum CoilNum Count
A-2020 B-1010 1
A-2021 B-1011 1
A-2022 B-1010 2
A-2023 B-1012 1
A-2024 B-1013 1
A-2025 B-1013 2

After this, I am then inserting records into another table based on this information, something like:

//insert

while select batAttrib

{

batAttrib.PdsAttribId = strFmt("CoilBatch%1",Count);

batAttrib.PsdAttribValue = CoilNum;

}

I just haven’t been able to figure out how to get the count to work the way that I am expecting in X++. I have it working in SQL, but I don’t know how to translate.

What you can do is add them to the table as you walk through the loop and before adding them do a select on the main fields to see if that record already exists.

So in your WHILE which walks through your records also check the table that you are filling to see if that record already exists. If it does then do nothing, else add the record into the table you’re filling.

Using aggregation at database level is much more efficient. It would look like this:

while WireNum, CoilNum, count(RecId) from myTable
    group by WireNum, CoilNum
{
}

I have tried to implement this without success:

while select count(invDim.InventBatchId), invDimCoils.InventBatchId, invTranOrigCoils.ItemId, invDim.InventBatchId invDim

group by InventBatchId, PdsBatchAttribValue

where invDim.inventBatchId == noBatAttrib.InventBatchId

join invTran

where invTran.inventDimId == invDim.inventDimId

join invTransOrig

where invTransOrig.RecId == invTran.InventTransOrigin

&& invTransOrig.ItemId == invTran.ItemId

&& invTransOrig.ReferenceCategory == InventTransType::BOMMain

join invTranOrigCoils

where invTranOrigCoils.ReferenceId == invTransOrig.ReferenceId

&& invTranOrigCoils.ReferenceCategory == InventTransType::BOMLine

join invTranCoils

where invTranCoils.InventTransOrigin == invTranOrigCoils.RecId

&& invTranCoils.ItemId == invTranOrigCoils.ItemId

join invDimCoils

I keep getting an error about syntax on the first line. I’m not sure what is wrong with it, from all the examples that I’ve found it looks right to me.

I already have a check to make sure that the entry is not there for the 1’s. It is the second part that is giving me problems. I don’t know how to make it switch between entering the 1’s and entering the 2’s. I will edit my original question with more detail about my code.

I guess it’s been too long and I cannot edit my original question. Here are more details about my code:

//Get only wire boxes without a CoilBatch1
while select noBatAttrib
group by noBatAttrib.InventBatchId
where noBatAttrib.PdsBatchAttribId != "CoilBatch1"
&& noBatAttrib.InventBatchId like "B-20*"
{//Get the parent coils for the found wire boxes
while select count(invDim.InventBatchId), invDimCoils.InventBatchId, invTranOrigCoils.ItemId, invDim.InventBatchId invDim
group by InventBatchId, PdsBatchAttribValue
where invDim.inventBatchId == noBatAttrib.InventBatchId
join invTran
where invTran.inventDimId == invDim.inventDimId
join invTransOrig
where invTransOrig.RecId == invTran.InventTransOrigin
&& invTransOrig.ItemId == invTran.ItemId
&& invTransOrig.ReferenceCategory == InventTransType::BOMMain
join invTranOrigCoils
where invTranOrigCoils.ReferenceId == invTransOrig.ReferenceId
&& invTranOrigCoils.ReferenceCategory == InventTransType::BOMLine
join invTranCoils
where invTranCoils.InventTransOrigin == invTranOrigCoils.RecId
&& invTranCoils.ItemId == invTranOrigCoils.ItemId
join invDimCoils
``
The outer while select gets all the boxes that I need to enter coil info for. The inner while select pulls the coil names. Like I mentioned in my original question there will sometimes be 2 coils per wire box. I will need to enter 2 lines one for each coil name but changing the Attribute name. The Attribute names that I am using are CoilBatch1 and CoilBatch2. So for the first, the 1’s in my original question, I would have something like:
AttribName, WireBox, Coil
“CoilBatch1”, A-2020, B-1010
Then for the second coil name attached to the wire box, I would have something like:
AttribName, WireBox, Coil
“CoilBatch2”, A-2020, B-1020

I have the above code so that it will get the first one and enter it just fine, but I cannot figure out how to get it to switch to “CoilBatch2” when there is a second coil that is associated with the wire box.

The compiler is right - you code isn’t syntactically correct. You forgot from keyword, count(invDim.InventBatchId) isn’t a valid selector and so on. You may be interested in Select Statement Syntax and related pages in AX documentation.

Also note that it’s not logically correct either. For example, you’re trying to return invDimCoils.InventBatchId, but you don’t group by it nor you aggregated it to a single value for the group, therefore its value is undefined (AX will return an empty string).

I have updated my code based on your suggestions and what I was able to find in the link you gave, but I still get the same syntax error message for the code:

while select count(invDim.InventBatchId), invDimCoils.InventBatchId, invTranOrigCoils.ItemId from invDim
group by invDim.InventBatchId, invDimCoils.InventBatchId, invTranOrigCoils.ItemId
where invDim.inventBatchId == noBatAttrib.InventBatchId
join invTran
where invTran.inventDimId == invDim.inventDimId
join invTransOrig
where invTransOrig.RecId == invTran.InventTransOrigin
&& invTransOrig.ItemId == invTran.ItemId
&& invTransOrig.ReferenceCategory == InventTransType::BOMMain
join invTranOrigCoils
where invTranOrigCoils.ReferenceId == invTransOrig.ReferenceId
&& invTranOrigCoils.ReferenceCategory == InventTransType::BOMLine
join invTranCoils
where invTranCoils.InventTransOrigin == invTranOrigCoils.RecId
&& invTranCoils.ItemId == invTranOrigCoils.ItemId
join invDimCoils

Why don’t you look at what the compiler tells you and fix it? As the compiler, I told too you that count(invDim.InventBatchId) isn’t a valid identifier, yet you still haven’t fixed your bug. Please pay attention to the errors identified by the compiler and fix them one by one. If you don’t know how to fix some of them, first read the documentation, then try a few things and if you’re still stuck, explain your problem in the forum.

Also, try to find somebody in your company who know basics of X++ development to give you some training. Also, there are books about AX, training materials, video courses, blogs and many other resources you can learn from.

This is your code rewritten to be syntactically correct (although there are still other bugs):

InventDim invDim, invDimCoils;
InventTrans invTran, invTranCoils;
InventTransOrigin invTransOrig, invTranOrigCoils;

while select count(RecId) from invDim
   group by InventBatchId
   where invDim.inventBatchId == 'test'

   join invTran
      where invTran.inventDimId == invDim.inventDimId

   join invTransOrig
      where invTransOrig.RecId == invTran.InventTransOrigin
         && invTransOrig.ItemId == invTran.ItemId
         && invTransOrig.ReferenceCategory == InventTransType::BOMMain

   join ItemId from invTranOrigCoils
      group by ItemId
      where invTranOrigCoils.ReferenceId == invTransOrig.ReferenceId
         && invTranOrigCoils.ReferenceCategory == InventTransType::BOMLine

   join invTranCoils
      where invTranCoils.InventTransOrigin == invTranOrigCoils.RecId
         && invTranCoils.ItemId == invTranOrigCoils.ItemId

   join InventBatchId from invDimCoils
      // BUG! Missing join condition
      group by InventBatchId
{}

Martin, I do look at what the compiler is telling me, but it isn’t very specific just says there is a syntax error. I’ve only been developing in X++ for a couple of months so I’m still learning. I have read the documentation, as I said I’m still learning so it doesn’t all make sense and it seemed to me that I was following the documentation, that’s why I am confused. I have tried several things in trying to get the errors to go away, but I’ve not found the solution yet.

I am the IT for my plant and there isn’t anyone else that I can go to in my company as I’m it. I do have a sister company that has a dev too and I’ve gone to him several times, but he doesn’t have the time to train me extensively in X++ as he has his plant to worry about and his development. He has helped me get to the point that I’m at, but he is busy with projects himself and cannot spend the time I need to figure this out.

I have searched online for help with this particular problem (and others), I don’t post much because I can usually find a solution that works, even if it isn’t elegant. This one I’ve not been able to figure out yet, that’s why I’m posting a question.

I have tried rewriting my code to follow what you wrote, but I am not getting another error that I don’t understand. The reason I wrote it the way I did, to begin with, is because of the documentation that I found that seemed to support me. In the doc Select Statement Syntax, I searched for “Group By” and found the example about halfway down the page for it that looks like:

CustTable custTable;

;

while select sum(CreditMax) from custTable

    group by CustGroup

{

    print custTable.CustGroup, " ",custTable.CreditMax;

}

Right below that is an example for using “Index” that looks like this:

CustTable custTable;

;

while select AccountNum, Name from custTable

    index AccountIdx

{

    print custTable.AccountNum, " ", custTable.Name;

}

Combining what I found in them I came up with the while select that I posted originally. Now with your help, I have made changes, but I have other errors and don’t have any clue where to even look to find a solution. What I changed my code to it this:

InventDim           invDim, invDimCoils;
InventTrans         invTran, invTranCoils;
PdsBatchAttributes  batAttrib, noBatAttrib;
InventJournalTable  invJorTable;
InventJournalTrans  invJorTrans, invJorTransC;
InventTransOrigin   invTransOrig, invTranOrigCoils;
    
while select noBatAttrib
    group by noBatAttrib.InventBatchId
    where noBatAttrib.PdsBatchAttribId != "CoilBatch1"
        && noBatAttrib.InventBatchId like "B-20*"
{//Get the parent coils for the found wire boxes
    while select count(InventBatchId) from invDim
        group by InventBatchId
        where invDim.InventBatchId == noBatAttrib.InventBatchId
        join invTran
            where invTran.inventDimId == invDim.inventDimId
        join invTransOrig
            where invTransOrig.RecId == invTran.InventTransOrigin
                && invTransOrig.ItemId == invTran.ItemId
                && invTransOrig.ReferenceCategory == InventTransType::BOMMain
        join ItemId invTranOrigCoils
            where invTranOrigCoils.ReferenceId == invTransOrig.ReferenceId
                && invTranOrigCoils.ReferenceCategory == InventTransType::BOMLine
        join invTranCoils
            where invTranCoils.InventTransOrigin == invTranOrigCoils.RecId
                && invTranCoils.ItemId == invTranOrigCoils.ItemId
        join InventBatchId invDimCoils
        //I forgot to include this last time
            where invDimCoils.InventDimId == invTranCoils.InventDimId
        {}
}

Where I am not running into errors is on the line: Join ItemId invTranOrigCoils. The compiler is now telling me that ItemId is a variable that has not been declared. Why is it now telling me that the ItemId is a variable? It should be the column that I want to use from the invTransOrigCoils table that I am using.

And I’m sure that I will get the same error when I get to the line: join InventBatchId invDimCoils, as it is in the same format. I have not seen this format before so I don’t understand it yet.

join ItemId invTranOrigCoils isn’t valid syntax.

You have two options when specifying a table in a query. One is without a field list:

join invTranOrigCoils

The other is using a field list, which has the following pattern: join {some fields} from {a table}. In your case, the right code is this:

join ItemId from invTranOrigCoils

Do you see? You forgot the ‘from’ keyword again.

You can also refer to the code I gave you before (where I refactored your code to something that compiles).

The missing from did it! Thanks! I’m not sure how I missed that again! I even went over your code example several times trying to figure out what I was doing wrong and just didn’t see it.

It now compiles and I’ll be able to test if it works.

I am curious about the Group By though. Since I am wanting to group by several different fields across the several joined tables how will AX know what order I want them grouped by? Or can I put them all in the first group by statement? Like this with them all in the one group by:

while select count(InventBatchId) from invDim
    group by InventBatchId, invTranOrigCoils.ItemId, invDimCoils.InventBatchId
    where invDim.InventBatchId == noBatAttrib.InventBatchId
    join invTran
        where invTran.inventDimId == invDim.inventDimId
    join invTransOrig
        where invTransOrig.RecId == invTran.InventTransOrigin
            && invTransOrig.ItemId == invTran.ItemId
            && invTransOrig.ReferenceCategory == InventTransType::BOMMain
    join ItemId from invTranOrigCoils
        where invTranOrigCoils.ReferenceId == invTransOrig.ReferenceId
            && invTranOrigCoils.ReferenceCategory == InventTransType::BOMLine
    join invTranCoils
        where invTranCoils.InventTransOrigin == invTranOrigCoils.RecId
            && invTranCoils.ItemId == invTranOrigCoils.ItemId
    join InventBatchId from invDimCoils
        where invDimCoils.InventDimId == invTranCoils.InventDimId

This compiles but doesn’t seem to get the results that I’m expecting. I get a SQL error about having duplicate columns specified, so this is probably not right.

Or like this with them spread out into 3 different groups:

while select count(InventBatchId) from invDim
    group by InventBatchId
    where invDim.InventBatchId == noBatAttrib.InventBatchId
    join invTran
        where invTran.inventDimId == invDim.inventDimId
    join invTransOrig
        where invTransOrig.RecId == invTran.InventTransOrigin
            && invTransOrig.ItemId == invTran.ItemId
            && invTransOrig.ReferenceCategory == InventTransType::BOMMain
    join ItemId from invTranOrigCoils
    group by ItemId
        where invTranOrigCoils.ReferenceId == invTransOrig.ReferenceId
            && invTranOrigCoils.ReferenceCategory == InventTransType::BOMLine
    join invTranCoils
        where invTranCoils.InventTransOrigin == invTranOrigCoils.RecId
            && invTranCoils.ItemId == invTranOrigCoils.ItemId
    join InventBatchId from invDimCoils
    group by InventBatchId
        where invDimCoils.InventDimId == invTranCoils.InventDimId

But this one doesn’t seem to distinguish between the first and second instance of the InventBatchId that I’m trying to group by, but it runs.

Please look at the refactored code I gave you; you can see how it’s done there.

Regarding “this one doesn’t seem to distinguish between the first and second instance of the InventBatchId”: it does! - from invDim group by InventBatchId is about inventDim buffer and from invDimCoils group by InventBatchId is about invDimCoils buffer.

If you look at T-SQL code generated from your X++ code, you’ll see there something like GROUP BY T1.InventBatchId, T2.InventBatchId. That the fields have the same names doesn’t matter, because they’re used in context.

Thank you for the help. Turns out it was my if statement after the above query that was not returning the results I expected. The group by was working correctly. I did keep forgetting to add the from.