• Replies 11 replies
  • Subscribers 83 subscribers
  • Views 198 views
  • Users 0 members are here
Options
Related

WordFly LP update preferences - anyone customized that successfully yet?

Mindee Waltz (Past Member)
$organization

Shortest possible version of my problem: with control-grouped email addresses (which we need), the WordFly procedure *as delivered* does not actually look for an eaddress_no that belongs to the appropriate group - or even the ACTUAL eaddress_no from the Eaddress table - it just selects the first row with the same email string (address) that the message was sent to...which means that currently, I have purposes that belong to one organization being written to another organization's email types.  PLEASE HELP. WILL SEND COOKIES. 

Parents
  • $organization

    We do have this procedure here in the Perth Consortium and I have attached step 2 of the procedure's contact point purpose section with some modifications. Thanks and hopefully this helps in some way!

    Fullscreen LP_WF_PREFERENCE_UPDATE_CUSTOMER_PART.sql Download 
    	--  STEP 2:  FIND EADDRESS RECORD TO MODIFY
		declare @eaddress_no int, @org_id Int, @org_desc VarChar(30), @userid Char(8), @usergroupid Char(8), @control_group Int
		
		-- Get organisation I.D.
		Select		@org_id = organization_id
		From		LT_WF_PREFERENCE_HISTORY With (NoLock)
		Where		Id = @preferenceHistoryId
		
		-- Get organisation name
		Select		@org_desc = [description]
		From		TR_ORGANIZATION With (NoLock)
		Where		id = @org_id

		-- Get organisation TMS / WF user name
		Select		@userid = default_value
		From		T_DEFAULTS With (NoLock)
		Where		field_name = 'TMS User' And parent_table = @org_desc

		-- Get organisation TMS / WF user name's default user group
		Select		@usergroupid = UG_id
		From		TX_USER_GROUP With (NoLock)
		Where		userid = @userid And default_ind = 'y'

		-- Get organisation TMS / WF user name's default user group's default control group
		Select		@control_group = control_group
		From		TX_CONTROL_GROUP_USER_GROUP With (NoLock)
		Where		ug_id = @usergroupid And default_ind = 'y'

		-- Try for potential control grouped eaddress_no
		Set			@eaddress_no =
		(Select		Top 1 eaddress_no 
		From		T_EADDRESS a With (NoLock)
					Left Join TR_EADDRESS_TYPE b With (NoLock) On b.id = a.eaddress_type
		Where		a.customer_no = @customer_no And a.[address] = @messaging_address And a.inactive = 'n' And
					b.control_group = @control_group
		Order By	a.create_dt Desc, a.last_update_dt Desc)		

		-- If not found, try for default uncontrol grouped eaddress_no
		If			IsNull(@eaddress_no, 0) = 0
		Begin
			Set			@eaddress_no =
			(Select		Top 1 eaddress_no 
			From		T_EADDRESS a With (NoLock)
						Left Join TR_EADDRESS_TYPE b With (NoLock) On b.id = a.eaddress_type
			Where		a.customer_no = @customer_no And a.[address] = @messaging_address And a.inactive = 'n' And
						b.control_group = -1
			Order By	a.create_dt Desc, a.last_update_dt Desc)		
		End
		
		/*
		select @eaddress_no = eaddress_no from dbo.[t_eaddress]
		where [customer_no] = @customer_no and [address] = @messaging_address and [inactive] = 'N'
		order by create_dt desc, last_update_dt desc
		*/

		if ISNULL(@eaddress_no,0) = 0
				begin		
					raiserror('Invalid email address for this customer.', 11, 2) with seterror
					return -101		
				end

Reply
  • $organization

    We do have this procedure here in the Perth Consortium and I have attached step 2 of the procedure's contact point purpose section with some modifications. Thanks and hopefully this helps in some way!

    Fullscreen LP_WF_PREFERENCE_UPDATE_CUSTOMER_PART.sql Download 
    	--  STEP 2:  FIND EADDRESS RECORD TO MODIFY
		declare @eaddress_no int, @org_id Int, @org_desc VarChar(30), @userid Char(8), @usergroupid Char(8), @control_group Int
		
		-- Get organisation I.D.
		Select		@org_id = organization_id
		From		LT_WF_PREFERENCE_HISTORY With (NoLock)
		Where		Id = @preferenceHistoryId
		
		-- Get organisation name
		Select		@org_desc = [description]
		From		TR_ORGANIZATION With (NoLock)
		Where		id = @org_id

		-- Get organisation TMS / WF user name
		Select		@userid = default_value
		From		T_DEFAULTS With (NoLock)
		Where		field_name = 'TMS User' And parent_table = @org_desc

		-- Get organisation TMS / WF user name's default user group
		Select		@usergroupid = UG_id
		From		TX_USER_GROUP With (NoLock)
		Where		userid = @userid And default_ind = 'y'

		-- Get organisation TMS / WF user name's default user group's default control group
		Select		@control_group = control_group
		From		TX_CONTROL_GROUP_USER_GROUP With (NoLock)
		Where		ug_id = @usergroupid And default_ind = 'y'

		-- Try for potential control grouped eaddress_no
		Set			@eaddress_no =
		(Select		Top 1 eaddress_no 
		From		T_EADDRESS a With (NoLock)
					Left Join TR_EADDRESS_TYPE b With (NoLock) On b.id = a.eaddress_type
		Where		a.customer_no = @customer_no And a.[address] = @messaging_address And a.inactive = 'n' And
					b.control_group = @control_group
		Order By	a.create_dt Desc, a.last_update_dt Desc)		

		-- If not found, try for default uncontrol grouped eaddress_no
		If			IsNull(@eaddress_no, 0) = 0
		Begin
			Set			@eaddress_no =
			(Select		Top 1 eaddress_no 
			From		T_EADDRESS a With (NoLock)
						Left Join TR_EADDRESS_TYPE b With (NoLock) On b.id = a.eaddress_type
			Where		a.customer_no = @customer_no And a.[address] = @messaging_address And a.inactive = 'n' And
						b.control_group = -1
			Order By	a.create_dt Desc, a.last_update_dt Desc)		
		End
		
		/*
		select @eaddress_no = eaddress_no from dbo.[t_eaddress]
		where [customer_no] = @customer_no and [address] = @messaging_address and [inactive] = 'N'
		order by create_dt desc, last_update_dt desc
		*/

		if ISNULL(@eaddress_no,0) = 0
				begin		
					raiserror('Invalid email address for this customer.', 11, 2) with seterror
					return -101		
				end

Children