Re: Adding default addressees/salutations

Shane Dalgleish — Thu, 12 Mar 2009 07:05:18 GMT

I threw together this bit of code to do the job we needed, it was written in VB.Net so needs a little translation if you want it in VBA, let me know if you need a hand with that.

This default set loader assumes that your default sets have nothing in them that will break RE, for example, if you only have a date in a constituent code and leave the code blank. It certainly isn't to be considered stable at this moment, but it may point you in the right direction.

So far it can handle Salutations, Constituent Codes, Attributes, Addresses and Appeals.

Hope you find it useful

    '===================================================================================================
    ' Load Defaults From....
    '===================================================================================================
    '
    ' Author:       Shane Dalgleish (The Wilderness Society Inc.)
    ' Date Modified:   12/03/2009
    ' Request Tracker:   #25398: Online Automation
    ' SVN Version:   --Not Yet Committed--
    ' Purpose:       Load a default set to a constituent record
    ' Reviewed By:   Kev Rothery
    ' Review Date:   12/03/2009
    '

        If strDefaultSet <> "" Then

            Dim intResuts As Integer = 0
            Dim strCommandType As String

            ' RE7 SQL Declarations
            Dim dbREConnect As New SqlConnection()
            Dim dbRECommand As New SqlCommand()
            Dim dbRERead As SqlDataReader

            Dim dbREConnect2 As New SqlConnection()
            Dim dbRECommand2 As New SqlCommand()
            Dim dbRERead2 As SqlDataReader

            Dim strDataSource As String = "Data Source=[server name];Initial Catalog=[Database Name];Integrated Security=True"
            ' another connection for later on
            dbREConnect2.ConnectionString = strDataSource
            dbREConnect2.Open()
            dbRECommand2.Connection = dbREConnect2

            ' Create array of valid RE Column Names
            dbREConnect.ConnectionString = strDataSource
            dbREConnect.Open()
            dbRECommand.Connection = dbREConnect
            dbRECommand.CommandText = "Select b.FieldDefaultSetsID, b.MetaObjectID, b.Sequence, count(*) as count " & _
                                " from fielddefaultsets a, fielddefaults b" & _
                                " where b.FIELDDEFAULTSETSID = a.FIELDDEFAULTSETSID" & _
                                " and a.name = '" & Trim(strDefaultSet) & "'" & _
                                " group by b.FieldDefaultSetsID, b.MetaObjectID, b.Sequence " & _
                                " order by metaObjectID, b.Sequence;"
            dbRERead = dbRECommand.ExecuteReader()

            While dbRERead.Read()
                ' Work out the data type
                Select Case dbRERead("MetaObjectID")
                    Case 713
                        strCommandType = "Constituent"
                    Case 735
                        strCommandType = "ConstituentCodes"
                    Case 714
                        strCommandType = "Attribute"
                    Case 715
                        strCommandType = "Address"
                    Case 736
                        strCommandType = "Appeals"
                    Case Else
                        GoTo jumpONE
                End Select

                ' Select the Data
                dbRECommand2.CommandText = "Select b.MetaObjectID, b.FieldNumber, b.DefaultValue, c.longdescription, c.shortdescription, d.description, e.appeal_id" & _
                                            " from fielddefaults b left outer join tableentries c " & _
                                            " on b.defaultValue = convert(varchar(5),c.Tableentriesid) " & _
                                            " left outer join " & _
                                            " attributeTypes d on b.defaultValue = convert(varchar(5),d.attributetypesid)" & _
                                            " left outer join appeal e on b.defaultvalue = convert(varchar(5),e.id) " & _
                                            "Where " & _
                                            " FieldDefaultSetsID = " & dbRERead("FieldDefaultSetsID") & _
                                            " and MetaObjectID = " & dbRERead("MetaObjectID") & _
                                            " and b.Sequence = " & dbRERead("Sequence") & ";"
                dbRERead2 = dbRECommand2.ExecuteReader()

                Select Case strCommandType
                    Case "Constituent"
                        With objConstituent
                            While dbRERead2.Read
                                ' We're only worrying about Salutations here...
                                Select Case dbRERead2("FieldNumber")
                                    Case 40
                                        ' Enum 40 & 43 are the description fields, subtracting 1 gives us the data field
                                        .Fields(39) = dbRERead2("DefaultValue")
                                    Case 43
                                        .Fields(42) = dbRERead2("DefaultValue")
                                End Select
                            End While
                        End With
                        dbRERead2.Close()

                    Case "Attribute"
                        With objConstituent.Attributes.Add
                            While dbRERead2.Read
                                Select Case dbRERead2("FieldNumber")
                                    Case 2
                                        .Fields(2) = Trim(dbRERead2("description"))
                                    Case 3
                                        If dbRERead2("DefaultValue") = "-1" Then
                                            .Fields(3) = True
                                        ElseIf dbRERead2("DefaultValue") = "0" Then
                                            .Fields(3) = False
                                        Else
                                            .Fields(3) = Trim(dbRERead2("longdescription"))
                                        End If
                                    Case 6
                                        If dbRERead2("DefaultValue") = "Current system date" Then
                                            .Fields(6) = Format(Now, "dd/MM/yyyy")
                                        Else
                                            .Fields(6) = dbRERead2("DefaultValue")
                                        End If
                                End Select
                            End While
                        End With
                        dbRERead2.Close()
                        objConstituent.Save()

                    Case "ConstituentCodes"
                        With objConstituent.ConstituentCodes.Add
                            While dbRERead2.Read
                                Select Case dbRERead2("FieldNumber")
                                    Case 1
                                        .Fields(1) = dbRERead2("shortdescription")
                                    Case 3
                                        If dbRERead2("DefaultValue") = "Current system date" Then
                                            .Fields(3) = Format(Now, "dd/MM/yyyy")
                                        Else
                                            .Fields(3) = dbRERead2("DefaultValue")
                                        End If
                                End Select
                            End While
                        End With
                        dbRERead2.Close()

                    Case "Address"
                        With objConstituent.PreferredAddress
                            While dbRERead2.Read
                                Select Case dbRERead2("FieldNumber")
                                    Case 14
                                        .Fields(14) = dbRERead2("longdescription")
                                    Case 3
                                        If dbRERead2("DefaultValue") = "Current system date" Then
                                            .Fields(3) = Format(Now, "dd/MM/yyyy")
                                        Else
                                            .Fields(3) = dbRERead2("DefaultValue")
                                        End If
                                    Case 25
                                        .Fields(25) = dbRERead2("shortdescription")
                                End Select
                            End While
                        End With
                        dbRERead2.Close()

                    Case "Appeals"
                        With objConstituent.Appeals.Add
                            While dbRERead2.Read
                                Select Case dbRERead2("FieldNumber")
                                    Case 1
                                        .Fields(1) = dbRERead2("appeal_id")
                                    Case 2
                                        .Fields(2) = dbRERead2("DefaultValue")
                                    Case 4
                                        If dbRERead2("DefaultValue") = "Current system date" Then
                                            .Fields(4) = Format(Now, "dd/MM/yyyy")
                                        Else
                                            .Fields(4) = dbRERead2("DefaultValue")
                                        End If
                                    Case 8
                                        .Fields(8) = dbRERead2("longdescription")
                                End Select
                            End While
                        End With
                End Select
jumpONE:
            End While
        End If

Re: Adding default addressees/salutations

David Zeidman — Tue, 10 Feb 2009 08:49:39 GMT

Unfortunately default sets are not part of the standard API

David

Re: Adding default addressees/salutations

Shane Dalgleish — Tue, 10 Feb 2009 08:16:09 GMT

I'm also looking to load a default set from the API because I'm wanting to automatically create constituents from incoming contacts.

Shane

Shane Dalgleish
The Wilderness Society Inc. (Australia)

Adding default addressees/salutations

Alan Leiter — Fri, 19 Dec 2008 20:48:00 GMT

Hi everyone. We have set up defaults on the Constituent | Load defaults from... menu to load a default set of addressee and salutation formats to a constituent. Is there a way to access that same default set in the RE API so we can add the same default set through code?

Thanks!

Alan Leiter
Assistant Director of Advancement Services
Azusa Pacific University

VBA/API

Re: Adding default addressees/salutations

Re: Adding default addressees/salutations

Re: Adding default addressees/salutations

Adding default addressees/salutations