RSVP program

Hi Everyone

Please go through this following link first and if that doesn't make sense then let us know here we will be happy to help you guys.

https://support.wordfly.com/hc/en-us/articles/204768664-How-can-I-set-up-an-RSVP-email-Tessitura-integration-

Regards

Jatin

Hi all,

Sorry for the delay—am replying from email so hopefully this formats correctly! Please note that neither of these solutions will add notes/guests/etc—they just update the status of an activity record in an account (or create one with the correct status if none exists).

What I did originally-- and this is probably the simplest if you a) want it to work today, or b) only use the functionality occasionally, or c) will not recycle links-- was simply insert the following into our LP_UPDATE_EMAIL_PROMOTIONS procedure which updates the activity records, using the urls linked by buttons in a WordFly email:

if @response_code = 4 and @url is not null

begin

    if @url = 'https://www.soulpepper.ca/rsvp/development/CCrsvp2'

              begin

            If Exists

                     (

                     Select * from T_SPECIAL_ACTIVITY

                     where

                           customer_no = @customer_no

                           and sp_act = '260'                                                                       --activity id

                           and sp_act_dt = '2017-05-16 00:00:00.000'                                  --date of activity to be updated

                     )

                           begin

                                  Update T_SPECIAL_ACTIVITY

                                  Set   

                                  status = 4,

                                  num_attendees = 2                                                                  -- accepted & 2

                                  Where 

                                  customer_no = @customer_no

                                  and sp_act = '260'                                                                 --activity id

                                  and sp_act_dt = '2017-05-16 00:00:00.000'                            --date of activity to be updated

                           end

                     Else

                           Insert t_special_activity (customer_no, sp_act, sp_act_dt, perf, status, num_attendees)

                           Select @customer_no, '260', '2017-05-16 00:00:00.000', 'for colored girls', 4, 2      --activity, date, title, status, # attendees

              End

End

(repeating for each unique url)

Buuuut…. because we have TONS of urls, and we recycle them and I want this to be hands-off for me, as the DBA, I have since created a (only slightly) more involved solution. I created a local table (ltr_email_promo_actvity) that I then made into a system table that contains rows for each unique landing page we create, with drop-down editable fields for activity type,  status, performance, etc. This way, I can update the procedure once, and leave the on/off/switch info for the individual urls up to the departmental power users. Then, I edited the insert in the LP_UPDATE_EMAIL_PROMOTIONS procedure to look like this:

if @response_code = 4 and @url is not null

begin

    if @url in (select url from LTR_EMAIL_PROMO_ACTIVITY)

              begin

            If Exists

                     (

                     Select * from T_SPECIAL_ACTIVITY

                     where

                           customer_no = @customer_no

                           and sp_act = (select activity from ltr_email_promo_activity where url=@url) --picks activity type for this url

                           and sp_act_dt = (select act_dt from ltr_email_promo_activity where url=@url)       --picks act date for this url

                     )

                           begin

                                  Update T_SPECIAL_ACTIVITY

                                  Set   

                                  status = (select act_status from LTR_EMAIL_PROMO_ACTIVITY where url=@url), --updates to correct status

                                  num_attendees = (select num_attendees from ltr_email_promo_activity where url=@url)      -- updates to correct number of attendees

                                  Where 

                                  customer_no = @customer_no

                                  and sp_act = (select activity from ltr_email_promo_activity where url=@url)            --updates to activity type for this url

                                  and sp_act_dt = (select act_dt from ltr_email_promo_activity where url=@url)     --updates to act date for this url

                           end

                     Else

                           Insert t_special_activity (customer_no, sp_act, sp_act_dt, perf, status, num_attendees)

                           Select @customer_no, activity, act_dt, act_perf_name, act_status, act_attendees from LTR_EMAIL_PROMO_ACTIVITY where url=@url --creates new activity record if does not exist

              End

End

And here is a screenshot of first couple of rows/columns of the local system table:

Feel free to shoot me an email with any further questions.

Cheers,

Sheila

We are looking into this exact scenario for our upcoming season and their activities.  Would it be possible for you to share your code with us?

Thanks,

Dot

Hey lovely Dot.  We just implemented Wordfly pages-RSVPs with a bit of a tweak to the Sproc by the gifted

I'll can share if you need.

What did ya change, Heath?