The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Can I update the Contacts AcountID directly with a SQL update statement?
Posted: 22 Dec 08 12:17 PM
|
Hello,
Ive hit an issue where a user has managed to Mas forward contacts to a single account. So what we have now is many contacts pointing to the incorect account. I have come up with a SQL query that has found these contacts based on matching the contacts address to an account address. Therefor I have the correct account ID for each of the contacts with the problem.
My question is "What is the best method of correcting the contact account ID?"
Im entertaining the idea of just running a strait up SQL update to modify the AcountID for each contact.
I wanted to seek any advice before performing the attempt.
Thank in advance
Robert Waite |
|
|
|
Re: Can I update the Contacts AcountID directly with a SQL update statement?
Posted: 23 Dec 08 2:18 AM
|
Firstly, remember that if you have remotes, you'll need to use Tools / Execute SQL in the Administrator client to get the SQL to sync ...
To put your mind at rest about maintaining database integrity, I suggest you get yourself a test db, select a contact, turn on SLX Profiler, choose the Edit / Move function and then check the results in Profiler. This will show you exactly how the system would move a contact and will, hopefully, ensure that you do not miss anything.
Phil |
|
|
|
Re: Can I update the Contacts AcountID directly with a SQL update statement?
Posted: 23 Dec 08 7:48 AM
|
Much thanks Phil for the reply,
I'm new to working with SLX and my company is too frugal to send me out for training on the development side... (The stuff I love doing)
If I'm reading this right I should first figure out how to use the profile. (I would be great full if you could put me in the right direction in finding documentation on it) and from there it will depict how SLX performs the data updates. Then armed with that knowledge I should execute the SQL updates in administrator rather then SQL management studio as we do indeed use remotes.
Robert |
|
|
| |
| |
|
Re: Can I update the Contacts AcountID directly with a SQL update statement?
Posted: 23 Dec 08 12:44 PM
|
Seems like there is allot of Inserts and Updates going on for a simple movement of a contact code to a different account.
I may be able to get this all into a stored procedure but I am now wondering if using a COM reference in a .NET console program would be the safest way of handling this.
Ive reference all the SLX COM references and performed a performed a search on contact in the Object browser to see if I might find a magical function I could tap into the Business Layer to handle the data changes. But I did not find one.
Would this be a possibility? Can anyone steer me in the right direction?
|
|
|
|
Re: Can I update the Contacts AcountID directly with a SQL update statement?
Posted: 23 Dec 08 7:58 PM
|
Yep - it's not as simple as it appears because there are several related tables. As you are new to SLX you did well to ask the question here - just updating AccountID may appear to work, but it would probably orphan other records in the system, or at least leave some inconsistent records behind.
Although there is no 'magical function', there is a place that you could start and that is the
System:MoveContact form
(as long as you are V7+)
You could adapt the code behind this form to perform the move automatically, by creating a new custom function
I suggest that you do not use COM or stored procs. Updates performed in stored procs do not sync out to remotes.
Phil |
|
|
|
Re: Can I update the Contacts AcountID directly with a SQL update statement?
Posted: 24 Dec 08 10:13 AM
|
It never is as simple as it appears and I have enough experience to know that hasty fixes often results in huge headaches. which is why I want to find out how experienced SLX developers would handle these issues before I get trigger happy.
Question: If a Stored Procedure was used to fix the issue would cutting new databases resolve the remote issue?
We are getting the web client set up and will discontinue use of remote clients once we have that set up.
I'm going to look into finding the code behind for the System:MoveContactForm... I am hoping that I can host the function build from that code can be hosted in a .NET application as it is my preferred platform of implementing customizations.
|
|
|
|
Re: Can I update the Contacts AcountID directly with a SQL update statement?
Posted: 25 Dec 08 3:20 AM
|
Originally posted by Robert Waite
--snip--
Question: If a Stored Procedure was used to fix the issue would cutting new databases resolve the remote issue?
--Snip--
|
|
Yes - the amended data would be contained in the new remote database.
The code in System::MoveContact is written in VBScript, not VB.NET, so moving to .NET would require a near total rewrite
Phil. |
|
|
|
Re: Can I update the Contacts AcountID directly with a SQL update statement?
Posted: 29 Dec 08 10:30 AM
|
Ive found the code behind for the System:MoveContact and I have been analyzing the code for the InitForm Sub
I cannot find any reference that points to the SLX_DB class nor can I find any methods named GetNewRecordSet anywhere in the object browser.
Is this a method that only supports late binding?
objSlxDb = New SLX_DB
ConRS = objSlxDb.GetNewRecordSet
If I perform all my Updates using the SalsesLogix OLE DB provider will my remotes sync?
Sub InitForm(ByVal contactid)
Dim strSql, Contact, ConRS
'I cannot find an SLX_DB or any methods for GetNewRecordSet
'Anywhere in the object browser. I have referenced any known
'SLX refererence I can find.
objSlxDb = New SLX_DB
ConRS = objSlxDb.GetNewRecordSet
strSql = "SELECT CONTACTID, FIRSTNAME, LASTNAME, ACCOUNTID, ACCOUNT FROM CONTACT WHERE CONTACTID = '" & contactid & "'"
ConRS.Open(strSql, objSlxDb.Connection)
If ConRS.Fields("CONTACTID").Value <> "" Then
Contact = ConRS.Fields("LASTNAME").Value
If Contact <> "" Then
Contact = Contact & ", "
End If
Contact = Contact & ConRS.Fields("FIRSTNAME").Value
newaccountid = ConRS.Fields("ACCOUNTID").Value
oldcontactid = ConRS.Fields("CONTACTID").Value
newaccount = ConRS.Fields("ACCOUNT").Value
End If
oldaccount = newaccount
oldaccountid = newaccountid
doLessOptions()
populateAssignTo(oldaccountid)
ConRS.Close()
newcontactid = oldcontactid
ConRS = Nothing
End Sub |
|
|
|
Re: Can I update the Contacts AcountID directly with a SQL update statement?
Posted: 30 Dec 08 8:56 AM
|
The System:SLX Database Support Script has the SLX_DB class, et al references.
We all found out the hard way that the only way to synchronize a move is to delete and add all necessary records.......it's kind of like a snowplow leading the way (the delete statement) and then you bring in the new record which is pointing to the new accountid.
Buy DevLogix immediately, big time saver/helper/training tool. |
|
|
| |
| |
|
Re: Can I update the Contacts AcountID directly with a SQL update statement?
Posted: 31 Dec 08 8:13 AM
|
A quick way to verify you have hit all the needed tables is to access the SECTableDefs
SELECT * FROM sysdba.sectabledefs where fieldname in ('contactid', 'entityid')
SELECT * FROM sysdba.sectabledefs where fieldname in ('accountid', 'entityid')
For a first run through on data checking: Use the Integrity Checker. That will give you an idea if the base table data is in order. However if you have customs; I would suggest you not run the "fix' portion without a strong understanding of your specific data structure and business rules.
If it is of any value at this point  : I've built similar functions but started with the creation of views based on the sectabledefs information. Then built a script that ran a loop with a couple If / Then statements using the view data as my table and field variables.
Carla |
|
|
|
Re: Can I update the Contacts AcountID directly with a SQL update statement?
Posted: 31 Dec 08 9:15 AM
|
Carla,
I am very greatful for your advice and I will look into the sectabledefs when I get back on friday. I will also run the integrity checker on my test environment before i hit the production database.
At this point SalesLogix is stock without any customizations or defined business rules. It is just an expensive phone book at this point. Ive ordered the DevLogix III.7 and it is in the mail. I very much look forward to perusing it. I love development and I cant wait to sink my cerebral teeth into a new platform and integrating it to our MAS 200 system.
Robert |
|
|
| |
| |
|