sheltermanager

Open full view…

Report on Forms

pawsdarwin
Tue, 30 Jul 2019 13:20:38 GMT

Is there a way to fun a report on forms that have come in over a specified time period?

KarenW
Tue, 30 Jul 2019 13:41:26 GMT

When we receive an online form, we attach the form to both the person and animal record if specified. We then pull the online forms attached using the media table, looking for the specific online form titles in the medianotes field. Not pretty, but it works for us. Here is our SQL: SELECT CASE WHEN linktypeid = 0 THEN (SELECT trim(animalname) || ' (' || sheltercode || ')' FROM animal WHERE animal.id = media.linkid) WHEN linktypeid = 3 THEN (SELECT ownername FROM owner WHERE owner.id = media.linkid) END AS animalowner, medianotes, media.date FROM media WHERE date_trunc('day', media.date) BETWEEN '$ASK DATE Enter from date$' AND '$ASK DATE Enter to date$' AND medianotes IN ('Cat Adoption Application', 'Dog Adoption Application','Getting Help - Cat', 'Getting Help - Dog','SNIP Application','Foster Application','Volunteer Application') ORDER BY date HTML: $$HEADER <table border="1"> <tr> <th>NAME</th> <th>TYPE</th> <th>DATE</th> </tr> HEADER$$ $$BODY <tr> <td>$animalowner</td> <td>$MEDIANOTES</td> <td>$date</td> </tr> BODY$$ $$FOOTER </table> FOOTER$$

pawsdarwin
Tue, 30 Jul 2019 23:40:31 GMT

Fantastic. Thanks :)

robinrt
Wed, 31 Jul 2019 06:42:53 GMT

There's also a report in the repository that does this called "View forms/documents attached between two dates".