Hi Todd,

Maybe I'm missing something here but it's not possible to use to mark an affiliate as primary unless the "umbrella" record is a household.  Perhaps you're using a specific Affiliation Type to mark the affiliation - we have a specific type for this sort of relationship.

Depending on how you intend to make the affiliate as the primary contact, the Query Element would be realtively easy to create.  I'd be happy to give you some help with this,

Martin

We have an affiliation type called "Primary Contact."  On most organization/business records there is an affiliated individual record with type Primary Contact.  When we run lists for Development they want all Organization records to include the Primary Contact info for mailings, emails, etc.  I can add the affiliated records when running the List/Extr however I get one record for the org and one record for the individual.  I want a single record with org and primary contact info together.  

I thought I could accomplish this with an Output Set and custom query elements.  I have created SQL that works however the code for many of the fields are beyond the 255 char limit for the query element.      

Hi Todd

The trick for complex code in Output Sets is to embed your code into a View, and then just refer to the View in your query element. I've had to do that a couple of times - works fine. The view should return the customer_no so the output set can link it in, and you probably should enforce a single-row per constituent in the view. but that's about all. The view below works, for example.

Or, of course, if the required output data is fairly stable, you could build a custom report.

-------------------------------------------------------------------

CREATE VIEW [dbo].[lv_aco_cust_cat]
AS

/*
maDE BY kENm 2013-07-09
Returns a primary category and subs package for ACO customers (SUB, Choose3, STB or NON
Intended for use in Output Sets and general analysis
Based on constituency
EXAMPLE
select * from [dbo].[lv_aco_cust_cat]
where customer_no in ( select customer_no from t_list_contents where list_no = 207147)

Mods
2013-07-10 KenM fixed to point at correct secured view for constituencies
v2 2016-01-04 KenM return best category from HH/indiv, not just direct
*/
SELECT
c.customer_no
, CASE
WHEN c.customer_no IN
(SELECT v.customer_no
FROM [dbo].[V_CUSTOMER_WITH_HOUSEHOLD] v
JOIN VX_CONST_CUST_ACTIVE x ON v.expanded_customer_no = x.customer_no
WHERE x.constituency = 5) THEN 'SUB'
WHEN c.customer_no IN
(SELECT v.customer_no
FROM [dbo].[V_CUSTOMER_WITH_HOUSEHOLD] v
JOIN VX_CONST_CUST_ACTIVE x ON v.expanded_customer_no = x.customer_no
WHERE x.constituency = 228) THEN 'CH3'
WHEN c.customer_no IN
(SELECT v.customer_no
FROM [dbo].[V_CUSTOMER_WITH_HOUSEHOLD] v
JOIN VX_CONST_CUST_ACTIVE x ON v.expanded_customer_no = x.customer_no
WHERE x.constituency = 30) THEN 'STB'
ELSE 'NON'
END AS category ,
ISNULL( (SELECT TOP 1 p.description
FROM lt_sub_hist LSH
JOIN T_PKG P ON LSH.pkg_no = P.pkg_no
WHERE LSH.season IN (
SELECT s.id FROM VRS_SEASON s
WHERE s.inactive = 'N'
AND s.description LIKE 'ACO%'
AND GETDATE() BETWEEN s.start_dt AND s.end_dt
AND s.id <> 402 -- exclude the mech season
)
AND lsh.customer_no IN (SELECT v.expanded_customer_no
FROM [dbo].[V_CUSTOMER_WITH_HOUSEHOLD] v
WHERE v.customer_no = c.customer_no )
--= c.customer_no
ORDER BY lsh.scrp_no ), '') pkg_type
FROM dbo.T_CUSTOMER AS c

 ----------------------------------------------------------------

Ken

Leslie,

Thanks for the reply.  The problem with the standard functionality of adding affiliates in Lists/Extractions is that it does not give you the organization info and affiliated info in the same record.  You end up with an org record and a separate individual record.  That is not the way our development department wants the output.  They want the org info and primary contact info on the same record.  

Yesterday I created a custom view and created query elements off from the view.  

Ken,

Thanks for the reply.  Looks like I was on the right track.  I created a custom view yesterday and then created query elements from the view.  I have some additional query elements to create but the basic structure is set and working.  

Thanks,
Todd T

Sandra,

I have been following this post and gave your Output Set Cookbook "Output Affiliated Account Name" instructions a try and it worked perfectly!