• Replies 4 replies
  • Subscribers 72 subscribers
  • Views 90 views
  • Users 0 members are here
Related

Local Stored Procedure for Custom Form Data

Debra Lemak
$organization

Is anyone writing custom form data to Attributes? If so, would you be willing to share your sproc? I'd also love to take a look at any other sprocs that people are using to work with form data. I'm just diving into this to see what solutions we have vs getting that large block of text stored to a CSI. Thanks! 

  • $organization

    Following! Debra, did you go to the webinar a few weeks ago on creating a stored procedure? I was having technical difficulties that day and had to just give up on getting in, but I do have the presentation saved and would be happy to share it with you. 

    In the meantime I would love to know what you find out from other orgs. 

  • $organization in reply to Katie J. Toomey

    I must've missed that webinar, and all I can find now is a recorded series from 2016. I'm going to refresh myself on that series since I haven't watched it in awhile. Would love if you could share the presentation doc you have though - dlemak@wexarts.org 

  • $organization in reply to Debra Lemak

    Stored Procedures_Presentation_12 October 2023.pdf 
     Nevermind. Now that you've reminded me, I was able to find a copy of it. 

  • $organization

    Thanks to Thanos at the National Theatre and some trial and error, I was able to get the base piece that I needed to start to pick apart the CSI notes into usable data. For now we're not going to use a stored procedure to interrupt the custom form writing to a CSI, we're just going to pick apart the data written into the CSI and report on it. 

    Here is an example: 

    SELECT activity_no, customer_no, perf_no,  notes,
    SUBSTRING(
        notes,
        (PATINDEX('%Event_Selection:%', notes) +16), 
        (CHARINDEX(CHAR(13), SUBSTRING(notes, (PATINDEX('%Event_Selection:%', notes) + 16), LEN(notes))) - 1)
		) AS Event_Selection, 
	SUBSTRING(
        notes,
        (PATINDEX('%Order Number:%', notes) +13), 
        (CHARINDEX(CHAR(13), SUBSTRING(notes, (PATINDEX('%Order Number:%', notes) + 13), LEN(notes))) - 1)
		) AS order_no
FROM T_CUST_ACTIVITY
WHERE notes LIKE '%Event_Selection%'

    The substring function will pull out the portion of text that follows your field name and ends at the next carriage return, so it should pull in the full answer provided by your constituent. In the example, our field name is Event_Selection, the +16 moves along the 16 characters in Event_Selection: and then starts pulling the text that follows that. 

    Happy to brainstorm with other folks if you're working on something similar!