08-29-2014 12:31 PM
We have an issue where basic SQL Update statements are not applying properly from a remote back to the host machine. Inserts work fine. Inside TrnViewer, I see there are back-to-back SQL statements of the following (these were generated from closing out an Opportunity). How would these statements ever get applied from remote to host if there is any lag time whatsoever on the user syncing back with the host? If anyone touches the Opportunity at the host site between when it was sent to the remote and when they sync it in, that update will fail because the modify date on the host won't match whatever the modify date was on the remote when they closed out the Opportunity. Am I missing something here? When SyncServer runs is it actually executing that ENTIRE where clause instead of just using the OpportunityId?
1.) UPDATE OPPORTUNITY SET ACTUALAMOUNT=?,ACTUALCLOSE=?,NOTES=?,REASON=?,MODIFYUSER=?,MODIFYDATE=? WHERE OPPORTUNITYID=? AND ACTUALAMOUNT IS NULL AND ACTUALCLOSE IS NULL AND REASON IS NULL AND MODIFYUSER=? AND MODIFYDATE=?
2.) UPDATE OPPORTUNITY SET CLOSED=?,CLOSEPROBABILITY=?,NOTES=?,STATUS=?,DAYSINPIPELINE=?,REASON=?,MODIFYUSER=?,MODIFYDATE=? WHERE OPPORTUNITYID=? AND CLOSED=? AND CLOSEPROBABILITY=? AND STATUS=? AND DAYSINPIPELINE=? AND REASON=? AND MODIFYUSER=? AND MODIFYDATE=?
08-29-2014 12:36 PM - edited 08-29-2014 12:36 PM
09-10-2014 05:48 AM
This issue still comes up sporadically. I've run several sync cyles doing the following:
*grabbing a snapshot of the Opportunity data before sync
*turning on SlxProfiler to capture every SQL statement (and then running Sync)
*grabbing a snapshot of the Opportunity data after sync and comparing the database data pre-sync/post-sync as well as what the remote user changed (via TrnViewer on the INFILES that SyncServer processed)
It usually runs flawlessly (everything the remote user did is applied).
There was one time where 7 of the 8 fields updated on the Remote were applied to the HOST (1 was not). I believe this was due to Conflict Resolutions rules, which are [1 - Network Client; 2- Most Recent Change; 3 - Not Used].
So far so good. But there was 1 record where literally nothing applied to the HOST.
On the record where 7 of the 8 field changes were correctly applied, it appears SyncServer ran a large number of consecutive SELECT/UPDATE statements trying to apply every individual data update that it could (which I assume was the Conflict Resolution logic kicking in).
On the record where NO fields updated, all Sync did was run a few SELECT statements and it never bothered to UPDATE anything. The record in question has a ModifyDate (from a Network user) from a few days before the Remote User closed out the Opportunity. But it doesn't look like the Network user updated any of the information that remote user updated - so I don't think Conflict Resolution would cause this???
Any ideas on what could be happening? I have a backup of ConfTran.stm AFTER I ran sync (it shows ONLY the data from the remote user -- ie the changes she made that did not get applied). I did not think to backup ConfTran.stm BEFORE I ran sync.
10-29-2014 09:15 AM
I have had this problem with 7.5.1 and have tested it in 8.1 LAN as well.
The problem is with the setting the conflict rule to 'network user wins' , 'is the owner of record' , 'made the most recent change'
If you set the rule like this then some fields will not sync back to the host from the remote. ie one of the problem fields is the opp comments as well as others. I don’t have a full list but you will start to lose changes from remotes on certain opportunity fields.
I have reported this to Infor/Swiftpage and I have replicated this issue in 7.5.1 and 8.1 so I believe it’s been there for some time and never been reported to Infor/Swiftpage