Pulling Constituency withe End Date

Matthew,

 

Thank you for this information. It looks like the criteria “Constituency Start Date” and “Constituency End Date” may in face be custom elements within my database. I am still waiting on an reply from my internal DBA on this subject. This was helpful.

 

Thanks,

 

 

Hi Marie,

I ran into a similar issue once. It was in v10 and it certainly could have changed in v11, so if I'm wrong hopefully somebody will correct me.

I believe that when you use the criteria "Constituent IN (whatever)" you're actually pulling from a database view that is already filtering to include only accounts that currently have that constituency. If I pull a list with just "Constituency IN Advisory Board" e.g., and don't specify start/end dates, I get a list of only the current advisory board. (In fact, I don't seem to have "Constituency End Date" in my List Builder, so that may be custom?)

If the Constituency criteria is pulling from the view vxs_cust_cont, I think you might need to manually edit the query to pull straight from the actual table, tx_const_cust instead, and then you could use start/end dates. (But if you're using custom list criteria already maybe you already have a workaround for this.) I'm definitely not a DBA so maybe somebody with a little more knowledge of the v11 backend can chime in with more useful advice. :)

Good luck!

Thank you Beth,

 

I did try different operators for my date “range” and received the same results. I believe my criteria are custom and am waiting for a reply from my internal DBA. Thank you for the suggestion. It was good for me to try.

 

 

Hi Marie,

To expand more on Terry's suggestion, I don't use any date field with the = operator if I can help it. :)

In Tess most dates have a hidden time attached to them at the end. So by searching for date = (some day) you may not get the right results. I would suggest doing a search using Constituency End Date BETWEEN '5/15/13/ and '5/17/13' to see if that gets the results you are looking for.

Hope that helps!

Yes, I did try different operators. Thanks for the suggestion. I think it is something on my end “behind the scenes.”

 

 

Are you sure your constituency end date for all the records you are looking for is 5/16/13?  Maybe you want to use BETWEEN or GREATER THAN rather than equals.

Beth – You and I are on the same track. I had it on my list of suggestions to talk to my DBA.

 

Thanks,

 

 

Hi Marie!

You can see where your list criteria is pulling it's data from. When you are creating a list, right-click on the criteria element (say, "Constituency") and you'll see the "Detail Table" that it is pulling from.

If the Detail Table is "vxs_const_cust" - it will NOT pull your inactive constituencies - even if it is a constituency in the future! This could be the root of your trouble.

It's my recollection that when we started with Tessitura, we only had one criteria element that referred to this table and only pulled current constituencies. I added three new elements -- Constituency(Archive),Constituency(Archive) Start Dt, Constituency(Archive) End Dt --  so I could pull ANY constituency, whether expired or not. It will probably require the assistance of your DBA and a new secure view -- but it would keep you from doing a lot of list criteria gymnastics to get what you're after.

Help on adding criteria can be found here: http://www.tessituranetwork.com/Help_System/Content/System_Tables/T_KEYWORD.htm

Beth

Hello Everyone

 

I was able to figure it out.

 

Here’s the situation: I was looking up a constituency for our Ballet School Students. I was attempting to pull our students last year (st dt: whenever / end dt: 5/16/2013) AND a separate list of our current students this year (st dt: 9/3/2013 / end dt: 5/13/2014).

 

So in actuality, the information I was trying to pull is “INACTIVE” as of today (8/29/2013). Last year’s constituency is inactive because the end date has past. The current constituency is inactive because the start date has yet to occur.

 

The “Constituency” list criteria element only pulls “active” constituencies. So when I paired it with my dates, the information I was asking for did not exist.

 

We created a “Constituency – ALL” to include active and inactive constituencies. However, to get accurate results this criterion must be paired with “Constituency Start Date” OR “Constituency End Date.” Otherwise, it will pull all records that have a particular constituency – active or inactive.

 

Thanks everyone for your help – much appreciated,

 

 

 

Beth – You and I are on the same track. I had it on my list of suggestions to talk to my DBA.

 

Thanks,

 

 

Hi Marie!

You can see where your list criteria is pulling it's data from. When you are creating a list, right-click on the criteria element (say, "Constituency") and you'll see the "Detail Table" that it is pulling from.

If the Detail Table is "vxs_const_cust" - it will NOT pull your inactive constituencies - even if it is a constituency in the future! This could be the root of your trouble.

It's my recollection that when we started with Tessitura, we only had one criteria element that referred to this table and only pulled current constituencies. I added three new elements -- Constituency(Archive),Constituency(Archive) Start Dt, Constituency(Archive) End Dt --  so I could pull ANY constituency, whether expired or not. It will probably require the assistance of your DBA and a new secure view -- but it would keep you from doing a lot of list criteria gymnastics to get what you're after.

Help on adding criteria can be found here: http://www.tessituranetwork.com/Help_System/Content/System_Tables/T_KEYWORD.htm

Beth

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!