Hi!
I've had such good luck on the forums...but this one might be a little hard for me to explain, so bear with me. I apologize in advance if this too broad or if I'm not providing enough info to help troubleshoot.
We send a daily email to all patrons who attended the performance the night before (perf_code from a local view which joins T_ORDER_SEAT_HIST and T_SUB_LINEITEM) . These lists are dynamic and are automatically re-generated every day through wordfly (or email application). The problem is, I'm not sure how to identify patrons whose tickets were NOT scanned (assumedly because they did not attend the show). We'd like to send them a different message and give them the opportunity to exchange their tickets into something else.
Do any other organizations ever send messages to patrons who did not have their tickets scanned and therefore, are assumed to have missed a performance?
Any help would be great. Otherwise, I'll have to begin training carrier pigeons. :-3
Thanks,
Tiffany
Unknown said: That looks so nice, but it gave me 0 constituents, though the query was successful.
That looks so nice, but it gave me 0 constituents, though the query was successful.
Yeah, the code has a bug I wrote in haste. Where it has
perf_dt = perf_dt - 1
Try this:
perf_dt = REPLACE(CONVERT(VARCHAR(10), GETDATE() - 1, 111), '/', '-') + ' ' + convert(varchar,DATEPART(HH, GETDATE() - 1)) + ':' + convert(varchar,DATEPART(MI, GETDATE() - 1))
We use a dynamic list with the list criteria pieces Performance Date <= RunDate -1 and Performance Date >= RunDate -1 since Performance Date = RunDate-1 tends to be iffy. This solves the issue of having to change the performance number each day in order to send out the triggered Wordfly email to people who attended the previous day. Pairing this with Performance Name Like has always done the trick (so we don't send to patrons with other events like rentals). Though we've not gone the extra step to separate out those who Attended from those who have not.