Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
Copper Elite Contributor
Posts: 33
Registered: ‎08-05-2009

Re: "Replace Data" function has stopped working in Version 6.2.3

In the SalesLogix folder there is a SLXProfiler.exe.

You can see all the SQL statements running through the OLEDB provider to the database.

 

When you are running the Replace Data Wizard somewhere the statement will be stuck

and you/we can identify what was going wrong. That helps to get to the bottom of (y)our problem.

 

Hartog

 

 

Silver Contributor
Posts: 835
Registered: ‎03-24-2009

Re: "Replace Data" function has stopped working in Version 6.2.3

One of the hardest lessons for us all (and especially Larry) is that the Replace Data function is an UPDATE function. Is not an Insert if no record exists\Update if the record exists process.

 

The NUMBER ONE reason for the replace data function to not work is that the 1:1 extension record does not exist in the first place.  In your group that you are using to Replace the data, make sure you have an INNER JOIN between the Contact table and the extension table, and that you display the ContactID from the extension table in the Group.

 

Number 2 reason: ownership\SECCODEID is different between the two tables (if it even exists in the extension table). NULLs are killers.

 

Number 3 reason: failure to handle NULL's and "" correctly in the where clause. NULL DOES NOT EQUAL "".

Coalesce or IsNull should always be used (or doesn't contain data for an SLX Group Query built clause.

 

Number 4 reason: allowing access to ANY USER (other than Admin ) for any of the Tools Maintenance kind of functionality. Heck, we've gotten rid of DELETE capabilities for the major entities.....send an email request\SLX status change to delete and we'll archive\hide the record.

 

I don't specifically recall Replace data failing IN version 6.2.3, Nor do I recall it working in 6.2.2, not working in 6.2.3, and then  working in 6.2.4. Not saying that what you and Larry are stating is **bleep**. Just that we long ago moved on to 6.2.6 at all of our customers and didn't have any issues with replace data if you are actually executing a valid UPDATE SQL statement against records that actually exist in the database.

RJ Samp
Gold Super Contributor
Posts: 3,087
Registered: ‎03-19-2009

Re: "Replace Data" function has stopped working in Version 6.2.3

To add.....

    What RJS stated about 1:1 is the reason many of us (BP's) stopped creating 1:1 long ago (yes, even before Sage SalesLogix "supported" adding custom fields to the core/OOTB tables). Now if you want to continue to use 1:1 and do replace data actions against them you can:

     a - follow what RJS posted on how to make the proper join for the group for data replacement

     b - create ALL the "missing" 1:1 records.

 

I have a tool that will do "b" very easily (and yes, the new records will sync). I'll dig it out a post a link to it for anyone who wants to use it.. but ZERO guarantees that it is fit for anything.. and ZERO guarantees that it will not "damage" a system.

 

Also, nobody who is running a 6.2.x should be running less than 6.2.6 because of the (very) severe issues involved w/Microsoft's changes in their Timezone/DST data changes. Since the change in SalesLogix to a TimeZone/GMT sensitive system (which is the correct way all db's should be - SalesLogix or not) and prior to 6.2.6, you will have this (DST) problem.

NOTE: Some of you may not know this but even if you are no longer on M&S and are running a valid 6.2.x system, you are entitled to obtain a copy of 6.2 SP6 at no cost. This is a special exemption and is documented in writing by Sage SalesLogix.

--
RJ Ledger - rjledger@rjlSystems.net +1 603.369.3047 x101

".. Innovators in Mobility - Experts in Workflow Automation..."
http://www.rjlSystems.net - blog: www.rjlSystems.net/blog.html
Copper Elite Contributor
Posts: 153
Registered: ‎02-27-2010

Re: "Replace Data" function has stopped working in Version 6.2.3

RJS,

 

First, thanks for the incredible post. While I am familiar with all the issues you listed (from painful trial and error and resolution via Exec SQL) you've done a great job of summarizing them including the reason behind them and the things an admin can do to minimize them! And we don't allow Delete in the major entities either.

 

But in order to protect my good name (too late Smiley Happy, I did a quick search on 6.2.6 Known Defects and found the following:

1-25815

The Replace Data Wizard may not replace data in a contact group with an OR condition in the query.

1-38773

Replace Data wizard does not alter the ModifyDate

1-45766

Replace Data Wizard may not work when changing a field where the Group is based on that field

1-49574

Replace data wizard may not replace a period or decimal

 

I'm not sure if these are consistent with your FAD-ITDD premise (Functioning As Designed - It's The Data, Dummy), but I may return to this unruly Wizard to see if I can find some additional IN(ot)TDD issues that haunted me in the past.

 

Thanks again,

Larry

Copper Elite Contributor
Posts: 153
Registered: ‎02-27-2010

Re: "Replace Data" function has stopped working in Version 6.2.3

I am delighted to report that I've just successfully used the Replace Data wizard to update 2 custom fields in a 1-to-1 extension table that did have matching records for every Account (but no seccodeid) and the same at the Contact level, which even included an "OR" clause. (i.e. testing 6.2.6 defect in 7.2.2). The modifyuser/date is still not updated but that's almost preferred, but not! In total, the custom fields of nearly 30,000 records were updated without a hitch.

 

Armed with RJS's insights and the list of the defects, I felt sufficiently comfortable returning to this utility that had frustrated me for years. I will still be on the lookout for gotcha's but I'll use the Wizard over Execute SQL whenever I can. BTW, I only use the Replace Data functionality, not the Search, Switch or Copy options which may or may not be impacted by the OR statement. The other 3 defects are not a critical concern to me at this time.

 

Thanks,

Larry Esposito

Copper Super Contributor
Posts: 23
Registered: ‎12-11-2010

Re: "Replace Data" function has stopped working in Version 6.2.3

Congratulations, Larry. It's always nice to hear about someone's success.

 

Not being as technically conversant as you and some others, I took the coward's way out. I renamed three of the standard SLx User Fields, then copied the data from my linked file to user fields 1, 2 and 3 in the main Contact File.  Copying the data worked fine (unlike the group replace). I then updated the data in the Contact File user defined fields using groups. Since I have a lot of group filters already using the original fields, I finally copied the data back from the SLx User fields to my linked user fields. I then changed the plugin forms to enter and update the new fields. As I use the filters, I'll change them to refer to the new data fileds.

 

This is obviously not a very sophisticated solution, but IT WORKS - and it only took a couple of hours to think it out and execute the procedures.

 

Thanks to all of you who helped with this problem.