Customer Table Modification Problems

markodrummer

Hi Everyone. We're coming across an interesting issue in one of our databases. Here's the setup:

Nav 4.0 SP2
SQL 2005 Std.
Server 2003 R2

On only one Customer Record (that i know of) whenever someone tries to edit anything on the Card they get the:

"Another User has modified the record for this Customer after you retrieved it from the Database" I've looked through all the custom code and cannot find anything that would point to this type of error. I finally rebooted the server for updates thinking this would clear whatever lock someone has on this record. However the error still exists.

Any ideas out there?

Thanks!

Savatage

do you have currform.updates and/or similar on the card? if so what triggers?

markodrummer

No UPDATE, UPDATECONTROLS, etc...

There is a CALCSUMS on the OnAfterGetRecord() Trigger. Like i mentioned, its only happening for one specific record.

themave

I have this happen sometimes, but it is not just with a specific customer record.

it happens, because I have a job on the job queue that updates certain customer data.
if in the 10 - 15 seconds it take to run the job, a person is editing a customer record, they will receive that error. Which is should be doing since that is exactly what happened, another user did edit the record.

so the quesion would be, is this a major customer where there might actually be two people in the same customer card at the same time

JosephGress

Does this happen in a test environment where you are the only user? Does it happen every time? If so, I would try running code coverage and look at all the code that gets executed when you edit the record. Then comment out any code that might modify the customer table until you find what is causing the error.

Savatage

as above comment out your code, if you get no error then your code has problems.
What are you calc summing on the customer card?

markodrummer

JosephGress wrote:

Does this happen in a test environment where you are the only user? Does it happen every time? If so, I would try running code coverage and look at all the code that gets executed when you edit the record. Then comment out any code that might modify the customer table until you find what is causing the error.

Does not happen in a Test DB - however its quite a bit older. Yes - it happens everytime for that one customer Record.

I'll look at the Code Coverage to see if that shows anything.

Savatage wrote:

as above comment out your code, if you get no error then your code has problems.
What are you calc summing on the customer card?

Quantity on the Item Ledger Entry field - i do a Calc on the value returned.

Savatage

can we assume it's a calc that can't be accomplished via a flowfield?

markodrummer

Savatage wrote:

can we assume it's a calc that can't be accomplished via a flowfield?

Here is the logic:
(grecItemLed = Global Item Ledger Entry Record var)

grecItemLed.RESET;
grecItemLed.SETCURRENTKEY("Entry Type","Item No.","Variant Code","Source Type","Source No.","Posting Date");
grecItemLed.SETRANGE("Source No.","No.");
grecItemLed.SETRANGE("Entry Type",grecItemLed."Entry Type"::Sale);
grecItemLed.SETFILTER("Posting Date",'>=%1&<=%2',CALCDATE('-1Y',TODAY),TODAY);
grecItemLed.CALCSUMS(Quantity);

Not sure why this would cause an issue?

markodrummer

Anyone have any further ideas on this?

matttrax

You could try running a SQL Trace on the user login while trying to update the record, although I'm not sure that this is any different than running Client Monitor.

You could also add a code to a SQL trigger (since there is no guarantee the MODIFY is being called as a MODIFY(TRUE) ) to update a field with the user id of the last person to change the record.

Just some thoughts. Not really a SQL expert so I can't tell you exactly how to do either of those.

markodrummer

I think I may have it narrowed down. I forgot about the Contact/Customer Sync which happens on Modify of a Customer Record. The onModify() Trigger on the Customer Table calls UpdateContFromCust.OnModify(Rec).

By the looks in the Client Monitor - the onModify() trigger happens 3 times on modifying a record.

markodrummer

As well - I restored the Database into a new Test version and the error does not happen. Just to make things more strange...

Alex_Chow

Since the problem occurs on the customizations you've made, it's difficult to address the problem for you.

What I would do is export the form and the table into .txt and search for MODIFY or UPDATE.

markodrummer

Alex Chow wrote:

Since the problem occurs on the customizations you've made, it's difficult to address the problem for you.

What I would do is export the form and the table into .txt and search for MODIFY or UPDATE.

Well - were not sure of that yet. The OnModify code being hit is the base Contact/Customer Sync code - not modified. The only code added is the CALCSUMS - which still begs the question of why it only happens to one single record, and doesn't happen when the DB is restored into a test version.

I'll look further at the code and post any additional findings.