Populating a list from the databse

It can use custom SQL in the List Criteria definition (overriding the criteria elements), but you cannot have a dynamic list that calls a stored procedure.

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Levi Sauerbrei
Sent: Tuesday, September 14, 2010 10:36 AM
To: Ryan Creps
Subject: [Tessitura Technical Forum] Populating a list from the databse

I haven't tried this yet, but I'm wondering if a list can be dynamic if it uses custom SQL?  Or, working from another angle, has any one used a stored procedure to populate t_list_contents?

I'm going to be adding a constituency to records based on somewhat complex criteria that would be hard to construct in List Builder.  But I would love to use the Manage Constituency report to do the actual constituency part.  Is there a straightforward way to do this?

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!

Hi Levi,

I have a report that creates a list and CSI at the same time, I think you could hack the list bit, schedule the SP and then schedule the Manage Constituency report. Would that work?

I haven't tried this yet, but I'm wondering if a list can be dynamic if it uses custom SQL?  Or, working from another angle, has any one used a stored procedure to populate t_list_contents?

I'm going to be adding a constituency to records based on somewhat complex criteria that would be hard to construct in List Builder.  But I would love to use the Manage Constituency report to do the actual constituency part.  Is there a straightforward way to do this?

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!

Have you tried copying the SQL code you are using into the Manual Edit window for a criteria set?  If the criteria set will accept it, you can then make the list dynamic.  I believe the only restriction is that the only data returned by the code is constituent ID, but it’s possible that if your code is really complex it might not work.  It’s worth a shot, though.

Kevin Sheehan

Documentation & Learning Resources Specialist

Tessitura Network

1 888 643 5778 ext 329 Office

ksheehan@tessituranetwork.com

Just uploaded the SP to my profile, if you want to other bits let me know.

Thank you all for the ideas!

Kevin, I haven't tried pasting my code into the Manual Edit window yet (just writing the code now). I think it will end up being complicated enough that Dale's local table would be preferable.

But since I'm always a fan of re-using code....  Gary, if you could post/send your report (or at least the SP for it) that would be very helpful!  Sounds like just what I need. (CSI's are going to be on next month's menu I suspect.)

could you not use your procedure to create a local view or table then slip some easy custom code into the list so it pulls constituent ids from that table?

select distinct customer_no from lv_stuff

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!