The scenario is you have created a SQL view for use in a Crystal Report. Your end users are excited about the report but then you started getting phone calls from your remote users that the report will not run. You realize that the view you used to help organize your data did not synchronize out to your remote user base. After some looking around on CustomerFX.com you realize that views do not Synchronize out Remote databases. Your options are Cut New Remote databases or some how get the views to sync out. Then you discovered Kris Halsrud’s blog on this exact topic written on May 13th called Distributing SQL Views to Remote SalesLogix Databases.
Kris’s post provided the answer to what I was looking for in preparation for the SalesLogix Reporting class I am preparing. This issue is something I have been trying to overcome throughout my SalesLogix reporting career. Since my goal is to write to the “Beginner” level of SalesLogix reporting I thought I would break down the process that Kris describes for the new Administrator or Report Writer.
The concept of what Kris writes about is that since changes to a detail view in SalesLogix synchronize out to remote databases, we can write a script that runs whenever a detail view opens. This script basically checks to make sure the view exists. If the view does not, the script creates the view. Once the change is saved and released, the script will synchronize out to the remotes and your report will run.
In order to accomplish what I am writing about today you must have access to the Sage SalesLogix Architect and the tables that support the view must be available in the remote database.
Our target detail view is the SLX Crystal Report Viewer. This is the screen that comes up when you select the report button on your nav bar.
You can open this view in the Architect using the File>Open Plugin menu item.
When the “Choose Plugin” screen opens, click on the System listing in the tree control on the left side of the view, and look for and select the the “SLX Crystal Report Viewer” from the list on the right.
When the form opens in the Architect look for and select the Script Tab.
There is some form information in gray text on the top of the script. Just below you see the word “Globals”. Insert the following text under that word.
Const SLXPW = “your_sysdba_password”
The phrase in the script, “your_sysdba_password”, will need to be changed to match your SQL server sysdba password.
Scroll down the script and search for the sub “AxFormOpen”.
Directly under the “AXFormOpen” line enter the following text:
FXCheckViews
application.BasicFunctions.ProcessWindowMessages
Any existing text will come after these two lines. If more then one view is required to be created then you could use different versions of the FXCheckViews text. It could look something like this:
FXCheckViews
FXCheckViews2
application.BasicFunctions.ProcessWindowMessages
Now scroll all the way to the bottom of the script and insert the following code:
‘ CFX Begin Script
Sub FXCheckViews
dim objrs
Set objrs = GetNewRecordset()
objrs.Open “select id from sysobjects where name = ‘NAMEOFVIEW’ and type=’V'”
if(objrs.eof or objrs.bof) then ExecuteNativeSQL(“CREATE view NAMEOFVIEW as SELECTSTATEMENT”)
Set objrs = nothing
End Sub
Function GetNewRecordset()
Set GetNewRecordset = CreateObject(“ADODB.Recordset”)
Set GetNewRecordset.ActiveConnection = GetNativeConnection()
GetNewRecordset.CursorLocation = 3 ‘adUseClient
GetNewRecordset.CursorType = 3 ‘adOpenStatic
GetNewRecordset.LockType = 3 ‘adLockOptimistic
End Function
Function ExecuteNativeSQL(ByVal SQL)
Dim objConn
Dim lRecs
On Error Resume Next
lRecs = -1
Set objConn = GetNativeConnection
objConn.Execute SQL, lRecs, 128 ‘adExecuteNoRecords
If Err.Number <> 0 Then RaiseError “executing SQL statement”
On Error Goto 0
Set objConn = Nothing
ExecuteNativeSQL = lRecs
End Function
Function GetNativeConnection()
Dim objRS
Dim connstring
Dim cn
Set objRS = Application.GetNewConnection.Execute(“slx_getNativeConnInfo()”)
connstring = objRS.Fields(0).Value & “;Password=” & SLXPW
objRS.Close
Set objRS = Nothing
Set cn = CreateObject(“ADODB.Connection”)
With cn
.ConnectionString = connstring
.Open
End With
If Err.Number <> 0 Then RaiseError “opening ADO connection object”
On Error Goto 0
Set GetNativeConnection = cn
Set cn = nothing
End Function
The first sub in the script you just inserted needs some adjustment.
Sub FXCheckViews
dim objrs
Set objrs = GetNewRecordset()
objrs.Open “select id from sysobjects where name = ‘NAMEOFVIEW‘ and type=’V'”
if(objrs.eof or objrs.bof) then ExecuteNativeSQL(“CREATE view NAMEOFVIEW as SELECTSTATEMENT“)
Set objrs = nothing
End Sub
The text ‘NAMEOFVIEW’ appears twice in the sub routine and should be replaced with name of the view as it appears in the SQL database.
The text SELECTSTATEMENT should be replaced with the actual SQL statement used in the view. This needs to be formatted to display on one line. I formated this by copying the SQL Statement into a text document and deleted out all of the carriage returns. Then I pasted the formatted text into the script making sure to just replace the SELECTSTATEMENT text. Here is an example:
Sub FXCheckViews
dim objrs
Set objrs = GetNewRecordset()
objrs.Open “select id from sysobjects where name = ‘vAccountDescription’ and type=’V'”
if(objrs.eof or objrs.bof) then ExecuteNativeSQL(“CREATE view vAccountDescription as SELECT a.ACCOUNTID, a.DESCRIPTION from ACCOUNT“)
Set objrs = nothing
End Sub
Notice how the word SELECTSTATEMENT is gone, the SQL Statement is on one line only.
If you have more then one view being created you want to create a sub routine for each. So in the example of above where there is the FXCheckViews and FXCheckViews2, we want the sub FXCheckViews and another sub for FXCheckViews2. Together these would look something like this.
Sub FXCheckViews
dim objrs
Set objrs = GetNewRecordset()
objrs.Open “select id from sysobjects where name = ‘vAccountDescription’ and type=’V'”
if(objrs.eof or objrs.bof) then ExecuteNativeSQL(“CREATE view vAccountDescription as SELECT ACCOUNTID, DESCRIPTION from ACCOUNT”)
Set objrs = nothing
End Sub
Sub FXCheckViews2
dim objrs
Set objrs = GetNewRecordset()
objrs.Open “select id from sysobjects where name = ‘vContactType’ and type=’V'”
if(objrs.eof or objrs.bof) then ExecuteNativeSQL(“CREATE view vContactType as SELECT ContactID, Type from Contact”)
Set objrs = nothing
End Sub
Any good SQL script will work. This really opens up you ability to use SQL to organize your data for use in SalesLogix reporting or Group building.
Subscribe To Our Newsletter
Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!
You have Successfully Subscribed!