• Replies 14 replies
  • Subscribers 414 subscribers
  • Views 259 views
  • Users 0 members are here
Related

Custom Criteria

Michelle Blau (Past Member)
$organization

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

Data Type: Number

Edit Mask: Number

Detail Tbl: vs_ticket_history

Detail Col: count(distinct !.prod_season_no)

Thanks!

Parents
  • $organization

    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

  • $organization in reply to Sandra Ashby

    Thank you SO much. This is amazing! 

Reply Children
No Data