How can we add criteria to Output Set Builder for the following Affiliate names for Households contained in the list?
A1 First Name
A1 Last Name
A2 First Name
A2 Last Name
Is this possible?
Thanks,
Chuck
Hi all,
We build a custom version of VB_CUSTOMER that performs better (the 11.0.4 version still was very slow for us), and doesn't eliminate certain records. For example, we found that VB_CUSTOMER would not include A1 and A2 primary affiliate individuals, which is not good in many situations. (Try doing select vb_customer where customer_no = <A1>, where <A1> is the customer_no of an A1 affiliate record in your database and you'll see what I mean.)
Basically, we wanted a clean and simple way to pull customer name data in a fairly consistent manner. For example, fname column in this view is either the first name of an individual record or the A1 first name of the household record (i.e. you don't have to look in one column for households and a separate column for individual records). It also includes a few other helpful things, for example, it will build sort_name dynamically if sort_name happens to be null (which we found is the case for some of our records intermittently).
We then added additional objects to TR_QUERY_ELEMENT to pull from this view, then use those in output sets. We also use this view in many of the stored procedures we use for our reports.
Anyway, this may not work for all situations - for example it does not add any affiliate data for organizations at this point. I placed the code in my file space in case it is of interest. Click here to get it.
Thanks,David