BULK Update Job Issue

(1) Share

Report

Posted on by Ayushaman

261

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!!

I have the same question (0)

All responses (17)

Answers (1)

Martin Dráb 236,275 Most Valuable Professional on at

Like (1)

Report

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.

Was this reply helpful? Yes No
Ayushaman 261 on at

Like (0)

Report
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

Was this reply helpful? Yes No
Verified answer

Martin Dráb 236,275 Most Valuable Professional on at

Like (0)

Report
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

You can also check F&O documentation to learn how to use select statements (https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/dev-ref/xpp-data/xpp-select-statement?WT.mc_id=DX-MVP-5000414).

Was this reply helpful? Yes No
Ayushaman 261 on at

Like (0)

Report
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

Was this reply helpful? Yes No
Martin Dráb 236,275 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Ayushaman 261 on at

Like (0)

Report
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

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 236,275 Most Valuable Professional on at

Like (0)

Report
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.

Was this reply helpful? Yes No
Ayushaman 261 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Martin Dráb 236,275 Most Valuable Professional on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Ayushaman 261 on at

Like (0)

Report

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

Was this reply helpful? Yes No

Community site session details

BULK Update Job Issue

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Abhilash Warrier – Community Spotlight

Congratulations to the September Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

BULK Update Job Issue

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Abhilash Warrier – Community Spotlight

Congratulations to the September Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates