What is the best way of creating a constituency for those that purchase a subscription ticket?
When some purchases a subscription ticket we need to create a constituency so we are able to pull that information via the WebAPI.
THis would be something that happens regularly
Hi Parnell,
Take a look at the document called Localized Procedures (I've attached it to this post)...there are two procedures which populate Performance and Subscription History...both have optional parameters to add a particular constituency to each customer based on the Subscription/Performance seasons a customer buys...See below for Subscription History blurb from the Localized Procedures document...Hope this helps...Cheers, James
LP_UPDATE_SUB_HIST This procedure is designed to update the subscription history table from live orders containing fixed seat packages. It is not part of the application, and is instead run from the SQL Server Job Scheduler. As such, this procedure can be changed in any way desired, including adding or subtracting parameters. Current parameters to this procedure are season_no and constituency. Constituency is optional and indicates a constituency code which should be applied to all constituents which have subscription history rows added. This procedure, as written, can be run anytime. It deletes all existing subscription history for the season and recreates it, then optionally removes all existing constituencies of the desired type and recreates them.
Hi Parnell
Depending upon how your organization operates, using the LP_UPDATE_SUB_HIST may not be suitable for adding constituencies. That is, you may need to add this to the procedure if it has been previously modified, or your organization may have multiple seasons in one year that would be using the same constituency and the current procedure doesn’t allow for a season string.
Instead of modifying the procedure, you could use the report Manage Constituencies under Data Management. All you need to do is create a dynamic list that identifies who are to get the constituency (based upon subscription history or other criteria). Once you have set the report up can then schedule it to run on a daily basis.
When you open the parameter window for the report click the Report Help button to get all the information about what each parameter does. I would suggest to trial it in your Test environment first.
Always good to have a 2nd option!
Sandra
Sandra Ashby
Application Specialist, Asia-Pacific
Tessitura Network, Inc
Tel: +1 888 643 5778 ext 325
sashby@tessituranetwork.com
Here at the ACO we have an overnight SQL job that runs every night that has a set of stored procedures to do various tasks. What happens for us is that LP_UPDATE_SUBS_HIST is run - it allows for a string of season numbers to be passed back and I'd be happy to share - it's really easy logic. This updates the LT_SUB_HIST table so that when the next script LP_ACO_AUTO_CONSTITUENCIES runs, it can just look at people who have a subscription package where the season has an fyear value of the year that the script is run.
Send me a message or something if you want more detail!
Martin
I am interested. The standard LP_UPDATE_SUBS_HIST will do the trick and add the constituency of "Subscriber" easy enough. Thinking long term, next year when subscriptions are new or renewed no problems as LP_UPDATE_SUBS_HIST will add them or leave them. BUT, if they are not renewed I want to terminate the "Subscriber" constituency.
This is the issue I'm trying to understand how also to solve.
We have a scheduled job that runs nightly after lt_sub_hist is updated. It removes all subscriber constituencies and then regenerates them all based on the sub hist at that moment. It does it for whatever we've defined as the "current" season. So people with cancelled orders, after a day no longer have the sub consituency, and when we start a new season, last season's subs no longer have the "current subscriber" constituency.