• Replies 13 replies
  • Subscribers 340 subscribers
  • Views 170 views
  • Users 0 members are here
Related

Dynamic List problems

Beth Varro
$organization

Hi all,

I'm having some trouble with some dynamic lists not "dynamic-ing" as they ought to. Some of them work, but some do not: I seem to be seeing it when I have a series of lists that should be updating each other, eg:

list 1 has criteria for various giving years,
list 2 is "has list 1" plus some other giving in the "OR" section,
list 3 is "has list 2" plus yet more criteria in the "OR" section.

The idea is that I should be able to pull list 3 and it should give me an updated count from lists 1 and 2, but when I run list 3 the numbers don't change based on edits to list 1 or 2.  If I manually regenerate list 1 or 2, then those updated numbers will pull into list 3.  I haven't had time to nail it down in more detail, as I'm focusing on getting these to work so I can run my renewals for this month.

We're down an IT person right now, so I thought I'd post something here real quick: does anyone know of any obvious user errors or setup issues that would cause this?  Else we'll escalate to Tessitura help. 

Thanks, all!

Beth

  • Former Member
    Former Member $organization

    Hey Beth!

    We recently experienced the same problem!  Unfortunately, i'm yet to find a good solution.  feel free to give me a call to discuss.  Anyone else run into this?

    James

  • $organization
    Hi James,

    Oh, I'm SO glad it's not just me!  I'm on several deadlines this week, but will call you next week...or, if you're going to the conference, perhaps we can chat there!  Tessitura lab time, anyone?

    Beth

    * * * * *

    Get ready for an unforgettable encounter 3,000 years in the making. Tutankhamun: The Golden King and the Great Pharaohs opens at the Science Museum of Minnesota on February 18, 2011, bringing priceless artifacts from the tomb of the Boy King to Minnesota for the first time. Don’t miss this opportunity; visit www.smm.org/tut to purchase tickets today.

    Elizabeth A Varro
    Membership Manager
    Science Museum of Minnesota
    (651) 265-9829

    From: "James Boncek" <bounce-jamesboncek9049@tessituranetwork.com>
    To: bvarro@smm.org
    Sent: Tuesday, July 5, 2011 2:27:23 PM
    Subject: Re: [Tessitura Technical Forum] Dynamic List problems

    Hey Beth!

    We recently experienced the same problem!  Unfortunately, i'm yet to find a good solution.  feel free to give me a call to discuss.  Anyone else run into this?

    James

    From: Beth Varro <bounce-elizabethvarro6946@tessituranetwork.com>
    Sent: 7/5/2011 11:54:50 AM

    Hi all,

    I'm having some trouble with some dynamic lists not "dynamic-ing" as they ought to. Some of them work, but some do not: I seem to be seeing it when I have a series of lists that should be updating each other, eg:

    list 1 has criteria for various giving years,
    list 2 is "has list 1" plus some other giving in the "OR" section,
    list 3 is "has list 2" plus yet more criteria in the "OR" section.

    The idea is that I should be able to pull list 3 and it should give me an updated count from lists 1 and 2, but when I run list 3 the numbers don't change based on edits to list 1 or 2.  If I manually regenerate list 1 or 2, then those updated numbers will pull into list 3.  I haven't had time to nail it down in more detail, as I'm focusing on getting these to work so I can run my renewals for this month.

    We're down an IT person right now, so I thought I'd post something here real quick: does anyone know of any obvious user errors or setup issues that would cause this?  Else we'll escalate to Tessitura help. 

    Thanks, all!

    Beth



    --
    View this message online at http://www.tessituranetwork.com/COMMUNITY/forums/p/4809/15754.aspx#15754 or reply to this message
    --
    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical 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

    Beth,

    I don't know exactly what your criteria are for your lists but I would probably try to combine them into one using the manual edit function - then make that list dynamic.  Using graphical edit you can only have one UNION (OR) but if you use manual edit you can have multiple without having to reference other lists.

    For example, lets say I wanted a list of constituents where:

    Constituency = 1 OR Membership Current in (x,y,z) OR Contribution Date >= 6/30/2011

    I would select the criteria of each one and then copy and paste the code into a new list - making sure to put a "UNION" between each bit of code:

    Select Distinct a.customer_no
     From t_customer a (NOLOCK)
    JOIN vxs_const_cust e (NOLOCK) ON a.customer_no = e.customer_no
     Where  IsNull(a.inactive, 1) = 1
     AND e.constituency in  (1)

    UNION
    Select Distinct a.customer_no
     From t_customer a (NOLOCK)
    JOIN V_MEMBERSHIP_CURRENT e (NOLOCK) ON a.customer_no = e.customer_no
     Where  IsNull(a.inactive, 1) = 1 
     AND e.memb_level in  ('X','Y','Z')

    UNION

    Select Distinct a.customer_no
     From t_customer a (NOLOCK)
    JOIN vs_contribution e (NOLOCK) ON a.customer_no = e.customer_no
     Where  IsNull(a.inactive, 1) = 1
     AND e.cont_dt >=  '2011/06/30'

    Let me know if that's clear. Would be glad to explain futher.

    Dale

  • Former Member
    Former Member $organization in reply to Dale Aucoin

    Hey Dale!

    A very clear solution to me.  Although it would get you by, doesn't that defeat the purpose of including dynamic lists?

    James

  • $organization
    Ditto what James said.  Dale's solution would work beautifully...except that I'm not a SQL writer, so I need to rely on the dynamic list functionality in situations where my IT department doesn't have the resources to help me out in the necessary timeframe, or where it's such a small project that it doesn't make sense to escalate to them.

    Beth

    * * * * *

    Get ready for an unforgettable encounter 3,000 years in the making. Tutankhamun: The Golden King and the Great Pharaohs opens at the Science Museum of Minnesota on February 18, 2011, bringing priceless artifacts from the tomb of the Boy King to Minnesota for the first time. Don’t miss this opportunity; visit www.smm.org/tut to purchase tickets today.

    Elizabeth A Varro
    Membership Manager
    Science Museum of Minnesota
    (651) 265-9829

    From: "James Boncek" <bounce-jamesboncek9049@tessituranetwork.com>
    To: bvarro@smm.org
    Sent: Tuesday, July 5, 2011 2:57:28 PM
    Subject: Re: [Tessitura Technical Forum] Dynamic List problems

    Hey Dale!

    A very clear solution to me.  Although it would get you by, doesn't that defeat the purpose of including dynamic lists?

    James

    From: Dale Aucoin <bounce-daleaucoin4707@tessituranetwork.com>
    Sent: 7/5/2011 2:40:20 PM

    Beth,

    I don't know exactly what your criteria are for your lists but I would probably try to combine them into one using the manual edit function - then make that list dynamic.  Using graphical edit you can only have one UNION (OR) but if you use manual edit you can have multiple without having to reference other lists.

    For example, lets say I wanted a list of constituents where:

    Constituency = 1 OR Membership Current in (x,y,z) OR Contribution Date >= 6/30/2011

    I would select the criteria of each one and then copy and paste the code into a new list - making sure to put a "UNION" between each bit of code:

    Select Distinct a.customer_no
     From t_customer a (NOLOCK)
    JOIN vxs_const_cust e (NOLOCK) ON a.customer_no = e.customer_no
     Where  IsNull(a.inactive, 1) = 1
     AND e.constituency in  (1)

    UNION
    Select Distinct a.customer_no
     From t_customer a (NOLOCK)
    JOIN V_MEMBERSHIP_CURRENT e (NOLOCK) ON a.customer_no = e.customer_no
     Where  IsNull(a.inactive, 1) = 1 
     AND e.memb_level in  ('X','Y','Z')

    UNION

    Select Distinct a.customer_no
     From t_customer a (NOLOCK)
    JOIN vs_contribution e (NOLOCK) ON a.customer_no = e.customer_no
     Where  IsNull(a.inactive, 1) = 1
     AND e.cont_dt >=  '2011/06/30'

    Let me know if that's clear. Would be glad to explain futher.

    Dale



    --
    View this message online at http://www.tessituranetwork.com/COMMUNITY/forums/p/4809/15758.aspx#15758 or reply to this message
    --
    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical 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!

  • Former Member
    Former Member $organization in reply to Beth Varro

    Even if you did know SQL, the object oriented relationship is the key.  For example:  suppression lists;

    Some day you may need to update your suppression criteria, if you hard code the criteria into all your lists, you'll need to update all your lists VS if you include a dynamic Suppression list, you can update that list and all the rest are set.

    Problem is, that the suppression list doesn't generate automatically whenever is its called from another list == dynamic fail.

  • $organization in reply to Former Member

    I ran into this same problem a couple years ago with regards to keeping current numbers on a suppression list. My solution was to schedule the "Generate a List" utility to run nightly for a few key lists. This way you can also scan your high priority lists (subscribers, VIPs, etc.) and know they are up to date even if you haven't used them in a while.

    ~Dan

  • Former Member
    Former Member $organization in reply to Dan Taraborrelli

    After discussing it, that does sound like the next best solution.  Considering you haven't found any better workaround, I think i'll implement that asap.

    Thanks for the tip.

    James

  • $organization

    I just did a bit of testing and determined this to be a bug in v10.0.  If you have multiple criteria elements in a List, only the first one is being considered for regeneration.  So if you have this as your criteria:

     

    List         In            (1,2)

    List         Does Not Have (3,4)

                    OR

    List         Has         (5,6)

     

    Only Lists 1 and 2 are being considered for regeneration (and then only if they are dynamic). 

    This is already addressed in v11, and any new updates for v10.0 will  include this fix.

    -Ryan

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of James Boncek
    Sent: Tuesday, July 05, 2011 4:33 PM
    To: Ryan Creps
    Subject: Re: [Tessitura Technical Forum] Dynamic List problems

     

    Even if you did know SQL, the object oriented relationship is the key.  For example:  suppression lists;

    Some day you may need to update your suppression criteria, if you hard code the criteria into all your lists, you'll need to update all your lists VS if you include a dynamic Suppression list, you can update that list and all the rest are set.

    Problem is, that the suppression list doesn't generate automatically whenever is its called from another list == dynamic fail.

    From: Beth Varro <bounce-elizabethvarro6946@tessituranetwork.com>
    Sent: 7/5/2011 3:15:05 PM

    Ditto what James said.  Dale's solution would work beautifully...except that I'm not a SQL writer, so I need to rely on the dynamic list functionality in situations where my IT department doesn't have the resources to help me out in the necessary timeframe, or where it's such a small project that it doesn't make sense to escalate to them.

    Beth

    * * * * *

    Get ready for an unforgettable encounter 3,000 years in the making. Tutankhamun: The Golden King and the Great Pharaohs opens at the Science Museum of Minnesota on February 18, 2011, bringing priceless artifacts from the tomb of the Boy King to Minnesota for the first time. Don’t miss this opportunity; visit www.smm.org/tut to purchase tickets today.

    Elizabeth A Varro
    Membership Manager
    Science Museum of Minnesota
    (651) 265-9829

    From: "James Boncek" <bounce-jamesboncek9049@tessituranetwork.com>
    To: bvarro@smm.org
    Sent: Tuesday, July 5, 2011 2:57:28 PM
    Subject: Re: [Tessitura Technical Forum] Dynamic List problems

    Hey Dale!

    A very clear solution to me.  Although it would get you by, doesn't that defeat the purpose of including dynamic lists?

    James

    From: Dale Aucoin <bounce-daleaucoin4707@tessituranetwork.com>
    Sent: 7/5/2011 2:40:20 PM

    Beth,

    I don't know exactly what your criteria are for your lists but I would probably try to combine them into one using the manual edit function - then make that list dynamic.  Using graphical edit you can only have one UNION (OR) but if you use manual edit you can have multiple without having to reference other lists.

    For example, lets say I wanted a list of constituents where:

    Constituency = 1 OR Membership Current in (x,y,z) OR Contribution Date >= 6/30/2011

    I would select the criteria of each one and then copy and paste the code into a new list - making sure to put a "UNION" between each bit of code:

    Select Distinct a.customer_no
     From t_customer a (NOLOCK)
    JOIN vxs_const_cust e (NOLOCK) ON a.customer_no = e.customer_no
     Where  IsNull(a.inactive, 1) = 1
     AND e.constituency in  (1)

    UNION
    Select Distinct a.customer_no
     From t_customer a (NOLOCK)
    JOIN V_MEMBERSHIP_CURRENT e (NOLOCK) ON a.customer_no = e.customer_no
     Where  IsNull(a.inactive, 1) = 1 
     AND e.memb_level in  ('X','Y','Z')

    UNION

    Select Distinct a.customer_no
     From t_customer a (NOLOCK)
    JOIN vs_contribution e (NOLOCK) ON a.customer_no = e.customer_no
     Where  IsNull(a.inactive, 1) = 1
     AND e.cont_dt >=  '2011/06/30'

    Let me know if that's clear. Would be glad to explain futher.

    Dale



    --
    View this message online at http://www.tessituranetwork.com/COMMUNITY/forums/p/4809/15758.aspx#15758 or reply to this message
    --
    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical 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!

     




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical 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!

  • Former Member
    Former Member $organization in reply to Ryan Creps (Past Staff Member)

    Sweet!  Thanks Ryan!!!!

  • $organization in reply to Beth Varro

    to be clear, I wasn't talking about writing SQL, just copying and pasting the code that gets generated automatically when you select criteria in a list.  You just have to put a UNION between the pasted portions.

    Dale

  • $organization
    I'll see James's "Thank you!!!" to Ryan and raise you several exclamation points...but that would look ridiculous, so I'll just say "Ditto James" yet again.  And once I get my already-late presentation done for Tina, I'll look into the union and/or scheduling lists ideas as a work-around.

    I love this network!  Thanks so much, everyone!

    Beth

    * * * * *

    Get ready for an unforgettable encounter 3,000 years in the making. Tutankhamun: The Golden King and the Great Pharaohs opens at the Science Museum of Minnesota on February 18, 2011, bringing priceless artifacts from the tomb of the Boy King to Minnesota for the first time. Don’t miss this opportunity; visit www.smm.org/tut to purchase tickets today.

    Elizabeth A Varro
    Membership Manager
    Science Museum of Minnesota
    (651) 265-9829

    From: "James Boncek" <bounce-jamesboncek9049@tessituranetwork.com>
    To: bvarro@smm.org
    Sent: Tuesday, July 5, 2011 4:42:39 PM
    Subject: RE: [Tessitura Technical Forum] Dynamic List problems

    Sweet!  Thanks Ryan!!!!

    From: Ryan Creps <bounce-ryancreps9649@tessituranetwork.com>
    Sent: 7/5/2011 4:35:06 PM

    I just did a bit of testing and determined this to be a bug in v10.0.  If you have multiple criteria elements in a List, only the first one is being considered for regeneration.  So if you have this as your criteria:

     

    List         In            (1,2)

    List         Does Not Have (3,4)

                    OR

    List         Has         (5,6)

     

    Only Lists 1 and 2 are being considered for regeneration (and then only if they are dynamic). 

    This is already addressed in v11, and any new updates for v10.0 will  include this fix.

    -Ryan

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of James Boncek
    Sent: Tuesday, July 05, 2011 4:33 PM
    To: Ryan Creps
    Subject: Re: [Tessitura Technical Forum] Dynamic List problems

     

    Even if you did know SQL, the object oriented relationship is the key.  For example:  suppression lists;

    Some day you may need to update your suppression criteria, if you hard code the criteria into all your lists, you'll need to update all your lists VS if you include a dynamic Suppression list, you can update that list and all the rest are set.

    Problem is, that the suppression list doesn't generate automatically whenever is its called from another list == dynamic fail.

    From: Beth Varro <bounce-elizabethvarro6946@tessituranetwork.com>
    Sent: 7/5/2011 3:15:05 PM

    Ditto what James said.  Dale's solution would work beautifully...except that I'm not a SQL writer, so I need to rely on the dynamic list functionality in situations where my IT department doesn't have the resources to help me out in the necessary timeframe, or where it's such a small project that it doesn't make sense to escalate to them.

    Beth

    * * * * *

    Get ready for an unforgettable encounter 3,000 years in the making. Tutankhamun: The Golden King and the Great Pharaohs opens at the Science Museum of Minnesota on February 18, 2011, bringing priceless artifacts from the tomb of the Boy King to Minnesota for the first time. Don’t miss this opportunity; visit www.smm.org/tut to purchase tickets today.

    Elizabeth A Varro
    Membership Manager
    Science Museum of Minnesota
    (651) 265-9829

    From: "James Boncek" <bounce-jamesboncek9049@tessituranetwork.com>
    To: bvarro@smm.org
    Sent: Tuesday, July 5, 2011 2:57:28 PM
    Subject: Re: [Tessitura Technical Forum] Dynamic List problems

    Hey Dale!

    A very clear solution to me.  Although it would get you by, doesn't that defeat the purpose of including dynamic lists?

    James

    From: Dale Aucoin <bounce-daleaucoin4707@tessituranetwork.com>
    Sent: 7/5/2011 2:40:20 PM

    Beth,

    I don't know exactly what your criteria are for your lists but I would probably try to combine them into one using the manual edit function - then make that list dynamic.  Using graphical edit you can only have one UNION (OR) but if you use manual edit you can have multiple without having to reference other lists.

    For example, lets say I wanted a list of constituents where:

    Constituency = 1 OR Membership Current in (x,y,z) OR Contribution Date >= 6/30/2011

    I would select the criteria of each one and then copy and paste the code into a new list - making sure to put a "UNION" between each bit of code:

    Select Distinct a.customer_no
     From t_customer a (NOLOCK)
    JOIN vxs_const_cust e (NOLOCK) ON a.customer_no = e.customer_no
     Where  IsNull(a.inactive, 1) = 1
     AND e.constituency in  (1)

    UNION
    Select Distinct a.customer_no
     From t_customer a (NOLOCK)
    JOIN V_MEMBERSHIP_CURRENT e (NOLOCK) ON a.customer_no = e.customer_no
     Where  IsNull(a.inactive, 1) = 1 
     AND e.memb_level in  ('X','Y','Z')

    UNION

    Select Distinct a.customer_no
     From t_customer a (NOLOCK)
    JOIN vs_contribution e (NOLOCK) ON a.customer_no = e.customer_no
     Where  IsNull(a.inactive, 1) = 1
     AND e.cont_dt >=  '2011/06/30'

    Let me know if that's clear. Would be glad to explain futher.

    Dale



    --
    View this message online at http://www.tessituranetwork.com/COMMUNITY/forums/p/4809/15758.aspx#15758 or reply to this message
    --
    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical 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!

     




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical 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!



    --
    View this message online at http://www.tessituranetwork.com/COMMUNITY/forums/p/4809/15766.aspx#15766 or reply to this message
    --
    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical 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 Ryan Creps (Past Staff Member)

    So glad that is being resolved in V11 will make lists for the scheduled emails in WordFly easier to do for end users.

    Mark