• Replies 9 replies
  • Subscribers 414 subscribers
  • Views 45 views
  • Users 0 members are here
Related

Pulling a Constituency

Marie Kocher
$organization

Hello all, 

We add a "Academy Student" constituency to all of our Ballet School Student records. The start date for the constituency is the first day of classes, Aug 31 - thus, a future date from today.

I need to pull a list of these students today, however my list returns no records. 

How can I pull a constituency with a future date? Furthermore, how can I pull a constituency with a past end date?

Thanks, 

Marie Kocher

Development Assistant

Kansas City Ballet

Todd Bolender Center for Dance & Creativity

500 W Pershing Rd

Kansas City, MO 64108

w 816.931.2232 | d 816.216.5582

  • Former Member
    Former Member $organization

    Hi Marie,

    You can accomplish this with a couple of manual edits to your list query. If you're comfortable doing that, it's relatively simple. I built a simple list with only "Constituency in..." and then clicked into Manual Edit and made the changes below. (If you're not on v12 it may look a little different but I believe the same approach should work.)

    Edits:

     

     

    • Replace vxs_const_cust with tx_const_cust 
    • Add this text: and a1.start_dt >= '2015/08/31'

    Result:

    Select Distinct a.customer_no
    From V_CUSTOMER_WITH_PRIMARY_GROUP a  WITH (NOLOCK)
    JOIN (

    Select a1.customer_no
    From tx_const_cust a1 WITH (NOLOCK) Where a1.constituency in (11)
      and a1.start_dt >= '2015/08/31'

    ) as e ON e.customer_no = a.customer_no
    Where  IsNull(a.inactive, 1) = 1

    "11" is the constituency I chose, so that will be different in your list depending on which constituency(s) you select. This same technique works for looking at constituencies past their end dates. You could change the above to this, for example:

    Select Distinct a.customer_no 
    From V_CUSTOMER_WITH_PRIMARY_GROUP a  WITH (NOLOCK)
    JOIN (

    Select a1.customer_no
    From tx_const_cust a1 WITH (NOLOCK) Where a1.constituency in (11)
      and a1.start_dt >= '2013/08/31'
      and a1.end_dt <= '2014/08/30 23:59:59'     

    ) as e ON e.customer_no = a.customer_no
    Where  IsNull(a.inactive, 1) = 1

     

    If this is something you have to do often, you might want to look into having your DBA build some custom list criteria to save time in the future. I don't think it would be terribly difficult.

    Hope that helps!

  • $organization
    Matthew, 

    I will try this tomorrow when I am back in the office. THANK YOU!

    Thanks,

     

    Marie

    __

    Marie Kocher | Development Assistant

    Kansas City Ballet

    Todd Bolender Center for Dance & Creativity

    500 W Pershing Rd

    Kansas City, MO 64108

    816-931-2232 ext 1382 | 816-931-1172 (fax)

    mkocher@kcballet.org

     

    Buy tickets or enroll for classes: www.kcballet.org

    From: Tessitura Development Forum [forums-development@tessituranetwork.com] on behalf of Matthew Echert [bounce-matthewechert5584@tessituranetwork.com]
    Sent: Wednesday, August 26, 2015 5:26 PM
    To: Marie Kocher
    Subject: Re: [Tessitura Development Forum] Pulling a Constituency

    Hi Marie,

    You can accomplish this with a couple of manual edits to your list query. If you're comfortable doing that, it's relatively simple. I built a simple list with only "Constituency in..." and then clicked into Manual Edit and made the changes below. (If you're not on v12 it may look a little different but I believe the same approach should work.)

    Edits:

     

     

    • Replace vxs_const_cust with tx_const_cust 
    • Add this text: and a1.start_dt >= '2015/08/31'

    Result:

    Select Distinct a.customer_no
    From V_CUSTOMER_WITH_PRIMARY_GROUP a  WITH (NOLOCK)
    JOIN (

    Select a1.customer_no
    From tx_const_cust a1 WITH (NOLOCK) Where a1.constituency in (11)
      and a1.start_dt >= '2015/08/31'

    ) as e ON e.customer_no = a.customer_no
    Where  IsNull(a.inactive, 1) = 1

    "11" is the constituency I chose, so that will be different in your list depending on which constituency(s) you select. This same technique works for looking at constituencies past their end dates. You could change the above to this, for example:

    Select Distinct a.customer_no 
    From V_CUSTOMER_WITH_PRIMARY_GROUP a  WITH (NOLOCK)
    JOIN (

    Select a1.customer_no
    From tx_const_cust a1 WITH (NOLOCK) Where a1.constituency in (11)
      and a1.start_dt >= '2013/08/31'
      and a1.end_dt <= '2014/08/30 23:59:59'     

    ) as e ON e.customer_no = a.customer_no
    Where  IsNull(a.inactive, 1) = 1

     

    If this is something you have to do often, you might want to look into having your DBA build some custom list criteria to save time in the future. I don't think it would be terribly difficult.

    Hope that helps!

    From: Marie Kocher <bounce-mariekocher5698@tessituranetwork.com>
    Sent: 8/26/2015 4:10:26 PM

    Hello all, 

    We add a "Academy Student" constituency to all of our Ballet School Student records. The start date for the constituency is the first day of classes, Aug 31 - thus, a future date from today.

    I need to pull a list of these students today, however my list returns no records. 

    How can I pull a constituency with a future date? Furthermore, how can I pull a constituency with a past end date?

    Thanks, 

    Marie Kocher

    Development Assistant

    Kansas City Ballet

    Todd Bolender Center for Dance & Creativity

    500 W Pershing Rd

    Kansas City, MO 64108

    w 816.931.2232 | d 816.216.5582




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Development Forum. You may reply to this message to post to the Development forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
  • $organization in reply to Former Member

    I'm trying to add a Constituency End Date to the list criteria so I can pull a list of people who have expired Subs constituencies. I have Constituency Start Date but no End Date.  I have added the Constituency End Date to the T_Keyword table.

    I have tx_const_cust as the Detail Tbl. !.end_date as the Detail Col. 

    I did find a message from Kevin that said make sure the new entry references the view not the table but I tried vxs_const_cust and I got an error message. I switched it to tx_const_cust and got the same error message. What am I doing wrong? 

    Thx in advance. 

  • $organization in reply to Adria Gunter

    Not sure if I'm adding to the prior posts, but this query works for me-

    SELECT distinct
    a.customer_no
    FROM TX_CONST_CUST a
    WHERE a.constituency=77 and a.end_dt<'2014-9-1'

    It catches all people with a constituency that ends prior to Sept 1 2014.

    Megan

  • $organization in reply to Adria Gunter

    Worth noting that the syntax catches anyone with an expiration prior to 8/31. So if you want to catch people w/ 8/31, the end date needs to be <9/1/16. Is that by chance the issue?

    I don't believe that any table modifications were made for that query to work in our system.

    Megan

  • $organization in reply to Adria Gunter

    Hi Adria,

    You mentioned that you got the keyword # which sounds like you might be using the ID from

    T _KEYWORD instead of the ID from TR_CONSTITUENCY, i.e. a.constituency=# from the latter table.

    Cheers,

    Kevin

  • $organization
    Hey. Thanks. So I did this:
    SELECT distinct
    a.customer_no
    FROM TX_CONST_CUST a
    WHERE a.constituency=324 and a.end_dt<'2016-8-31'

    324 is the keyword # in our table for subscribers.
    I'm getting 0 results. I'm concerned that if the End Date isn't in the TX_CONST_CUST column then it won't pull it, right? 


    On Mon, Feb 1, 2016 at 2:51 PM, Megan Hall <bounce-meganhall6920@tessituranetwork.com> wrote:

    Not sure if I'm adding to the prior posts, but this query works for me-

    SELECT distinct
    a.customer_no
    FROM TX_CONST_CUST a
    WHERE a.constituency=77 and a.end_dt<'2014-9-1'

    It catches all people with a constituency that ends prior to Sept 1 2014.

    Megan

    From: Adria Gunter <bounce-adriagunter1034@tessituranetwork.com>
    Sent: 2/1/2016 2:12:54 PM

    I'm trying to add a Constituency End Date to the list criteria so I can pull a list of people who have expired Subs constituencies. I have Constituency Start Date but no End Date.  I have added the Constituency End Date to the T_Keyword table.

    I have tx_const_cust as the Detail Tbl. !.end_date as the Detail Col. 

    I did find a message from Kevin that said make sure the new entry references the view not the table but I tried vxs_const_cust and I got an error message. I switched it to tx_const_cust and got the same error message. What am I doing wrong? 

    Thx in advance. 



    --
    View this message online at http://www.tessituranetwork.com/Community/forums/p/14250/46394.aspx#46394 or reply to this message

    Adria Gunter | Subscriptions and Groups Manager

    STUDIO THEATRE

    1501 14TH ST NW

    WASHINGTON DC  20005

    T  202-232-7267, ext. (365)

    F  202-588-5262

    www.studiotheatre.org

    Subscriptions are online!   Reserve your group of 10+ today and enjoy a discount!

    2015-2016 SEASON MAIN SERIES    CHIMERICA |THE APPLE FAMILY CYCLE: SORRY & REGULAR SINGING |  BETWEEN RIVERSIDE AND CRAZY  | MOMENT | HEDDA GABLER

    2015-2016 SEASON   STUDIO X    ANIMAL | CONSTELLATIONS   

    2015-2016  SPECIAL REMOUNT    BAD JEWS

  • $organization
    I tried this and it still comes up with 0 constituents. 

    SELECT distinct
    a.customer_no
    FROM TX_CONST_CUST a
    WHERE a.constituency=324 and a.end_dt<'2016/9/1'

    This is what I'm trying to catch. 

    There are still some subscribers who aren't being updated with my utility
    Inline image 1

    The constituency should expire on 8/31/2016 not on 3/28/2014. So I'm trying to find all of the subscribers in this current season who still have an expired Subsciber constituency so I can update them to the 8/31/2016 date. 

    Thanks so much. 

    On Mon, Feb 1, 2016 at 3:31 PM, Megan Hall <bounce-meganhall6920@tessituranetwork.com> wrote:

    Worth noting that the syntax catches anyone with an expiration prior to 8/31. So if you want to catch people w/ 8/31, the end date needs to be <9/1/16. Is that by chance the issue?

    I don't believe that any table modifications were made for that query to work in our system.

    Megan

    From: Adria Gunter <bounce-adriagunter1034@tessituranetwork.com>
    Sent: 2/1/2016 8:07:51 PM

    Hey. Thanks. So I did this:
    SELECT distinct
    a.customer_no
    FROM TX_CONST_CUST a
    WHERE a.constituency=324 and a.end_dt<'2016-8-31'

    324 is the keyword # in our table for subscribers.
    I'm getting 0 results. I'm concerned that if the End Date isn't in the TX_CONST_CUST column then it won't pull it, right? 


    On Mon, Feb 1, 2016 at 2:51 PM, Megan Hall <bounce-meganhall6920@tessituranetwork.com> wrote:

    Not sure if I'm adding to the prior posts, but this query works for me-

    SELECT distinct
    a.customer_no
    FROM TX_CONST_CUST a
    WHERE a.constituency=77 and a.end_dt<'2014-9-1'

    It catches all people with a constituency that ends prior to Sept 1 2014.

    Megan

    From: Adria Gunter <bounce-adriagunter1034@tessituranetwork.com>
    Sent: 2/1/2016 2:12:54 PM

    I'm trying to add a Constituency End Date to the list criteria so I can pull a list of people who have expired Subs constituencies. I have Constituency Start Date but no End Date.  I have added the Constituency End Date to the T_Keyword table.

    I have tx_const_cust as the Detail Tbl. !.end_date as the Detail Col. 

    I did find a message from Kevin that said make sure the new entry references the view not the table but I tried vxs_const_cust and I got an error message. I switched it to tx_const_cust and got the same error message. What am I doing wrong? 

    Thx in advance. 



    --
    View this message online at http://www.tessituranetwork.com/Community/forums/p/14250/46394.aspx#46394 or reply to this message

    Adria Gunter | Subscriptions and Groups Manager

    STUDIO THEATRE

    1501 14TH ST NW

    WASHINGTON DC  20005

    T  202-232-7267, ext. (365)

    F  202-588-5262

    www.studiotheatre.org

    Subscriptions are online!   Reserve your group of 10+ today and enjoy a discount!

    2015-2016 SEASON MAIN SERIES    CHIMERICA |THE APPLE FAMILY CYCLE: SORRY & REGULAR SINGING |  BETWEEN RIVERSIDE AND CRAZY  | MOMENT | HEDDA GABLER

    2015-2016 SEASON   STUDIO X    ANIMAL | CONSTELLATIONS   

    2015-2016  SPECIAL REMOUNT    BAD JEWS



    --
    View this message online at http://www.tessituranetwork.com/Community/forums/p/14250/46396.aspx#46396 or reply to this message

    Adria Gunter | Subscriptions and Groups Manager

    STUDIO THEATRE

    1501 14TH ST NW

    WASHINGTON DC  20005

    T  202-232-7267, ext. (365)

    F  202-588-5262

    www.studiotheatre.org

    Subscriptions are online!   Reserve your group of 10+ today and enjoy a discount!

    2015-2016 SEASON MAIN SERIES    CHIMERICA |THE APPLE FAMILY CYCLE: SORRY & REGULAR SINGING |  BETWEEN RIVERSIDE AND CRAZY  | MOMENT | HEDDA GABLER

    2015-2016 SEASON   STUDIO X    ANIMAL | CONSTELLATIONS   

    2015-2016  SPECIAL REMOUNT    BAD JEWS

  • $organization
    Thank you. That seems to be it. At least I have a number. I was wondering why the Subscriber number was in the 300s. It didn't make them seem important. 

    On Mon, Feb 1, 2016 at 5:11 PM, Kevin Madeira <bounce-kevinmadeira9286@tessituranetwork.com> wrote:

    Hi Adria,

    You mentioned that you got the keyword # which sounds like you might be using the ID from

    T _KEYWORD instead of the ID from TR_CONSTITUENCY, i.e. a.constituency=# from the latter table.

    Cheers,

    Kevin

    From: Adria Gunter <bounce-adriagunter1034@tessituranetwork.com>
    Sent: 2/1/2016 8:52:51 PM

    I tried this and it still comes up with 0 constituents. 

    SELECT distinct
    a.customer_no
    FROM TX_CONST_CUST a
    WHERE a.constituency=324 and a.end_dt<'2016/9/1'

    This is what I'm trying to catch. 

    There are still some subscribers who aren't being updated with my utility
    Inline image 1

    The constituency should expire on 8/31/2016 not on 3/28/2014. So I'm trying to find all of the subscribers in this current season who still have an expired Subsciber constituency so I can update them to the 8/31/2016 date. 

    Thanks so much. 

    On Mon, Feb 1, 2016 at 3:31 PM, Megan Hall <bounce-meganhall6920@tessituranetwork.com> wrote:

    Worth noting that the syntax catches anyone with an expiration prior to 8/31. So if you want to catch people w/ 8/31, the end date needs to be <9/1/16. Is that by chance the issue?

    I don't believe that any table modifications were made for that query to work in our system.

    Megan

    From: Adria Gunter <bounce-adriagunter1034@tessituranetwork.com>
    Sent: 2/1/2016 8:07:51 PM

    Hey. Thanks. So I did this:
    SELECT distinct
    a.customer_no
    FROM TX_CONST_CUST a
    WHERE a.constituency=324 and a.end_dt<'2016-8-31'

    324 is the keyword # in our table for subscribers.
    I'm getting 0 results. I'm concerned that if the End Date isn't in the TX_CONST_CUST column then it won't pull it, right? 


    On Mon, Feb 1, 2016 at 2:51 PM, Megan Hall <bounce-meganhall6920@tessituranetwork.com> wrote:

    Not sure if I'm adding to the prior posts, but this query works for me-

    SELECT distinct
    a.customer_no
    FROM TX_CONST_CUST a
    WHERE a.constituency=77 and a.end_dt<'2014-9-1'

    It catches all people with a constituency that ends prior to Sept 1 2014.

    Megan

    From: Adria Gunter <bounce-adriagunter1034@tessituranetwork.com>
    Sent: 2/1/2016 2:12:54 PM

    I'm trying to add a Constituency End Date to the list criteria so I can pull a list of people who have expired Subs constituencies. I have Constituency Start Date but no End Date.  I have added the Constituency End Date to the T_Keyword table.

    I have tx_const_cust as the Detail Tbl. !.end_date as the Detail Col. 

    I did find a message from Kevin that said make sure the new entry references the view not the table but I tried vxs_const_cust and I got an error message. I switched it to tx_const_cust and got the same error message. What am I doing wrong? 

    Thx in advance. 



    --
    View this message online at http://www.tessituranetwork.com/Community/forums/p/14250/46394.aspx#46394 or reply to this message

    Adria Gunter | Subscriptions and Groups Manager

    STUDIO THEATRE

    1501 14TH ST NW

    WASHINGTON DC  20005

    T  202-232-7267, ext. (365)

    F  202-588-5262

    www.studiotheatre.org

    Subscriptions are online!   Reserve your group of 10+ today and enjoy a discount!

    2015-2016 SEASON MAIN SERIES    CHIMERICA |THE APPLE FAMILY CYCLE: SORRY & REGULAR SINGING |  BETWEEN RIVERSIDE AND CRAZY  | MOMENT | HEDDA GABLER

    2015-2016 SEASON   STUDIO X    ANIMAL | CONSTELLATIONS   

    2015-2016  SPECIAL REMOUNT    BAD JEWS



    --
    View this message online at http://www.tessituranetwork.com/Community/forums/p/14250/46396.aspx#46396 or reply to this message

    Adria Gunter | Subscriptions and Groups Manager

    STUDIO THEATRE

    1501 14TH ST NW

    WASHINGTON DC  20005

    T  202-232-7267, ext. (365)

    F  202-588-5262

    www.studiotheatre.org

    Subscriptions are online!   Reserve your group of 10+ today and enjoy a discount!

    2015-2016 SEASON MAIN SERIES    CHIMERICA |THE APPLE FAMILY CYCLE: SORRY & REGULAR SINGING |  BETWEEN RIVERSIDE AND CRAZY  | MOMENT | HEDDA GABLER

    2015-2016 SEASON   STUDIO X    ANIMAL | CONSTELLATIONS   

    2015-2016  SPECIAL REMOUNT    BAD JEWS



    --
    View this message online at http://www.tessituranetwork.com/Community/forums/p/14250/46398.aspx#46398 or reply to this message

    Adria Gunter | Subscriptions and Groups Manager

    STUDIO THEATRE

    1501 14TH ST NW

    WASHINGTON DC  20005

    T  202-232-7267, ext. (365)

    F  202-588-5262

    www.studiotheatre.org

    Subscriptions are online!   Reserve your group of 10+ today and enjoy a discount!

    2015-2016 SEASON MAIN SERIES    CHIMERICA |THE APPLE FAMILY CYCLE: SORRY & REGULAR SINGING |  BETWEEN RIVERSIDE AND CRAZY  | MOMENT | HEDDA GABLER

    2015-2016 SEASON   STUDIO X    ANIMAL | CONSTELLATIONS   

    2015-2016  SPECIAL REMOUNT    BAD JEWS