First name blank in lists when there is a household

We work around this within the view for WF, but we're only pulling in fname for now... something like this should get you started. We then have custom output set elements referencing this View. 

Code Credit: 

ALTER VIEW [dbo].[LV_2ST_PRESHOW]
AS

SELECT DISTINCT
case
when (
isnull(ini.customer_no,0) > 0 --initiator exists
and isnull(ini.customer_no,0) <> a.customer_no --initiator is different from order owner
and e.cust_type not in (1,8) --order account is not HH/Indv
)
then ini.customer_no
else a.customer_no
end as 'customer_no',
case
when (
isnull(ini.customer_no,0) > 0 --initiator exists
and isnull(ini.customer_no,0) <> a.customer_no --initiator is different from order owner
and e.cust_type not in (1,8) --order account is not HH/Indv
)
then iniea.address
else c.address
end as 'eaddress',
v.description as perf_name,
DATENAME(dw, d.perf_dt) as perf_day,
--d.perf_dt as friendly_perf_dt, -- converting to "friendly" date in Query Elements
format (d.perf_dt, 'h:mm tt') as perf_time,
d.time_slot,
convert(varchar,perf_dt,101) as 'perf_dt', --day without perf time to match with list manager criteria
case
when (
isnull(ini.customer_no,0) > 0 --initiator exists
and isnull(ini.customer_no,0) <> a.customer_no --initiator is different from order owner
and e.cust_type not in (1,8) --order account is not HH/Indv
)
then (case when ini.fname is null then ininame.fname else ini.fname end) --if initiator exists and is a HH, use fname of prim aff of HH
else (case when e.fname is null then g.fname else e.fname end)
end as 'fname',
a.order_no,
d.prod_season_no
FROM dbo.T_ORDER a (nolock)
join T_SUB_LINEITEM b (nolock) on a.order_no = b.order_no
outer apply
(select top 1 address, customer_no
from T_EADDRESS ea (nolock)
where a.customer_no = ea.customer_no
and ea.primary_ind = 'y' and ea.inactive = 'n'
order by last_update_dt desc) as c --get the most recently updated active primary email address on the order owner account
join T_PERF d on d.perf_no = b.perf_no and d.perf_status = 1 --select * from TR_PERF_STATUS
join T_INVENTORY v on v.inv_no = d.perf_no
join dbo.TR_SEASON AS s ON d.season = s.id

--these tables are joined to get order initiator information / order initiator affiliate information (to pull a fname if the order was on a HH record)
left outer join T_CUSTOMER ini on ini.customer_no = a.initiator_no
outer apply
(select top 1 address, customer_no
from T_EADDRESS ead (nolock)
where ini.customer_no = ead.customer_no
and ead.primary_ind = 'y' and ead.inactive = 'n'
order by ead.last_update_dt) as iniea --get the most recently updated active primary email address on the initiator account
left outer join T_AFFILIATION iniaf on iniaf.group_customer_no = ini.customer_no and iniaf.name_ind = -1
left outer join T_CUSTOMER ininame on ininame.customer_no = iniaf.individual_customer_no

--these tables are joined to get order owner affiliate information (to pull a fname if the order was on a HH record)
join T_CUSTOMER e (nolock) on e.customer_no = a.customer_no
left outer join T_AFFILIATION f ON f.group_customer_no = e.customer_no and f.name_ind = -1
left outer join T_CUSTOMER g (nolock) ON g.customer_no = f.individual_customer_no

WHERE (
(isnull(ini.customer_no,0) > 0
and isnull(ini.customer_no,0) <> a.customer_no
and e.cust_type not in (1,8)
and exists (select top 1 1 from T_EADDRESS (nolock) where customer_no = ini.customer_no and primary_ind = 'Y' and inactive = 'N')
) --for cases where there is an initiator, initiator must have email on file

or exists (select top 1 1 from T_EADDRESS (nolock) where customer_no = a.customer_no and primary_ind = 'Y' and inactive = 'N') --otherwise, order owner
) --must have email
AND b.sli_status IN (3, 12) --select * from TR_SLI_STATUS where id in (3,12)

AND (d.perf_dt BETWEEN CONVERT(DateTime, DATEdiff(DAY, -1, getdate())) AND --beginning of tomorrow
DATEADD(SECOND, - 1, CONVERT(DateTime, DATEDIFF(DAY, -2, getdate())))) --end of tomorrow
AND d.perf_no not in (select inv_no from TX_INV_TKW where tkw in (19,20,21)) -- Devo, Dummy, and Champ Nights select * from TR_TKW where id in (19,20,21)

GO

Hi Corinne,

Have you tried pulling the lsal_desc field from FT_GET_PRIMARY_SALUTATION()?  It depends you you use the letter salutation over there.  I think that's the intent of lsal_desc (thought admittedly we have a few "Sir/Madam"s)

select lsal_desc from FT_GET_PRIMARY_SALUTATION()
where customer_no = 142568

Returns ...

Heath

This is great by the way.  Gleefully stolen

Corinne,

Without sharing another whole code set, we basically do the same thing as Emily.  It is just easier to write a custom VIEW or have custom output elements take care of things like that for you than to try anything else clever.

John

Hi Corinne,

As Heath mentioned, the salutations are the external-facing, "customer-friendly" version of names that are intended for emailings, envelopes, etc. The Salutation_outside_line1 field is commonly more formal and might read like "Mr. and Mrs. Brown" for a household and "Mrs. Brown" for an individual; as the name implies, it often goes on the outside of an envelope. The Salutation_inside field is commonly more personal and might read like "John and Sam" for a household or "Sam" for an individual; as the name implies, it goes inside the envelope or email, and we often call it a "dear" salutation, as in "Dear Sam." 

Exactly how these salutations are automatically generated is governed the by the settings in the system table TR_SALUTATION_FORMAT. If the salutations as they exist in your system don't meet your organization's needs for mailing purposes, this is where you could adjust the formatting. This also answers your second question; the "Household" is coming from the settings in the columns labeled "Group", but the "Household" value is generally left in there because it's used is various reports and screens inside the application. 

Best,

-Michael Flaherty-Wilcox, Tessitura Network 

This is most helpful, and I think that if we could consistently have our Salutation_inside be the first name this would be sufficient for now.

I did start pulling the Salutation_inside field which makes me think we need to reconfigure some aspects of our salutations table. I'm poking around a little more in the (TEST) system table TR_SALUTATION_FORMAT, where does the Salutation_inside field get populated from? Is it pulled from the description "Individual Informal" and, if so, do we need to create an "Household Informal" line?

Similar question for how to change the default "Gen Sal" to the informal. Is that a matter of reorganizing the ID # of the TR_SALUTATION_FORMAT table or is there a different way for having the default salutation be first name only?

Thank you for your help. As you can maybe tell, I don't have deep experience in SQL but we don't have an IT department. 

Take care,

Corinne 

In TR_SALUTATION_FORMAT, the Salutation_inside is generated from one of three columns:

• Single Name LI - individuals, organizations, and households with only an A1 affiliate
• Double Name LI - households when the A1 and A2 affiliates have different last names
• Same Name LI - households when the A1 and A2 affiliates have the same last name

The default salutation format is associated with each constituent type in TR_CUST_TYPE. In that table, your "Household" constituent type is probably associated with your "Household" salutation format, and your "Individual" constituent type is associated with one of your Individual salutation formats. The default salutation is used when creating a brand new constituent, or when you click the Gen Sal button. (If you right-click on the Gen Sal button, you can choose something other than the default.)

So to your question, if you want the default inside salutations to be first name only, you would want to look in TR_CUST_TYPE to see what default salutation type is used by each constituent type, and then make adjustments in TR_SALUTATION_FORMAT to Single Name LI, Double Name LI, and Same Name LI. Fortunately, no SQL skills required! The documentation will tell you what all the codes mean in the salutation table.

You will see changes reflected in newly created constituents or when regenerating salutations. Should you need it, the Rebuild Salutations utility can push changes en masse to your database, but please proceed with caution on that. 

-Michael Flaherty-Wilcox, Tessitura Network 

Thank you Michael! You are my new best friend. I will work this out in Test for sure; it's good to know about rebuild salutations but I will certainly only push that out once I know for sure I have it the way we want it in the Test environment.

Take care,

Corinne

So, working in Test, I have the Inside Name correct, but now on the Salutation 1 line I have an error. Can  you tell me where I went terribly wrong? Did I inadvertently change a line of code in the TR_SALUTATION_FORMAT?

Thanks!

JK! Sorted! I had to set up the Individual Informal line in TR_SALUTATION_FORMAT for the Salutation 1 line. The default code there was not functional, but by following the Salutation Format Table I could sort it out.

Thanks all,
CMR