Version 5: changes to record since retreival from database.

Mauddib · 2007-09-13

I have just moved databases from Nav Attain Native to Navision 5.0 on SQL.

The upgrade to Version 5 was technical only, not code. So its just the executables.

In one function I have code where i have SalesLines and TEMPSalesLines defined. I:

1) Reset an integer field on all sales lines to zero (an integer field of mine, not in any key). I then use MODIFY(FALSE) on this.

2) Insert the salesline into the temp table

IF salesline.FIND('-') THEN REPEAT
  salesline.myint := 0;
  salesline.MODIFY;
  tempsalesline := salesline;
  tempsalesline.INSERT;
UNTIL salesline.NEXT = 0;

3) Do some processing on some integers, nothing to do with tables to find my new values. Then set the temptable values.

tempsalesline.FIND('-');
for i := 1 to tempsalesline.COUNT DO BEGIN
  tempsalesline.myInt := newvals[i];
  tempsalesline.MODIFY;
  tempsalesline.NEXT;
END;

4) Write my changes to the database like this:

salesline := TempsalesLine
salesline.MODIFY;

This works FINE in Native 3.60 but in SQL 5.0 I get the 'Another user has changed this record since you got it from the database'.

Is this a change in SQL or in NAV5 causing this? If so what it the issue?[/code]

DenSter · 2007-09-13

I'd try CLEAR(salesline) before you set it to the value of Tempsalesline. You should now also review your FIND('-') and FIND('+') statements and replace them with the correct FIND statements.

By the way, MODIFYALL works faster than looping through and setting the values individually.

salesline.MODIFYALL(myint,0);
IF salesline.FINDSET(FALSE,FALSE) THEN REPEAT 
  tempsalesline.DELETEALL; // to empty out the temp table
  tempsalesline := salesline; 
  tempsalesline.INSERT; 
UNTIL salesline.NEXT = 0;

kine · 2007-09-13

It can be problem of timestamp, which is transfered when you assign the record into another one. Because than the timestamp is compared and if it is different, the error is fired. Try to assign just the fields you want to transfer.

Mauddib · 2007-09-14

So is this more a of a problem in navision 5 than before?

For example in many cases I have seen when you delete a sales header it does DELETE(TRUE) on the sales lines. However in the onDelete of the saleslines it sets the "Change by user" in the header.

Now in the past such things have always worked. However now in my Nav5SQL I get the same "user has changed the record...." when it gets to the SalesHeader.MODIFY; line.

Ok I admit having such a loop in code where the header goes to the lines goes to the header is bad code. Thats another discussion. But why has it worked in all Natives and SQLs before this project i am on now?

Mauddib · 2007-09-20

Just an update here which rounds down the problem to the recent version 5 upgrade and not to SQL.

I restored the same database into the same SQL only this time into Version 4 SP3.

The issue with the other user altering the record did not reoccour.

Obviously SOMETHING has been changed between 4.3 and 5 as to how it treats a record as having been edited since retreival or not. Does anyone know the parameters for this specific change?

mrQQ · 2007-09-20

i don't know what changed, but you CANNOT do it like that. you are right, if record has been changed after you retrieved it, then you just can't modify it directly, and it has been like this for ages.

what you should do is:

salesline := TempsalesLine // this sets primary key to correct
if salesline.find then // refresh this record, e.g. take current version
  salesline.MODIFY;

and that will work.

DenSter · 2007-09-20

Just be aware that that is not SQL Server friendly code, and yu might run into performance problems.

DenSter · 2007-09-20

Why not just do it straight on the sales line table. I don't see why you'd have to do it in a temp table first, if you're going to write it back to the 'real' table anyway. If it's a matter of sorting, you can always resort.

Something like this:

MyIntegerVar := 0;
IF salesline.FINDSET(TRUE,FALSE) THEN BEGIN
// Setting the first FINDSET parameter to TRUE makes it so you have 
// the records ready to modify
  salesline.MODIFYALL(myint,0); 
  REPEAT 
    MyIntegerVar += 1;
    salesline,myint := MyIntegerVar;
    salesline.MODIFY;
  UNTIL salesline.NEXT = 0;
END;

Mauddib · 2007-09-20

Thanks for the code examples. Im sure there are loads of other ways of doing it that I could use. Although for the processing I do it actually helps to use the temporary table...

... however the point I am trying to get to is not how to improve this code block... its to try and understand what has changed in the latest version that means the code works on one version and not another. I have missed anything written in the release notes I think, or its not there.

mrQQ · 2007-09-20

well i'm pretty sure you can't modify a old version of record in any version. just try it.

Mauddib · 2007-09-20

mrQQ, look at my second post above (4th on this thread).

This code works in my tests in:

Nav3.60 native
Nav3.60 SQL
Nav3.7 native
Nav4.3 native
Nav4.3 sql

But NOT in Nav 5.

The same is true for my first post above, however in terms of understanding my code i think the second example is easier to reproduce.

It might seema like a small issue but for any software house doing an upgrade from any version to Version 5 I think they are going to find this "minor change" a large headache in the future.

DenSter · 2007-09-20

I don't know about any undocumented changes, or why your code would have worked in other versions. In my experience prior than 5.0 that would have errored out also, so I guess you are lucky that it didn't.

The issue is you have the 'salesline' variable, you copy it over to 'tempsalesline'. When you INSERT the temp one, it becomes its own record, it is no longer identical to salesline. Now when you set salesline back to tempsalesline, the variable is still linked to the 'old' salesline and you get that error when you try to MODIFY it.

You don't always get errors, but once you do get them once or twice, you start programming such things more carefully, and you don't get the error anymore. I'm not saying your coding sucks, but it's one of those things you have to think about when manipulating data.

It's been like that ever since I started developing in NAV, only on SQL Server it is enforced much more strictly.

Mauddib · 2007-09-20

Thanks Denster,

Ok forgetting about the code in my first post and look at my second post. This is a simple bit of code that I did not write but I have seen it in many implementations in many clients. A change, modify or delete to a sales line updated the "Last changed by" field in the header.

Deleteing the sales order does a delete(true) on the lines which in turn updates the header again. In all the versions I listed above this has always worked. In version 5 it suddenly does not however.

As you said these things happen, you learn to code better to avoid them, and you move on. However it would be interesting to at least understand the version difference and why this happens.

mrQQ · 2007-09-20

i have to agree with DenSter. it's not a problem with database engine, in fact, in this case database engine does exactly what it should do. this is problem with code - you cannot just modify a record without refreshing it!

DenSter · 2007-09-20

It is wrong to begin with, it should have caused errors in previous versions as well. I guess now they closed that loophole. Maybe they didn't mean to do it so they didn't document it.

I don't know really. I could speculate, but I'd be called a liar

DenSter · 2007-09-20

About the line modifying the header while in a trigger of the header. This should only be possible if the header record was sent into the line logic by reference (i.e. with the VAR field turned on in the parameter list), or if in the header trigger the header record was refreshed.

It's the same principle. While in the header the record is 'version A'. Then in the line it is changed to 'version B' (with different value in certain fields). After completing the line logic, the database only knows about 'version B', but the header logic still thinks it's 'version A'. There is a difference between versions A and B, and a MODIFY on that version A shoud error out. If it doesn't, you have a serious problem, because it will overwrite the fields that the line changed back to their original values, and that goes straight against what you want to do with MODIFY in the line logic.

Mauddib · 2007-09-20

Someone else wrote this code in the client I am currently working on at the moment. In the ondelete of the header there is after filtering:

IF SalesLine.FIND('-') THEN
  REPEAT
    SalesLine.DELETE(TRUE);
  UNTIL SalesLine.NEXT = 0;

and in the onDelete() of the salesline there is:

SalesHeader.GET("Document Type", "Document No.");
SalesHeader."Last change by" := USERID;
SalesHeader.MODIFY(FALSE);

This kind of looping is bad code, I agree. I would not have written it. However this isnt my point. Im just curious why this would work fine in all versions except version 5 that Ive tested it on.

mrQQ · 2007-09-20

you got me - i don't know..

perhaps stricter version policy enforcement?

DenSter · 2007-09-20

Mauddib wrote:

However this isnt my point. Im just curious why this would work fine in all versions except version 5 that Ive tested it on.

I see your point. MY point is that it should not be possible to run on previous versions either. I've been dealing with errors just like these on SQL Server in many versions.

DenSter · 2007-09-20

Oh and by the way... instead of this:

IF SalesLine.FIND('-') THEN 
  REPEAT 
    SalesLine.DELETE(TRUE); 
  UNTIL SalesLine.NEXT = 0;

you can do this:

SalesLine.DELETEALL(TRUE);

You don't even have to do a FIND, because if there are no records in the filter, it won't delete anything.

Version 5: changes to record since retreival from database.

Comments

Categories