Cannot update table with error "record was not selected for update". Same code works in other table.

(0) Share

Report

Posted on by Dushatar

I am trying to update a field in my table and I am getting the correct values (as seen in the info-prints) but when it gets to update() line; it crashes saying:

7870.dffff.png

This is the code. I have a very similar method (just other joins) on another table where it works perfectly. So why does it not let me update here?

        ttsbegin;
        while select forUpdate rTable
        {
            select count(RecId) from vTrans group by rTable.RecId
                join rTable where rTable.Voucher == vTrans.Voucher
                join docTable where docTable.VendTransId == vTrans.RecId
                join docLine where docLine.DocHeaderRefRecId == docTable.RecId
                join approver where approver.LineRefRecid == docLine.RecId;
            
            Info("RecId: "   int2Str (rTable.RecId));           //Gives correct value
            Info("Antal approvers: "   int2Str (vTrans.RecId)); //Gives correct value
            rTable.NumApprovers = vTrans.RecId;                 //Saves correct value.
            
            //rTable.selectforUpdate(true);                     //Was suggested, did not work. 
            //rTable.reread();                                  //Was suggested, did not work. 
            
            rTable.update();                                    //CRASHES when it gets here. 
        }
        ttscommit;

I have the same question (0)

All responses (9)

Answers (1)

Verified answer

Martin Dráb 237,831 Most Valuable Professional on at

Like (1)

Report
There is a huge bug in your code. You're trying to iterate rTable, but you overwrite the value inside the loop, because you use rTable again in the select statement there. Use a different name inside the loop. For example:

ttsbegin; while select forUpdate rTable { RTable rTableForCount; select count(RecId) from vTrans group by rTableForCount.RecId join rTableForCount where rTableForCount.Voucher == vTrans.Voucher join docTable where docTable.VendTransId == vTrans.RecId join docLine where docLine.DocHeaderRefRecId == docTable.RecId join approver where approver.LineRefRecid == docLine.RecId; rTable.NumApprovers = vTrans.RecId; rTable.update(); } ttscommit;

Another obvious bug is that you run the same query again and again for each rTable record - the query inside doesn't ever use the value from the while select. Also, you group records by rTableForCount.RecId, but you always take data from the first group only.

Running such a query every time when you open a form suggests a wrong design. It means a problem for performance and a high risk update conflicts.

Was this reply helpful? Yes No
Dushatar 26 on at

Like (0)

Report

Seems I do a lot of thing wrong here. I am indeed new to this.

What I want to do is to give a field in rTable the value from that count query. That query gives the correct value when used in the SQL-database so I have just tried to transfer it to x++.

So what would the correct way be to update rTable with that count value?

Was this reply helpful? Yes No
Dushatar 26 on at

Like (0)

Report

Naming the second rTable a different name makes it run, but as you said it is way too inefficient to even finish. But surely this is a task that can be done? Is it possible to solve it with recordset instead? But from what I understand you cant use count there?

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

Like (0)

Report

First of all, make a query that does something useful, instead of running the same query across all lines again and again. When you filter the query, it's likely that performance will be much better too.

When you have a meaningful query returning the right result, then it'll be time to optimize it. You can use update_recordset for it. I recommend creating a view that will count transactions for the given RTable, and using it in update_recordset.

But as I said, the whole desing looks very problematic. It may have a significantly negative impact on usability of your system.

Was this reply helpful? Yes No
Dushatar 26 on at

Like (0)

Report
What do you mean "instead of running the same query across all lines again and again."?

For each head in rTable I want to count the number of lines. If I want the same type of result I should use the same query, right? Even though the query is the same, the result will be different depending on the head I am running it on.

This is the "same" code I run in another method which sets the AvgApprover field to different values for each row just as intended, even thought ,like in the above code, it runs the "same query".

ttsbegin; while select forUpdate vTable { select count(RecId) from approver join docLine where docLine.recid == approver.LINEREFRECID join docTable where docTable.RECID == docLine.DocHeaderRefRecId && vTable.VendAccount == docTable.VendAccount join vTrans where vTrans.recid == docTable.VENDTRANSID; vTable.AvgApprovers = approver.RecId; vTable.update(); } ttscommit;

Maybe Im missing something completely, but what I thought my code did was to go through the rows in rTable, and for each row it would do the count-query to count the lines of that row. So lets say I have 100 rows in rTable each with varying number of lines. It would run this query 100 times and save those 100 values into the field in those 100 rows.

When I run the query

select count(RecId) from vTrans
group by rTableForCount.RecId
join rTableForCount where rTableForCount.Voucher == vTrans.Voucher
join docTable where docTable.VendTransId == vTrans.RecId
join docLine where docLine.DocHeaderRefRecId == docTable.RecId
join approver where approver.LineRefRecid == docLine.RecId;

in the SQL server database I instantly get the different values for every row. So if the database can do it instantly, why is it not possible in x ? I am really just trying to save the data from that database query into a table.

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

Like (0)

Report

All right, now you have a filter for with a field from the table in the while select (VendAccount), which you didn't have in the query you showed above. There you referred to the joined rTable record. I obviously talked about the code that you showed us at that time.

Your question "why is it not possible in x++" isn't a good one, because it is possible. Did you try it? Did you run into any problem?

Was this reply helpful? Yes No
Dushatar 26 on at

Like (0)

Report
No I did not try it cause you said " the whole desing looks very problematic. It may have a significantly negative impact on usability of your system.".

I took that as meaning it cant be done. Or at least not in a time efficient manor :)

I am still trying to figure out how to transfer that (instant) sql query into working x code (saving those values into a table).

EDIT: Nvm, I think I finally got it to work. I removed rTable from the joins and just used its value to filter instead. This seems to do the trick, and in a reasonable time.

while select forUpdate rTable { select count(RecId) from vTrans group by docTable.RecId where rTable.Voucher == vTrans.Voucher join docTable where docTable.VendTransId == vTrans.RecId join docLine where docLine.DocHeaderRefRecId == docTable.RecId join approver where approver.LineRefRecid == docLine.RecId; rTable.NumApprovers = vTrans.RecId; rTable.update(); }

But if I understood you right the way I do it is still really slow. With my 2500 lines in takes 20:ish seconds. While it is instant in SQL even with 10,000 rows.

So do you have a suggestion to make it faster? Id like to use recordset, but I do not think it works with count, does it?

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 237,831 Most Valuable Professional on at

Like (0)

Report
I'm saying that fetching all records from database and updating them every time when a form opens looks wrong. It's expensive by itself, you do it repeatedly even if nothing changed and updating all records means that you can get into conflicts with changes done by othere users. Mere opening the form by two users can lead to problems.

This doesn't say that creating a query with count() and group by is impossible in X++.

Consider two alternative desings:

Don't store the value in vTable.AvgApprovers at all, especially if it changes often. Create a view, calculate the numbers there and use this view in the form.

If storing numbers in AvgApprovers is needed (they're need often but they change only occassionally), it seems that you should update vTable.AvgApprovers when a record is added to approver, removed from there or - if allowed - approver.LineRefRecId changes.

By the way, you also seemed to miss my remark about update_recordset.

Was this reply helpful? Yes No
Dushatar 26 on at

Like (0)

Report

I do not plan to do this each time the form is opened, currently I really only do it once, at the same time as I build the tables in a initiator class.

The data wont be updated very often, and considering it doesnt have to be that exact I was thinking of just having it run as a batch job updating it every now and then.

However your idea of updating it when a new record is added is probably the better (but more expensive) solution.

Was this reply helpful? Yes No

Join the conversation

Community site session details

Cannot update table with error "record was not selected for update". Same code works in other table.

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

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

Featured topics

Product updates

Community site session details

Cannot update table with error "record was not selected for update". Same code works in other table.

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

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

Featured topics

Product updates