Advanced EmailManagement

Is it possible to get an up to date (CRM 7.3) definition for the query objects passed into a template? eg Jeff says in his blog community.sagecrm.com/.../sage-crm-advanced-e-mail-management-creating-a-template.aspx that: PersonObject SQL =

"SELECT * FROM vEmail, vPerson WHERE email_personid = pers_personid AND emai_emailaddress = FromAddress"

but vEmail no longer exists. I'm trying to determine which columns are included; Pers_Salutation seems to be absent, for instance, which is annoying. So, I found in the 7.3 System Administrator Guide (p. 35-15) it says the PersonQuery object "runs the following script":

Select * from vPersonEmail where elink_recordid = pers_Personid and emai_emailaddress = FromAddress

However, vPersonEmail doesn't include any columns prefixed with pers_ and the PersonQuery object passed into a template definitely contains pers_firstname and pers_lastname. Is the documentation just wrong? Is there an updated manual? How do I find out what fields are in the UserObject, CompanyObject and PersonObject objects - without having to resort to trial and error detective work?

Parents
  • 0

    For anyone else working with the eMailManager, the CompanyQuery, PersonQuery and UserQuery objects contain the following fields (CRM 7.3):

    CompanyQuery PersonQuery UserQuery
    Emai_EmailAddress Emai_EmailAddress User_FullName
    Pers_FirstName Pers_FirstName User_UserId
    Pers_LastName Pers_LastName User_PrimaryChannelId
    Pers_PersonId Pers_PersonId User_Logon
    Pers_PrimaryUserId Pers_PrimaryUserId User_LastName
    Pers_SecTerr Pers_SecTerr User_FirstName
    Pers_CreatedBy Pers_CreatedBy User_Language
    Pers_ChannelId Pers_ChannelId User_Department
    Pers_Deleted Pers_Deleted User_Phone
    Pers_CompanyId Pers_CompanyId User_Extension
    Pers_AccountId Pers_AccountId User_Pager
    Pers_LibraryDir Pers_LibraryDir User_Homephone
    Comp_CompanyId Comp_CompanyId User_MobilePhone
    Comp_Name Comp_Name User_Fax
    Comp_PrimaryUserId Comp_PrimaryUserId User_EmailAddress
    Comp_SecTerr Comp_SecTerr User_LastLogin
    Comp_CreatedBy Comp_CreatedBy User_User1
    Comp_ChannelId Comp_ChannelId User_Password
    Comp_LibraryDir Comp_LibraryDir User_CreatedBy
    Acc_AccountId Acc_AccountId User_CreatedDate
    Acc_Name Acc_Name User_UpdatedBy
    Acc_PrimaryUserId Acc_PrimaryUserId User_UpdatedDate
    Acc_SecTerr Acc_SecTerr User_TimeStamp
    Acc_CreatedBy Acc_CreatedBy User_Deleted
    Acc_ChannelId Acc_ChannelId User_Per_ContactInsert
    Acc_LibraryDir Acc_LibraryDir User_Per_ContactUpdate
    User_Per_Communication
    User_Per_Opportunity
    User_Per_Case
    User_Per_ToDo
    User_Per_Channels
    User_Per_Reports
    User_Per_Admin
    User_Per_Team
    User_Per_TeamUpdate
    User_Per_TeamSensitive
    User_ProfileEnabled
    User_Profile
    User_ProfileLastSyncDate
    User_LastEntitySyncOutcome
    User_RecentList
    User_MustChangePassword
    User_CannotChangePassword
    User_PasswordUpdateDate
    User_PasswordNeverExpires
    User_SMSNotification
    User_CTIDeviceName
    User_Disabled
    User_Resource
    User_Per_CompAssign
    User_Per_EntityMerge
    User_SecurityProfile
    User_ExternalLogonAllowed
    User_PrimaryTerritory
    User_IsTerritoryManager
    User_TerritoryProfile
    User_Per_User
    User_Per_Product
    User_Per_Currency
    User_Per_Data
    User_OfflineAccessAllowed
    user_MobileEmail
    user_per_tree
    user_rollupto
    user_forecastcurrency
    user_per_customise
    User_MinMemory
    User_MaxMemory
    User_ForecastSecurity
    user_title
    user_location
    user_deskid
    User_Per_InfoAdmin
    User_Device_MachineName
    User_Per_Solutions
    User_IsTemplate
    User_TemplateName
    User_WebServiceEnabled
    user_MasterPersID
    User_AccountLockedOut
    User_CTIEnabled
    User_CTICallScreen
    user_GroupAccess
    User_LastServer
    user_EnableDoNotReprice
    User_Logons
    User_ShowSurvey
    User_LastSurveyTaken
    user_licencetype
    User_TerritoriesFK
    User_DefaultLandingPage
    User_ExchangeSync
    User_ShowLogs
    User_EmarketingUserID
    User_ExConflictCount
    User_ExErrorCount
    User_ExSkippedItemsCount
    User_ExLastSyncTime
    user_Per_License
    user_MailMergeToWord
    user_EnableMailChimp
    user_dvCustAnalysisCodes
    user_dvCust
    user_dvCustMemos
    user_dvCustPeriodTurnover
    user_dvCustProjects
    user_dvCustSalesOrdersLines
    user_dvProductsPurchased
    user_dvCustTranMemos
    user_dvCustTransactions
    user_dvCustYearTurnover
    user_dvInvoicesForOrderLines
    user_dvInvsForPOPOrderLines
    user_dvPOPReceiptLines
    user_dvPurchaseOrdersLines
    user_dvPurchaseOrders
    user_dvSalesOrdersLines
Reply
  • 0

    For anyone else working with the eMailManager, the CompanyQuery, PersonQuery and UserQuery objects contain the following fields (CRM 7.3):

    CompanyQuery PersonQuery UserQuery
    Emai_EmailAddress Emai_EmailAddress User_FullName
    Pers_FirstName Pers_FirstName User_UserId
    Pers_LastName Pers_LastName User_PrimaryChannelId
    Pers_PersonId Pers_PersonId User_Logon
    Pers_PrimaryUserId Pers_PrimaryUserId User_LastName
    Pers_SecTerr Pers_SecTerr User_FirstName
    Pers_CreatedBy Pers_CreatedBy User_Language
    Pers_ChannelId Pers_ChannelId User_Department
    Pers_Deleted Pers_Deleted User_Phone
    Pers_CompanyId Pers_CompanyId User_Extension
    Pers_AccountId Pers_AccountId User_Pager
    Pers_LibraryDir Pers_LibraryDir User_Homephone
    Comp_CompanyId Comp_CompanyId User_MobilePhone
    Comp_Name Comp_Name User_Fax
    Comp_PrimaryUserId Comp_PrimaryUserId User_EmailAddress
    Comp_SecTerr Comp_SecTerr User_LastLogin
    Comp_CreatedBy Comp_CreatedBy User_User1
    Comp_ChannelId Comp_ChannelId User_Password
    Comp_LibraryDir Comp_LibraryDir User_CreatedBy
    Acc_AccountId Acc_AccountId User_CreatedDate
    Acc_Name Acc_Name User_UpdatedBy
    Acc_PrimaryUserId Acc_PrimaryUserId User_UpdatedDate
    Acc_SecTerr Acc_SecTerr User_TimeStamp
    Acc_CreatedBy Acc_CreatedBy User_Deleted
    Acc_ChannelId Acc_ChannelId User_Per_ContactInsert
    Acc_LibraryDir Acc_LibraryDir User_Per_ContactUpdate
    User_Per_Communication
    User_Per_Opportunity
    User_Per_Case
    User_Per_ToDo
    User_Per_Channels
    User_Per_Reports
    User_Per_Admin
    User_Per_Team
    User_Per_TeamUpdate
    User_Per_TeamSensitive
    User_ProfileEnabled
    User_Profile
    User_ProfileLastSyncDate
    User_LastEntitySyncOutcome
    User_RecentList
    User_MustChangePassword
    User_CannotChangePassword
    User_PasswordUpdateDate
    User_PasswordNeverExpires
    User_SMSNotification
    User_CTIDeviceName
    User_Disabled
    User_Resource
    User_Per_CompAssign
    User_Per_EntityMerge
    User_SecurityProfile
    User_ExternalLogonAllowed
    User_PrimaryTerritory
    User_IsTerritoryManager
    User_TerritoryProfile
    User_Per_User
    User_Per_Product
    User_Per_Currency
    User_Per_Data
    User_OfflineAccessAllowed
    user_MobileEmail
    user_per_tree
    user_rollupto
    user_forecastcurrency
    user_per_customise
    User_MinMemory
    User_MaxMemory
    User_ForecastSecurity
    user_title
    user_location
    user_deskid
    User_Per_InfoAdmin
    User_Device_MachineName
    User_Per_Solutions
    User_IsTemplate
    User_TemplateName
    User_WebServiceEnabled
    user_MasterPersID
    User_AccountLockedOut
    User_CTIEnabled
    User_CTICallScreen
    user_GroupAccess
    User_LastServer
    user_EnableDoNotReprice
    User_Logons
    User_ShowSurvey
    User_LastSurveyTaken
    user_licencetype
    User_TerritoriesFK
    User_DefaultLandingPage
    User_ExchangeSync
    User_ShowLogs
    User_EmarketingUserID
    User_ExConflictCount
    User_ExErrorCount
    User_ExSkippedItemsCount
    User_ExLastSyncTime
    user_Per_License
    user_MailMergeToWord
    user_EnableMailChimp
    user_dvCustAnalysisCodes
    user_dvCust
    user_dvCustMemos
    user_dvCustPeriodTurnover
    user_dvCustProjects
    user_dvCustSalesOrdersLines
    user_dvProductsPurchased
    user_dvCustTranMemos
    user_dvCustTransactions
    user_dvCustYearTurnover
    user_dvInvoicesForOrderLines
    user_dvInvsForPOPOrderLines
    user_dvPOPReceiptLines
    user_dvPurchaseOrdersLines
    user_dvPurchaseOrders
    user_dvSalesOrdersLines
Children
No Data