Report for clients choosing email reminders
Submitted by lakesidevet on Tue, 04/02/2014 - 14:49
Is there a report I can run to get a list of all those clients who have elected to receive their reminders via email? Currently we do this by selecting "reminder" from the purpose list when editing clients contacts - email.
Re: Report for clients choosing email reminders
I don't think there's any report at present that will do this.
The following SQL should extract the required information. It just needs to be put into a Jasper Report:
select title.name as title, firstName.value as firstName, lastName.value as lastName, cd.value as emailAddress from entities e join contacts c on c.contact_id = e.entity_id and c.arch_short_name = "contact.email" join contact_details cd on c.contact_id = cd.contact_id and c.arch_short_name = "contact.email" and cd.name = "emailAddress" left join entity_details firstName on firstName.entity_id = e.entity_id and firstName.name = "firstName" left join entity_details lastName on lastName.entity_id = e.entity_id and lastName.name = "lastName" left join entity_details titleCode on titleCode.entity_id = e.entity_id and titleCode.name = "title" left join lookups title on titleCode.value = title.code and title.arch_short_name = "lookup.personTitle" join contact_classifications cc on c.contact_id = cc.contact_id join lookups l on cc.lookup_id = l.lookup_id and l.arch_short_name = "lookup.contactPurpose" and l.code = "REMINDER" where e.active = true and e.arch_short_name = "party.customerperson"
-Tim