How difficult is it to build a custom output set that pulls constituents' email addresses and sources from a selected appeal? We currently already have a custom output that does this for mailing addresses that was built by an outside vendor. I'm curious if it is feasible to build one for emails in-house.
Thanks,
Chuck Buchanan
92nd Street Y
Easy, or moderately easy, depending on what you want to do.
Under Tools > Output Set Builder, click on the New Set button. Select Eaddress_email from the EAddress Information folder (double-click on it, or drag it to the right under Output Element). Click on OK, and give it a name such as Email Only.
What gets tricky is how you use eaddresses. You can define which eaddress gets pulled when you run reports or extractions that allow you to select eaddress type and mailing purpose; but you need to be clear about how you treat eaddresses in your setup.
Lucie
_____________________________Lucie SpielerIT Development and Training Manager
Editor, Season Program BookFLORIDA GRAND opera
I already built an output set with the e-address types I want and when I run it I'm getting a list of Const IDs, First Names, Last Names, and the Email Addresses which I can save to Excel. I want this report to be able to include corresponding source codes from an extraction already promoted to an appeal when I save to Excel.
You need to set up an output element that pulls source info from promotion records for a constituent (I think that’s in T_PROMOTION but I might be wrong there) and accepts a parameter for the appeal so you only pull promotions from the appeal you are interested in.
Kevin Sheehan
Senior Documentation & Learning Resources Specialist
Tessitura Network
+1 888 643 5778 x 329
ksheehan@tessituranetwork.com
Promotion Appeal and Promotion Source Number exist within query builder, but you’ll get every appeal and source connected with that customer id. So what you need to do is be able to limit by appeal.
Try this and see if it works for you:
STEP 1:
Go to TR_QUERY_ELEMENT_PARAMETER and add a row:
Id = [next value]
Description = Appeal
Data Type = Number
End of Day = unchecked
Multi-Select = unchecked
Ref Tbl = VS_APPEAL
Ref Id = appeal_no
Ref Desc = description
Ref Where = [leave blank]
Ref Sort = inactive desc, description
STEP 2:
Go to TR_QUERY_ELEMENT and add two rows
[NOTE: the value in <<p#>> for both rows below needs to be replaced with the number value of the Id row you added in TR_QUERY_ELEMENT_PARAMETER]:
First row:
Description = Promoted Eaddress in Appeal
Category = Promotions
Data Select = !.eaddress
Data From = (select a.customer_no, a.appeal_no, a.eaddress as eaddress, b.source_name as source_name from vs_promotion a join tx_appeal_media_type b on a.source_no = b.source_no)
Data Where = a.appeal_no IN (<<p#>>) and isnull(eaddress,'N')<>'N'
Control Group = [appropriate value for your organization, if any]
Second row:
Description = Sources in Appeal
Data Select = !.source_name
STEP 3:
Go to Tools > Output Set Builder and create a new set. Select Promoted Eaddress in Appeal and Sources in Appeal from the Promotions folder as Output Elements. For both, click on the chicklet to the right and select the same appeal. Name the output set something that identifies the appeal you are using.
This output set will return a row for every constituent id on the list you use when you run the Execute an Output Set report, but give eaddresses and source names for those promoted in that appeal.
That worked great!
Thank you, Lucie!