12-16-2010 01:20 PM
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.
12-18-2010 08:32 AM
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.
12-18-2010 11:37 AM
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.
12-19-2010 05:37 AM
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 , I did a quick search on 6.2.6 Known Defects and found the following:
The Replace Data Wizard may not replace data in a contact group with an OR condition in the query.
Replace Data wizard does not alter the ModifyDate
Replace Data Wizard may not work when changing a field where the Group is based on that field
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.
12-22-2010 10:36 AM
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.
12-22-2010 12:19 PM
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.