Skip to main content

Notifications

Announcements

Share Your Journey with Power Pages and Help Shape Its Future!
News and Announcements icon
Dynamics 365 Community / Forums / Finance forum / BULK Update Job Issue
Finance forum
Answered

BULK Update Job Issue

(1) ShareShare
ReportReport
Posted on by Ayushaman 50
When using this piece of code as a job i am getting error while updating my melting table from tagline table .
  public void UpdateJob()
  {
    BULKOLDTAGDESTROY BULKOLDTAGDESTROY;
    BULKOLDTAGDESTROYDETAIL BULKOLDTAGDESTROYDETAIL;
    INVENTJOURNALTRANS iNVENTJOURNALTRANS;
    INVENTDIM iNVENTDIM;
    MELTINGTABLE MELTINGTABLE;
   TAGLINES TAGLINES;
    int updateCount = 0;

    ttsbegin;
    
    while select forUpdate MELTINGTABLE
        join iNVENTDIM where MELTINGTABLE.BATCHID == iNVENTDIM.INVENTBATCHID
        join iNVENTJOURNALTRANS where iNVENTDIM.INVENTDIMID == iNVENTJOURNALTRANS.INVENTDIMID
        join BULKOLDTAGDESTROY where iNVENTJOURNALTRANS.JOURNALID == BULKOLDTAGDESTROY.JOURNALID
        join BULKOLDTAGDESTROYDETAIL where BULKOLDTAGDESTROY.BULKTAGDESTROYNO == BULKOLDTAGDESTROYDETAIL.BULKTAGDESTROYNO
        notExists join TAGLINES where BULKOLDTAGDESTROYDETAIL.TAGNO == TAGLINES.TAGNO
            && MELTINGTABLE.INPUTITEMID == TAGLINES.ITEMID
            && TAGLINES.TRANSFINEWEIGHT != 0.00
            && TAGLINES.NetwtDeduction != 0.00
    {
    
      if (MELTINGTABLE && TAGLINES)
      {
        MELTINGTABLE.TRANSFINEWEIGHTNEW = TAGLINES.TRANSFINEWEIGHT;
        MELTINGTABLE.NetwtDeduction = TAGLINES.NetwtDeduction;
        MELTINGTABLE.update();
        updateCount++;
      }
    }
    ttscommit;
    info(strFmt("Updated %1 records in MELTINGTABLE.", updateCount));
  }

I am gettign error :An update conflict occurred due to another user process deleting the record or changing one or more fields in the record. 
I also tried marking tagline as null after the while loop but then i don't get value from the while select statement to update in melting table
purpose : i want to update my melting table with tagline table on daily basis in scheduled time frame thats why i need this job to be running perfectly.

Thanks for the help!!
Categories:
Customization / Solutions / SDK and API
  • Martin Dráb Profile Picture
    Martin Dráb 228,317 Most Valuable Professional on at
    BULK Update Job Issue
    Notice that your previous attempt included the buffer name in the field list (e.g. sum(TAGLINES.TRANSFINEWEIGHT)), which isn't correct. The buffer is already clearly identified (by from tagLines), therefore there is no need to repeat it and X++ syntax doesn't even allow that.
     
    It seems that you're trying to follow syntax of SQL (another example is your usage of as operator), but you must respect X++ select statement syntax.
  • Ayushaman Profile Picture
    Ayushaman 50 on at
    BULK Update Job Issue
    Hi Martin!!
     
    Thanks for the assistance! That worked like charm. I don't know where i was wrong in syntax though😶.But Thanks again. I am sharing this if anyone might need.

    Regards,
    Ayushaman 
    while select sum(TransFineWeight), sum(NetWtDeduction), ItemId, TagNo from   TAGLINES
         GROUP BY  TAGLINES.TAGNO, TAGLINES.ITEMID
       join   BULKOLDTAGDESTROYDETAIL
            where   TAGLINES.TAGNO ==   BULKOLDTAGDESTROYDETAIL.TAGNO
       join   BULKOLDTAGDESTROY
            where   BULKOLDTAGDESTROYDETAIL.BULKTAGDESTROYNO ==   BULKOLDTAGDESTROY.BULKTAGDESTROYNO
       join iNVENTJOURNALTRANS
            where   BULKOLDTAGDESTROY.JOURNALID == iNVENTJOURNALTRANS.JOURNALID
       join iNVENTDIM
            where iNVENTJOURNALTRANS.INVENTDIMID == iNVENTDIM.INVENTDIMID
       join   MELTINGTABLE
            where iNVENTDIM.INVENTBATCHID ==   MELTINGTABLE.BATCHID
            &&   MELTINGTABLE.INPUTITEMID ==   TAGLINES.ITEMID
     
  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 228,317 Most Valuable Professional on at
    BULK Update Job Issue
    When all your attempts failed, maybe it's time to look at the code I gave you and see the correct syntax there. Let me repeat it here: 
    while select sum(TransFineWeight), sum(NetWtDeduction), ItemId, TagNo from tagLines
  • Ayushaman Profile Picture
    Ayushaman 50 on at
    BULK Update Job Issue
    Martin,
     
    I made the amendments as per you suggestion but i am still getting those  
     public void UpdateJob()
  {
       BULKOLDTAGDESTROY  BULKOLDTAGDESTROY;
       BULKOLDTAGDESTROYDETAIL  BULKOLDTAGDESTROYDETAIL;
      INVENTJOURNALTRANS iNVENTJOURNALTRANS;
      INVENTDIM iNVENTDIM;
       MELTINGTABLE  MELTINGTABLE,meltingTableUpdate;
       TAGLINES  TAGLINES;
      int updateCount = 0;
      real TransFineWeightSum;
      real NetWtDeductionSum;

      ttsbegin;
      while select sum( TAGLINES.TRANSFINEWEIGHT), //error ')' expected
              sum( TAGLINES.NetwtDeduction),
               TAGLINES.TAGNO,
               TAGLINES.ITEMID
       from  TAGLINES
       join  BULKOLDTAGDESTROYDETAIL
            on  TAGLINES.TAGNO =  BULKOLDTAGDESTROYDETAIL.TAGNO
       join  BULKOLDTAGDESTROY
            on  BULKOLDTAGDESTROYDETAIL.BULKTAGDESTROYNO =  BULKOLDTAGDESTROY.BULKTAGDESTROYNO
       join INVENTJOURNALTRANS
            on  BULKOLDTAGDESTROY.JOURNALID = INVENTJOURNALTRANS.JOURNALID
       join INVENTDIM
            on INVENTJOURNALTRANS.INVENTDIMID = INVENTDIM.INVENTDIMID
       join  MELTINGTABLE
            on INVENTDIM.INVENTBATCHID =  MELTINGTABLE.BATCHID
            &&  MELTINGTABLE.INPUTITEMID =  TAGLINES.ITEMID
    ​​​​​​​ group by aCXTAGLINES.TAGNO,aCXTAGLINES.ITEMID

      {
        select forupdate meltingTableUpdate 
          where meltingTableUpdate.BatchId == iNVENTDIM.INVENTBATCHID
          &&  MELTINGTABLE.INPUTITEMID ==  TAGLINES.ITEMID;

          if (meltingTableUpdate)
           {
             meltingTableUpdate.TRANSFINEWEIGHTNEW =  TAGLINES.TRANSFINEWEIGHT;
             meltingTableUpdate.NetWtDeduction =  TAGLINES.NetwtDeduction;
             meltingTableUpdate.update();
             updateCount++;
      	   }
            }

      ttscommit;

      info(strFmt("Updated %1 records in  MELTINGTABLE.", updateCount));
  }
    Kindly review it once and suggest me where i can correct it . for error reference kinldy find the attached ss
  • Martin Dráb Profile Picture
    Martin Dráb 228,317 Most Valuable Professional on at
    BULK Update Job Issue
    Your code isn't valid X++ - 'as' operator isn't supported in X++ select statements.
     
    You can see the correct syntax in the example I gave you. You'll then access the aggregated values by simple tagLines.TransFineWeight and tagLines.NetWtDeduction.

    Also, you can't update the meltingTable record, because you didn't select any record for update. Instead of calling reread(), use find() method or a select statement to find the record to update.

    I know you use meltingTable buffer in your query, but that can be used only to filter records returned by the query. It'll never fetch any field values from database and it can't be used to update the data. You need to select the record (that will store the aggregated values) inside the loop.
  • Ayushaman Profile Picture
    Ayushaman 50 on at
    BULK Update Job Issue
    Hi Martin,
    Thanks for the prompt response. 
     ttsbegin;
    

    while select sum( TAGLINES.TRANSFINEWEIGHT) as TransFineWeightSum, // in here i am getting error stating')' isn't declared
                   sum( TAGLINES.NetwtDeduction) as NetWtDeductionSum,
                    TAGLINES.TAGNO,
                    TAGLINES.ITEMID
        from  TAGLINES
        join  BULKOLDTAGDESTROYDETAIL where  TAGLINES.TAGNO ==  BULKOLDTAGDESTROYDETAIL.TAGNO
        join  BULKOLDTAGDESTROY where  BULKOLDTAGDESTROYDETAIL.BULKTAGDESTROYNO ==  BULKOLDTAGDESTROY.BULKTAGDESTROYNO
        join iNVENTJOURNALTRANS where  BULKOLDTAGDESTROY.JOURNALID == iNVENTJOURNALTRANS.JOURNALID
        join iNVENTDIM where iNVENTJOURNALTRANS.INVENTDIMID == iNVENTDIM.INVENTDIMID
        join  MELTINGTABLE where iNVENTDIM.INVENTBATCHID ==  MELTINGTABLE.BATCHID
                             &&  MELTINGTABLE.INPUTITEMID ==  TAGLINES.ITEMID
        where  TAGLINES.TRANSFINEWEIGHT != 0.00
           &&  TAGLINES.NetwtDeduction != 0.00
        group by  TAGLINES.TAGNO,  TAGLINES.ITEMID
    {
      
       MELTINGTABLE.reread();
       MELTINGTABLE.TRANSFINEWEIGHTNEW = TransFineWeightSum;
       MELTINGTABLE.NetWtDeduction = NetWtDeductionSum;
       MELTINGTABLE.update();
      updateCount++;
    }
    
    ttscommit;
    Can you please help me out here;  with the circular bracket error
    Thanks,
    Ayushaman
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 228,317 Most Valuable Professional on at
    BULK Update Job Issue
    Use sum() function to summarize the values. Also, you can't use notExists join as in your current code, because that doesn't fetch any data from the table.
     
    You can use something like this to iterate the records and then update MeltingTable.
    while select sum(TransFineWeight), sum(NetWtDeduction), ItemId, TagNo from tagLines
    group by ItemId, TagNo
    where ...
{
}
    The most efficient way would be using update_recordset, but I recommend starting with something simpler and optimize it afterwards.
  • Ayushaman Profile Picture
    Ayushaman 50 on at
    BULK Update Job Issue
    Hi Martin !
     
    I apologize for confusing you guys with my description. What you are saying is perfectly accurate for the requirement. I also got last minute update on it right now only. i want to sum up the transfinewt from the tagline table and update it on meltingtable.transfineweightnew. same for netwtdeduciton also.  and update it on melting table against Itemid. 

    Feel free to ping me back if you have any queries to help me resolve this issue.
     
    Thanks,
    Ayushaman
  • Martin Dráb Profile Picture
    Martin Dráb 228,317 Most Valuable Professional on at
    BULK Update Job Issue
    Your description is very confusing. For example, you're saying that all ten `TAGLINES.tag_no` records in the `MELTINGTABLE` are being updated, but MeltingTable doesn't contain TagLines records.
     
    You seem to be trying to say that you have multiple TagLines records to a single MeltingTable record and that your logic updates the MeltingTable record with TransFineWeightNew and NetwtDeduction from the last TagLines records. That's true; your code keeps overwriting the same MeltingTable record and the final values are the ones from the last TagLines record. This logical error can't be fixed by mere reread().
     
    You need to decide what you want to put to the MeltingTable record. Do you want to somehow summarize values from those four TagLines records and store the calculated values in MeltingTable?
  • Ayushaman Profile Picture
    Ayushaman 50 on at
    BULK Update Job Issue
    Hi Waed !
     
    Thanks for the assistance ! Yes, I tried that and that issue is resolved  but the error that i mentioned in my previous reply is not yet resolved. 

    Thanks,
    Ayushaman

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Share Your Journey with Power Pages and Help Shape Its Future!

Quick Links

Dynamics 365 Community Update – Sep 16th

Welcome to the next edition of the Community Platform Update. This is a weekly…

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,435 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,317 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans