Data ends up on wrong record.

[pjames95003]

Hi,

We are on 7.2.2 LAN version with remote office and remote users.  Over the past year or so, I have received a few complaints that data entered on one record is ending up on a different record.  It seems to be just Opportunities. I kind of put it down to the user just mistakenly updating the wrong record since there was nothing I could find to indicate a problem. 

The issue was first reported as "I updated the comments on Opty XYZ, and after I synced, I see that the comments that I put on Opty XYZ now show up as comments on Opty ABC instead!".    It is always a remote database user (but not remote office) who reports the problem, but that may not be meaningful because the remote users are the only ones who would be updating this fields. 

Today, a different user has noticed the problem on several records. In addition to the comments being on the wrong Opty, the Account Manager on the Opty is also wrong.  So, now the comments AND Account Manager from Opty XYZ are showing up as the comments and account manager on opty ABC.  The Account Manager from opty ABC now shows a different account manager.  The actual accounts still show the correct account manager.  

Looking at the modify dates of the above two optys, it looks like this probably happened during SQL update statement run through the admin tool.  The statement would have been....

Update Opportunity

Set STATUS = 'Open' where STATUS = '01 - Open'

Any thoughts?

[DanCarvin]

First thought...    I've read a warning here that a lot of complex code is triggered by the value in Opportunity.Status so it be best to avoid messing with the OOTB picklist values since that may have unexpected effects.

Next, it sounds like a global variable is not being cleared at some point when it should, which is causing incorrect IDs to be written to records.  You may need to work with a remote user to reproduce the glitch, then follow the process through the code, paying particular attention to custom changes that have been made to the code in Opportunities.  Look for anything that might cause a GlobalInfoClear to be skipped or other variable with an ID to not be changed.

[MikeSpragg]

"First thought... I've read a warning here that a lot of complex code is triggered by the value in Opportunity.Status so it be best to avoid messing with the OOTB picklist values since that may have unexpected effects."

You are right - scattered throughout SLX are assumptions on the status of "Open", "Closed - Won" and "Closed - Lost". You can add others - but these need to remain as they are *OR* you need to hunt these status's (statii ?!) and change them - otherwise, if you close an opp - you won't invoke the close script/form on Won/Lost etc.

Regards
Mike

[pjames95003]

Thanks for your responses Gentlemen.  I have noticed that on the back end, there is some mystery about the behavior of changing the status values, so haven't really fiddled with the default values in the list.  I wouldn't have thought that using the SQL tool in Admin to change the value of the Status field from one that isn't in the SLX picklist to one that is, and one that is an out of the box value in fact, should affect anything except that specific field.  The issue we had was that when we migrated from See-Bull to SLX, we brought our legacy values over, and they are not in the LOV in SLX. ( I know, I know, I know..... ). 

Anyway, we just wanted to do some clean-up, and perhaps I don't understand the admin tool, but my understanding is that it by-passes the application and hits the database directly, and that by running the SQL through the admin tool, you are able to send the same SQL commands to other remote databases to be executed directly against those SQL databases resulting in consistent data.  If that is so, I don't see how the SQL update command could affect any fields other than the ones in the SQL statement. 

Am I mis-informed or are your words of guidance more in the way of,  "Yikes, tread carefully near that field!"?

Thanks again.

Patrick

[MikeSpragg]

You are correct, the Admin SQL command does simply update the database (as if you were using SQL Studio etc) but, it works through the provider (which has limitations like update from etc. not being supported) and it does sync those changes out to the remotes as you say. The situation comes down to this:

Open
Closed - Won
Closed - Lost

are expected to be part of the opportunity.status picklist and column data. The application looks for these specifically to know when to call the opportunity close out views, it's used in the forecasting module and in other areas of the app/3rd party integration etc.

I have, as I suspect many others have, changed this picklist/data to suit the needs of the customer - it's simply a case of knowing that this particular change can have consequences and you'll need to allow for those (by changing scripts etc). For example:

01-Open
02-Closed - Won
03-Closed - Lost

Is not the same. If you change to this and select "03-Closed - Lost" you will find that the opportunity is not closed and the opportunity close out view is not invoked. It only "says" it's closed.

Regards
Mike