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!
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.