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

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! 

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?

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!