Custom Criteria

Hi Michelle

For the Price Type Category criteria you would need to create a custom view that JOINS VS_TICKET_HISTORY to TR_PRICE_TYPE - I have included some basic code for the View below. Based upon that, your T_KEYWORD set up would be:

Desc: Tkt History Price Type Category

Data Type: Number

Edit Mask: 

Detail Tbl: LVS_CRITERIA_PRICE_TYPE_CATEGORY (or the name of the View you create)

Detail Col: !.price_type_category

Ref Tbl: TR_PRICE_TYPE_CATEGORY

Ref Idcol: id

Ref Desccol: description

Primary Group Default: Default Value Number

Category: Ticketing

Use for List: List Only

The Unique Production Season criteria looks correct. But with all custom work it pays to check the Results to make sure they are accurate.

Hope that helps!

Sandra

Sample View for Price Type Category

USE [impresario]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [LVS_CRITERIA_PRICE_TYPE_CATEGORY]
AS
/************************************************************************
Custom list criteria for Price Type Category
*************************************************************************/
select a.*, b.price_type_category from VS_TICKET_HISTORY a
join TR_PRICE_TYPE b on a.price_type = b.id
GO

GRANT REFERENCES, SELECT on [LVS_CRITERIA_PRICE_TYPE_CATEGORY] to impusers, tessitura_app
GO

Thank you SO much. This is amazing! 

Michelle,

Sandra's solution looks perfect, but the one thing I would change for the Unique Production Seasons would be to use the new view you created instead of using the VS_TICKET_HISTORY view. When you create a list using criteria that use different views, it generates 2 distinct queries with independent WHERE statements. So if you use VS_TICKET_HISTORY for your unique prod season criteria, it will pull anyone matching that criteria regardless of price type category, then it will also pull in anyone with that price type category, regardless of whether they meet the criteria for unique prod season.

Where is it that you would use the new view (instead of VS_TICKET_HISTORY) exactly? Thanks!

A short correction to my previous statement, they will still have to fit both criteria but they will be mutually exclusive(I hope I'm using that right). So a customer still has to fit the distinct unique prod season category, and have ticket history at that price type category, but it wont be looking at the products together. 

So if you do want to point the unique production season to a different view just change the detail tbl column from VS_TICKET_HISTORY to the new view you just created.

So I actually haven't created a custom view for Unique Production Seasons at this point(only for Price Type Category). For unique production seasons, I just copied the row for for Ticket History Unique Perfs in T_KEYWORD and changed the Detail Col to count(distinct!.prod_season_no). I'm not quite adept enough in SQL to build a custom view from scratch, but open to trying something different if you have any ideas!

So if you used the exact query that Sandra provided, then the new view you created would include the price type category and all the data columns from VS_TICKET_HISTORY, including prod_season_no. So you wouldn't need to create a whole new view for this criteria, but rather use the same view for your Unique Production Season criteria as you are for the price type category criteria.

Hi Sandra Ashby I installed this into Test, but I am getting the following error: 'Syntax Error in query: Invalid object name 'LVS_CRITERIA_PRICE_TYPE_CATEGOROY'. I am able to run a basic query with this view in SSMS, so I imagine it would be something in the T_KEYWORD set-up, but double checked my row matches what you suggested above. Thoughts?

Yep, view was created first. And includes access rights. Thanks!

One more thing ...

In the Detail Tbl column in T_KEYWORD - make sure the name is identical to the View you are referencing ... 

I think you may have a typo - LVS_CRITERIA_PRICE_TYPE_CATEGOROY instead of LVS_CRITERIA_PRICE_TYPE_CATEGORY

Hopefully it's that simple! :-)

Unfortunately that was just an error in my post! When I went to look at the view I installed in SSMS, I noticed that it was named

TNHS\Mblau310.LVS_CRITERIA_PRICE_TYPE_CATEGORY instead of with dbo.LVS_CRITERIA_PRICE_TYPE_CATEGORY (which I would expect to see). I put in a ticket, but figured I'd still post my findings here!

So I noticed after the cloud migration, it no longer defaults to the dbo schema when you create new objects like tables, views, or procedures. So you have to explicitly state it in your CREATE VIEW query. My suggestion would be to simply delete the TNHS\Mblau310.LVS_CRITERIA_PRICE_TYPE_CATEGORY view, you can use a DROP VIEW command or simply right click it in the object explorer and delete from there. Then run Sandra's query again but replace CREATE VIEW [LVS_CRITERIA_PRICE_TYPE_CATEGORY] with CREATE VIEW [dbo].[LVS_CRITERIA_PRICE_TYPE_CATEGORY]

Ah, this worked! 

Unfortunately, however, the results didn't match the criteria selected (i.e. I selected Single Tickets as the category for a specific production season, but still got subs (that did not have single tickets for said prod season). Getting closer step by step at least!