Need to insert value one time per record using while loop

Finance and Operations/AX Developer Forum
,
1

Hi All,

I have three tables 1) prodtable, 2)TransTable, 3)RouteTable. I need to insert the value in Trans table based on prod and route table.

while select Trans where Trans.CreateType != Type::Task
             join prodTable
            where prodTable.ProdId == Trans.ProdId && prodTable.WorkflowState == WorkflowState::Approved
            join Route
            where Route.ProdId == Trans.ProdId

{
            if (Route.RecId)
            {
                Trans.CeateType = 'Task';
                Trans.Insert();
            }
   }

I need to insert ‘Task’ in trans table if the prodid doesn’t have ‘Task’.

Problem: for the above code combination, the trans table same prodid already have other 2 value other than Task(a,b,). So when i am inserting , Task is inserted two times because of a and b values. It should insert the Task in only one time.

Please guide/help me on this one.

Thanks in Advance,
Marcus.

2

Your current code does a completely different thing that you want. It tries to create a task transaction for every transaction that isn’t a Task.
What you really want is finding ProdTable records that don’t have a task transaction - and the way to do it is using notexists join:

while select prodTable
	where prodTable.WorkflowState == WorkflowState::Approved
	notexists join trans
		where trans.ProdId == prodTable.ProdId
		   && trans.CreateType == Type::Task
3

Hi @MartinDrab ,

Thanks for your reply. I checked with your code. It returns all the prodid which is not existing in Trans table.

But my need is that, Trans table should have the prodid of ProdTable and also that trans table should not have create type: ‘Task’ for corresponding prodid.

 while select prodTable
	where prodTable.WorkflowState == WorkflowState::Approved
	notexists join trans
		where trans.ProdId == prodTable.ProdId
		   && trans.CreateType == Type::Task
{
 
          Info(strFmt("Id1: %1",prodTable.ProdId));
            Info(strFmt("id2: %1",trans.ProdId));
 }

Please advice.

Marcus.

4

Are you saying that it should has at least one transaction? If so, you can add an additional exists join for this condition.
You’re wrong in thinking that you’re looking for a transaction meeting some conditions. That’s what you already tried and it failed, because a single production order may have several such transactions. You’re actually looking for an order meeting some conditions.

5

Hi @MartinDrab ,

The trans table is a customized table. As you told in your reply, i tried with another exist join by creating new buffer for trans table. it didn’t work.

My need is actually ,to insert the createType field as ‘Task’ in Trans table if 'task; is not there in trans table for corresponding prodid.

Because every production id of prodtable has different createtype field in trans table(A,B,TASK,C). so when i am trying to insert Task , its inserted 3 times (because while loop of A,B,C).

As per your suggestion NotExists Join works to return the Prodid which doesn’t have task from trans table and also which also returning the empty values(sometimes trans table doesn’t have any record.it should not consider that).

Tried the below code, doesn’t work.

while select prodTable
	where prodTable.WorkflowState == WorkflowState::Approved
	notexists join trans
		where trans.ProdId == prodTable.ProdId
		   && trans.CreateType == Type::Task
exists join trans1
		where trans1.ProdId == prodTable.ProdId
		  {
                 }

I hope you understand now. Please advice

Thanks,
Marcus

6

I’m sorry, but “doesn’t work” is not a sufficient description of a problem. Please give us more information, so we know what needs fixing.