Follow @dporton

JustCode : Adding AutoAnswer Hints to your MDD

This code shows you how to add AutoAnswer Hints to your MDD files.

 
Metadata(en-AU, Question, Label)
    AUTOANSWER lcl(en-AU, AutoAnswer, Label);
    '### END LCL MAP ###

    HDATA -
    [
    Template = "DefaultLayout.htm",
    TemplateLocation = "Hints_files"
    ]
    AUTOANSWER:-;

    Email "Please enter email address"
      AUTOANSWER:-
      AUTOANSWER: [ Value = "smarterDimensions@live.com.au" ]
      text;

    SingleResponseQuestion ""
      AUTOANSWER:-
      AUTOANSWER: [ Value = "D" ]
    categorical [1..1]
    {
      A "A" AUTOANSWER:-,
      B "B" AUTOANSWER:-,
      C "C" AUTOANSWER:-,
      D "D" AUTOANSWER:-,
      E "E" AUTOANSWER:-,
      F "F" AUTOANSWER:-
    };

    MultiResponseQuestion ""
      AUTOANSWER:-
      AUTOANSWER: [ AllowedCategories = "A,B,C" ]
    categorical [1..]
    {
      A "A" AUTOANSWER:-,
      B "B" AUTOANSWER:-,
      C "C" AUTOANSWER:-,
      D "D" AUTOANSWER:-,
      E "E" AUTOANSWER:-,
      F "F" AUTOANSWER:-
    };

    NumericQuestion ""
      AUTOANSWER:-
      AUTOANSWER: [ Max = 10, Min = 20 ]
    long;
End Metadata

JustCode : Delete cases from the input file of a dms script

This script shows you how to delete a record in your input file. The delete is done in the OnNextCase

' ****************************************
' Designed by : Smarter Dimensions
' Last Updated : 4th August 2009
' Delete cases from the input file of a dms script.
' ****************************************

InputDatasource(Input, "")
    ConnectionString = "Provider=mrOleDB.Provider.2; _
        Data Source=mrDataFileDsc; _
        Location=C:\temp\short_drinks.ddf; _
        MR Init MDM Access=1; _
        Initial Catalog=C:\temp\short_drinks.mdd"
    SelectQuery = "SELECT * FROM VDATA"
End InputDatasource

OutputDatasource(Output, "")
    UseInputAsOutput = "True"
End OutputDatasource

Logging(MyLog, "")
    Group = "DMA"
    Alias = "datamgmt"
    FileSize = 500
    Path = "c:\temp"
End Logging

Event(OnNextCase, "")

    If Respondent.Serial > 10 Then
       dmgrJob.DropCurrentCase()
       dmgrLog.LogError_2(Ctext(Respondent.Serial) + " - Case Deleted")
    End If

End Event

JustCode : Update a sample record

This code shows you how to update a sample record from an MRS script.

Dim oConnection

' Start code to check if the field has data in it.
    Set oConnection = CreateObject("ADODB.Connection")

' Standard Connection String You need to change this
    oConnection.ConnectionString = "Provider=SQLOLEDB.1;Password=MyPassword;Persist Security Info=-1;User ID=MyUser;Initial Catalog=Participants;Data Source=MyServer"
    oConnection.Open()

    If ( oConnection.State = 1 ) Then
        oConnection.Execute("UPDATE TABLE1 SET Queue = 'Fresh' WHERE Queue = 'Completed'")
    End If

    oConnection.Close()

Set oConnection = Null

JustCode : Strip out html from metadata context

This code will show you how to strip out html or unwanted characters from a specified context in your mdd file Via a DMS script.

Event(OnBeforeJobStart, "")

    Dim oMDM
    ' Create the MDM object and open the Short Drinks .mdd file in read-write mode
    Set oMDM = CreateObject("MDM.Document")
        oMDM.Open("C:\survey.mdd", ,2)
        oMDM.Contexts.Current = "Question"

    Dim oVar,oElement
    'Using the StripHTML funciton
    
    For Each oVar in oMDM.Variables
        if oVar.IsSystem = False then
            oVar.Label = StripHTML ( oVar.Label )
            For each oElement in oVar.Elements.Elements
                oElement.Label = StripHTML ( oElement.Label )
            Next
        end if
    Next

    'Using the StripOut function
    For Each oVar in oMDM.Variables
        if oVar.IsSystem = False then
            oVar.Label = StripOut ( oVar.Label )
            For each oElement in oVar.Elements.Elements
                oElement.Label = StripOut ( oElement.Label )
            Next
        end if
    Next

    oMDM.Save("C:\survey.mdd")
    Set oMDM = null

    Function StripHTML ( sText )
        Dim objRegExp, strOutput
        Set objRegExp = CreateObject("VBScript.RegExp")
            objRegExp.IgnoreCase = True
            objRegExp.Global = True
            objRegExp.Pattern = "< (.|n)+?>"

            'Replace all HTML tag matches with the empty string
            strOutput = objRegExp.Replace(sText, "")

            'Replace all < and > with < and >
            strOutput = Replace(strOutput, "< ", "<")
            strOutput = Replace(strOutput, ">", ">")

            StripHTML = strOutput    'Return the value of strOutput

        Set objRegExp = null
    End Function

    Function StripOut ( sText )
    ' You can use this function to hard code specific things you want to clean out the text
    Dim sAnswer
        sAnswer = sText
        ' Just repeat this line with the items you want to remove
        sAnswer = Replace(sAnswer,"[WHAT_ARE_YOU_LOOKING FOR]","")
        StripOut = sAnswer

    End Function

End Event

InputDatasource(Input, "")
    ConnectionString = "Provider=mrOleDB.Provider.2;Data Source=mrDataFileDsc;Location="C:\survey.ddf;Initial Catalog="C:\survey.mdd"
    SelectQuery = "SELECT * FROM VDATA"
End InputDatasource

OutputDatasource(Output, "")
    ConnectionString = "Provider=mrOleDB.Provider.2;Data Source=mrSavDsc;Location="C:\survey_out.sav"
    MetaDataOutputName = "C:\survey_OUT.mdd"
End OutputDatasource

JustCode : Strip out new lines from text questions

This code shows us how to strip out new lines from the text questions in our survey via a DMS script.

 
' ****************************************
' Designed by : Smarter Dimensions
' Last Updated : 30th July 2009
' Delete new lines from text questions
' ****************************************

Event(OnNextCase, "Next")
Dim iQuestionCount
    For iQuestionCount = 0 To dmgrJob.Questions.Count - 1
        If dmgrJob.Questions[iQuestionCount].response.DataType = 2 then
            dmgrJob.Questions[iQuestionCount].response.Value = dmgrJob.Questions[iQuestionCount].Replace(mr.Cr," ")
            dmgrJob.Questions[iQuestionCount].response.Value = dmgrJob.Questions[iQuestionCount].Replace(mr.lf," ")
        End If
    Next
End Event

JustCode : Delete cases from the output file of a dms script

This script shows you how to delete a case in the OnNextCase Event in the output data file.

' ****************************************
' Designed by : Smarter Dimensions
' Last Updated : 4th August 2009
' Delete cases from the output file of a dms script.
' ****************************************
InputDatasource(Input, "")
    ConnectionString = "Provider=mrOleDB.Provider.2; _
        Data Source=mrDataFileDsc; _
        Location=C:tempshort_drinks.ddf; _
        Initial Catalog=C:\temp\short_drinks.mdd"
    SelectQuery = "SELECT * FROM VDATA"
End InputDatasource

OutputDatasource(Output, "")
    ConnectionString = "Provider=mrOleDB.Provider.2; _
        Data Source=mrSAVDsc; _
        Location=C:\temp\Output.sav; _
        MR Init MDM DataSource Use=2; _
        MR Init Overwrite=1"
       MetaDataOutputName = "C:\temp\Output.MDD"
End OutputDatasource

Logging(MyLog, "")
    Group = "DMA"
    Alias = "datamgmt"
    FileSize = 500
    Path = "c:\temp"
End Logging

Event(OnNextCase, "")

    If Respondent.Serial > 10 Then
        dmgrJob.DropCurrentCase()
        dmgrLog.LogError_2(Ctext(Respondent.Serial) + " - Case Deleted")
    End If

End Event

JustCode : Delete Empty columns of data

This mrs script shows you how you can use mrs files to make and run a dms file that will strip out any columns that do not have any data in.

Step 1: Open the VQ file and make an MDD FILE.

Dim mrDSCs, mrMdsc, oDoc,sVQ

sVQ = "C:\temp\FILE"
    Set mrMdsc = createobject("mrdscreg.components")
    Set mrMDSC = mrMdsc["mrScDsc"]

    Set oDoc = mrMdsc.Metadata.Open(sVQ + ".vq")
        oDoc.Save(sVQ + ".mdd")
        oDoc.Close()

Set oDoc = Null

Step 2: Take the MDD file and loop the variable instances


Dim oMDM, oVar
Dim sSQL,oConnection,oRecordset

' Create the MDM object
Set oMDM = CreateObject("MDM.Document")
    oMDM.Open("C:\temp\FILE.mdd", , 1)

    For Each oVar in oMDM.Variables
        'if oVar.IsSystem = False then
            debug.Log("Question Name : " + oVar.FullName)

            ' Start code to check if the field has data in it.
            Set oConnection = CreateObject("ADODB.Connection")
                oConnection.ConnectionString = "Provider=mrOleDB.Provider.2;Data Source=mrScDSC;Location=C:\temp\FILE.VQ;Initial Catalog=C:\temp\FILE.mdd"
                oConnection.Open()

            If ( oConnection.State = 1 ) Then
                Set oRecordset = CreateObject("ADODB.Recordset")
                    oRecordset.Open("SELECT count(respondent.serial) FROM VDATA WHERE " + oVar.FullName + " is not null" ,oConnection,3,1)
                    If ( oRecordset.EOF = true and oRecordset.BOF = true ) Then
                        sSQL = sSQL
                    ELSE
                        sSQL = sSQL + oVar.FullName + ","
                    End if
                Set oRecordset = Null

            End If

            oConnection.Close()
            Set oConnection = Null
    ' end if
Next

oMDM.Close()
sSQL = Left(sSQL,len(sSQL)-1)
debug.Log(sSQL)

Step 3: Create the dms file with the select statement in it.


Dim oFSO, oFile

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set oFile = oFSO.CreateTextFile("C:\temp\FILE.dms", True)


oFile.WriteLine("InputDatasource(Input, """")")
oFile.WriteLine("    ConnectionString = ""Provider=mrOleDB.Provider.2;Data Source=mrScDSC;Location=C:\temp\FILE.vq;Initial Catalog=C:\temp\FILE.mdd"" ")
oFile.WriteLine("    SelectQuery = ""SELECT " + sSQL + " FROM VDATA""")
oFile.WriteLine("End InputDatasource")
oFile.WriteLine("")
oFile.WriteLine("OutputDatasource(Output, """")")
oFile.WriteLine("    ConnectionString = ""Provider=mrOleDB.Provider.2;Data Source=mrSavDsc;Location=C:\temp\Final.sav""")
oFile.WriteLine("    MetaDataOutputName = ""C:\temp\Final.mdd""")
oFile.WriteLine("End OutputDatasource")

oFile.Close()

Set oFSO = Null

Step 4: Run the file


Dim oDMOMJob

Set oDMOMJob = CreateObject("DMOM.Job")

oDMOMJob.Load("C:\temp\FILE.dms", null)

oDMOMJob.Run()

JustCode : Function to return the current connection string

This code shows us how to open up an MDD and read the conenction string properties to get the current connection string settings.

 
' ****************************************
' Designed by : Smarter Dimensions
' Last Updated : 5th July 2009
' Function to return the current connection string of an MDD
' ****************************************

Dim sConnection, oMDM

Set oMDM = CreateObject("MDM.Document")

oMDM.Open("c:\temp\short_drinks.mdd", , openConstants.oREAD)

sConnection = GetConnectionStringFromMDM(oMDM)

debug.Log(sConnection)

oMDM.close()

Function GetConnectionStringFromMDM(oDoc)
Dim oDataLinkHelper, sConnectionString

Set oDataLinkHelper = CreateObject("MROLEDB.DataLinkHelper")
sConnectionString = "Provider=mrOleDB.Provider.2;"
sConnectionString = sConnectionString + "Data Source=" + oDoc.DataSources.Current.cDscName + ";"
sConnectionString = sConnectionString + "Location=""" + oDoc.DataSources.Current.DbLocation + """;"
sConnectionString = sConnectionString + "MR Init MDM Document=" + oDataLinkHelper.CreateDocumentObjectString(oDoc) + ";"
sConnectionString = sConnectionString + "MR Init Project=" + oDoc.DataSources.Current.Project + ";"
GetConnectionStringFromMDM = sConnectionString

End Function

Creating a TOM table within an interview

Recently the following question was asked in LinkedIn :

KG: Some years ago I saw an interview script that produced a TOM table and presented the information to the respondent. You might use this to show poll results when a respondent has answered a question. For the life of me I can not find that script. Does anyone have an example of this that I can use?

and it got the following response from

CB : I haven’t found the example, but here’s code to connect to a database and pull in the results. You’d need to adjust the code inside the If but I’m sure you could take that result and display it:

Dim rsquery, sSQL, oConnection, oRecordSet
Set oConnection = CreateObject("Adodb.connection")
Set oRecordset = CreateObject("ADODB.Recordset")

    oConnection.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=MyDatabase;Data Source=MyServer"
    oConnection.Open()
    sSQL = "Select * FROM [MyDatabase].[dbo].[MyTable]"

    Set rsQuery=oConnection.Execute(sSQL)
    If rsQuery.BOF=False Then'Found record(s)
        sampBusunit =cText(rsQuery.Fields["Busunit"])
    End if
oConnection.Close()

This is a great reply as it shows one way , not using TOM , but a way that will work. If however you realy need to use TOM then you can do something like this once your table is created. To see how to create the table take a look at this post , but once you have your table object you can use the HTMLRenderer to do the rest for you.

Dim HtmlRenderer,sTomHTML


Set HtmlRenderer = CreateObject("ExportHtml.Renderer")
    HtmlRenderer.DisplayAnnotations = False
    HtmlRenderer.DisplayOption = 0              ' 0 = Table Only
    HtmlRenderer.EmbedCss = true
			
    HtmlRenderer.UseFormattedLabels = True
    HtmlRenderer.IncludeTitleBlock = False
    HtmlRenderer.IncludeTOC = False
    HtmlRenderer.OutputBodyOnly = True
			
    sTomHTML = replace(HtmlRenderer.Render(oTableDoc.GetTablesXml(sTable)),mr.newline,"")
		
    Set HtmlRenderer = Null

I do this sort of stuff all the time and it works well , I normally take the output data and run it through the Highcharts api. to produce charts etc. Oh and don’t forget this will only work in your survey if you have TOM on the server. To get an idea of what I do , take a look at this.

JustCode : Make an MDD file from a VQ file using DSC

This code shows you how to open an surveycraft metatdata file using the Surveycraft dsc and save it as a MDD file.

 
' ****************************************
' Designed by : Smarter Dimensions
' Last Updated : 7th July 2009
' Code to make an MDD file from a VQ file using the SC dsc
' ****************************************

Dim mrDSCs, mrMdsc, oDoc,sVQ

sVQ = "C:\temp\MUSEUM"

Set mrMdsc = createobject("mrdscreg.components")
Set mrMDSC = mrMdsc["mrScDsc"]

Set oDoc = mrMdsc.Metadata.Open(sVQ + ".vq")

oDoc.Save(sVQ + ".mdd")

oDoc.Close()

Set oDoc = Null
%d bloggers like this: