• Replies 2 replies
  • Subscribers 332 subscribers
  • Views 32 views
  • Users 0 members are here
Related

narrowing a list to one MOS

Jessica Daugherty
$organization

I was asked to create a list of everyone who purchased a subscription online since 10/1/2012, but had trouble narrowing the list to the Web sales channel or to Web Subscription MOS. (We didn't make web-specific price types for our packages this season... but barring a better solution, we will next season, since that would work).

Support tried to help us out on the fly with a custom "Tickets - MOS custom" criterion, to narrow results to one (or more) MOS, but it will only function as though "Has" has been selected, not "In," regardless of which is selected, so it hasn't been able to require all criterion to be on one order. Support suggested I post here, so I'm trying you guys before I contact the consulting team.

Other than by using price types as a criterion, does anyone have way to narrow sales to Web only?

Thanks for any help!!

(More background info:

  • We're on v.10, but expect to be on v.11 by the end of this month.
  • We tried criterion Channel, which did not work as expected. Support says it's not a standard criterion to narrow to one sales channel.
  • The custom criterion setup we were given was the following entry in T_KEYWORD:

    Description: Tickets - Order MOS Custom
    Data Type:  Number
    Edit Mask:  Number
    Detail Tbl:  T_ORDER
    Detail Col:  !.mos
    Ref Tbl:  tr_mos
    Ref Idcol:  id
    Refdescol:  description
    Category:  Ticketing
    Use for List:  List Only
    Control Group:  <set as needed>
    Keyword Desc:  <set as needed>
    Primary Group Default:  Default Value No

  • The above is what added the custom criterion that functions for us as though "Has" rather than "In" has been selected.
  • Support suggested building a list within a list, where List 1 pulled by date and price type criteria, and List 2 pulled with criteria  "In" List 1 and "In" Tickets - MOS custom (MOS # 14). The result was everyone in List 1 who had MOS 14 anywhere in their ticketing history, including before 10/1/2012.
    --- The SQL query for List 2 was:

    Select Distinct a.customer_no
     From t_customer a (NOLOCK)
    JOIN t_list_contents e (NOLOCK) ON a.customer_no = e.customer_no
    JOIN T_ORDER f (NOLOCK) ON a.customer_no = f.customer_no
     Where  IsNull(a.inactive, 1) = 1
     AND e.list_no in (908)
     AND f.mos in  (14)
  • Support asked us to create a new List 2 using Manual Edit with the following SQL query that would pull everyone in List 1 (908) and "In" MOS Web Subscription (14):

    Select Distinct a.customer_no
    From V_CUSTOMER_WITH_PRIMARY_GROUP a (Nolock)
    JOIN t_list_contents e (Nolock) ON e.customer_no = a.customer_no
    JOIN T_ORDER f (Nolock) ON f.customer_no = a.customer_no|
    Where 1 = 1
    AND e.list_no in (908)
    AND f.MOS in (14)

    The result was an error when we tried to generate:

    SQLSTATE = 42S02
    Microsoft OLE DB provider for SQL Server
    Invalid object name "V_CUSTOMER_WITH_PRIMARY_GROUP"

And that's where we are at the moment.

Thanks!
Jessica

Parents
  • $organization

    Hi Jessica,

     

    You can use the Order Search screen to find all people who have orders with your Web Subscription MOS and/or Web Sales channel since 10/1 and then save the results to a list.  Here are instructions on how to do that: http://www.tessituranetwork.com/Help_System/Content/List%20Creation%20and%20Management/Creating%20a%20List%20from%20the%20Order.htm

    When running the order search you’ll need to enter a performance date range that includes any performances that could currently be bought in the packages.

     

    A couple of other notes, to accomplish what you are attempting in list criteria you will have to update (customize) your performance and or subscription histories to include MOS.  And then you can add MOS list criteria that can be combined with other performance or subscription history criteria to look for a single row that meets all the criteria.  For criteria to combine to find a single row in a table they must all be pointing to the same view or table. The MOS criterion you added was looking at the Order table while all the other criteria were looking at the performance history table.

     

    And the problem with the manual edit to the code you see is that it is v11 code and is trying to pull from a new v11 view.  If you change to V_CUSTOMER_WITH_PRIMARY_GROUP to T_CUSTOMER the query should run.  I’m not sure if it will get you the result you want (SQL code isn’t my strongsuit), but it shouldn’t give you an error anymore.

     

     

    Kevin Sheehan

    Senior Technical Writer & Consultant

    Tessitura Network

    +1 888 643 5778 x 329

    ksheehan@tessituranetwork.com

     

Reply
  • $organization

    Hi Jessica,

     

    You can use the Order Search screen to find all people who have orders with your Web Subscription MOS and/or Web Sales channel since 10/1 and then save the results to a list.  Here are instructions on how to do that: http://www.tessituranetwork.com/Help_System/Content/List%20Creation%20and%20Management/Creating%20a%20List%20from%20the%20Order.htm

    When running the order search you’ll need to enter a performance date range that includes any performances that could currently be bought in the packages.

     

    A couple of other notes, to accomplish what you are attempting in list criteria you will have to update (customize) your performance and or subscription histories to include MOS.  And then you can add MOS list criteria that can be combined with other performance or subscription history criteria to look for a single row that meets all the criteria.  For criteria to combine to find a single row in a table they must all be pointing to the same view or table. The MOS criterion you added was looking at the Order table while all the other criteria were looking at the performance history table.

     

    And the problem with the manual edit to the code you see is that it is v11 code and is trying to pull from a new v11 view.  If you change to V_CUSTOMER_WITH_PRIMARY_GROUP to T_CUSTOMER the query should run.  I’m not sure if it will get you the result you want (SQL code isn’t my strongsuit), but it shouldn’t give you an error anymore.

     

     

    Kevin Sheehan

    Senior Technical Writer & Consultant

    Tessitura Network

    +1 888 643 5778 x 329

    ksheehan@tessituranetwork.com

     

Children
  • $organization in reply to Kevin Sheehan

    Thank you, Kevin! I was able to create the list I needed using a cumulative search on Order Search. That's a huge help.

    The option to make a dynamic list narrowed to an MOS would help Education and Groups Sales as well as myself, but if it comes up often enough to be an issue, we'll ask about getting the custom work to add MOS to the performance and Subscription histories.

    Thanks again!!
    Jessica