Login / Register  search  syndication  about

          George Jensen's Blog

George Jensen on Reporting, Business Intelligence, Crystal Reports, Visual Analyzer, and more.

Sync Views out To Remotes

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. 

 

What's This?
  
Bookmark and Share

About George Jensen

   George Jensen is a Senior Developer for Customer FX Corporation.


Related Content
   Video: Demystifying Infor CRM (Saleslogix) Updates
Watch it now! If you missed yesterdays demo, "Demystifying Infor CRM (Saleslogix) Updates" ,
Posted on Sep 26, 2014 by Brianna Ojard to The Inbox
 
   Thoughts on the Future and Involvement in the Saleslogix/Infor CRM Community
Over the last year and a half I've been working on another product named Contatta. It's a great p
Posted on Sep 25, 2014 by Ryan Farley to Ryan Farley's Blog
 
   Fixing the Orphaning of Salesfusion Data when Converting Leads to Contacts in Infor CRM (Saleslogix)
We have had a couple of client recently come to other with the same problem-When you convert a Lead to a
Posted on Sep 19, 2014 by Kris Halsrud to Kris Halsrud's Blog
 
   Official Infor CRM Acquisition FAQ
What to expect now that Saleslogix is Infor CRM. Like many of you, it will take some time (probably a
Posted on Sep 18, 2014 by Brianna Ojard to The Inbox
 
   ComboFix Kills Saleslogix Sync
ComboFix, a third party malware utility, will alter the Operating system so .qts and .que files will no l
Posted on Sep 12, 2014 by Brianna Ojard to SalesLogix Support
 
Comments

 

Twitter Trackbacks for Sync Views out To Remotes - The Reporting Blog [customerfx.com] on Topsy.com said:

Pingback from  Twitter Trackbacks for                 Sync Views out To Remotes - The Reporting Blog         [customerfx.com]        on Topsy.com

September 18, 2009 3:57 PM

Leave a Comment

(required)  
(optional)
(required)  
Add
All contents Copyright © 2014 Customer FX Corporation
Customer FX Corporation
2324 University Avenue West, Suite 115
Saint Paul, Minnesota 55114
Tel: 800.728.5783

  Follow @CustomerFX on twitter
Follow the best news, tips, and articles
  Subscribe to Customer FX on youtube
Watch SalesLogix tutorial videos from Customer FX
Login / Register