I'm working on linking Tessitura profiles with an outside database system. The system uses their own constituent IDs in URLs, which is fantastic because they're all unique. However, it's not so fantastic because they're completely different from our Tessitura IDs.
What I'd like to do is create a browser-based T_CONSTITUENT_REPORT row that includes the outside database's ID in the dynamic URL. Something like:
D Object: http://www.databasething.com/seattlesymphony?profile=<<outside_key>>
Anyone know if there's any way of doing that, or something like it? Is the only alternative to do an SSRS report that then links to the ResearchPoint profile? Trying to make this simple for the end user, so advice is appreciated...thanks!
Andrew
Hi Andrew,Have you taken a look at the stored procedure LP_PREPROCESS_URL? Report and custom screen URLs are passed to this proc before rendering the object, perhaps this can help? Cheers,Patrick.From: Andrew Smith <bounce-andrewsmith9816@tessituranetwork.com>Sent: 6/26/2014 4:52:14 PMI'm working on linking Tessitura profiles with an outside database system. The system uses their own constituent IDs in URLs, which is fantastic because they're all unique. However, it's not so fantastic because they're completely different from our Tessitura IDs.What I'd like to do is create a browser-based T_CONSTITUENT_REPORT row that includes the outside database's ID in the dynamic URL. Something like:D Object: http://www.databasething.com/seattlesymphony?profile=<<outside_key>>Anyone know if there's any way of doing that, or something like it? Is the only alternative to do an SSRS report that then links to the ResearchPoint profile? Trying to make this simple for the end user, so advice is appreciated...thanks!Andrew 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 Andrew,
Have you taken a look at the stored procedure LP_PREPROCESS_URL?
Report and custom screen URLs are passed to this proc before rendering the object, perhaps this can help?
Cheers,
Patrick.
From: Andrew Smith <bounce-andrewsmith9816@tessituranetwork.com>Sent: 6/26/2014 4:52:14 PM
Totally works! Thanks for the suggestion everyone. For posterity, here's the extremely hacky solution I came up with as an addition to LP_PREPROCESS_URL
IF NOT(CHARINDEX('blackbaudhosting', @url)) = 0 -- If 'blackbaudhosting' is in the URL BEGIN Declare @pattern as varchar(10) = 'recordId=' -- Pattern that precedes <> Declare @ind as bigint = len(@url) - CHARINDEX(@pattern, @url) - len(@pattern) + 1 -- Find the index Declare @customer_no as int = RIGHT(@url, @ind) -- Get the <> from the URL Declare @rp_id as varchar(36) = (SELECT r.research_source FROM T_CUST_RESEARCH r WHERE r.customer_no = @customer_no AND r.research_type = 2 ) SELECT (LEFT(@url, (len(@url) - @ind)) + @rp_id) -- Replace the <> with the RP id END