Hello!
I'm working on building 2 custom criterion and I need a little assistance.
Price Type Category: Below is what I have in T_KEYWORD, but received the error: 'Syntax Error in Query: Invalid column name 'customer_no (List Sql). Based on the error, I think this means that my issue is the Detail Table doesn't contain a customer id column. I think what I need to do in this case is create a custom view?
Desc: Price Type Category
Data Type: Number
Edit Mask: Alpha Numeric
Detail Tbl: TR_PRICE_TYPE
Detail Col: !.price_type_category
Ref Tbl: TR_PRICE_TYPE_CATEGORY
Ref Idcol: id
Ref Desccol: description
Primary Group Default: Default Value Number
Unique Production Seasons: What I want is to be able to pull lists of patrons that purchased tickets to more than 1 production each season. I started by copying Ticket History Unique Perfs in T_KEYWORD. This worked successfully without errors, but wanted to confirm I did this correctly.
Desc: Tkt History Unique Prod Season
Edit Mask: Number
Detail Tbl: vs_ticket_history
Detail Col: count(distinct !.prod_season_no)
Thanks!
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
Edit Mask:
Detail Tbl: LVS_CRITERIA_PRICE_TYPE_CATEGORY (or the name of the View you create)
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]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO
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 ajoin TR_PRICE_TYPE b on a.price_type = b.idGOGRANT REFERENCES, SELECT on [LVS_CRITERIA_PRICE_TYPE_CATEGORY] to impusers, tessitura_appGO
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!