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$$
Fantastic. Thanks :)
There's also a report in the repository that does this called "View forms/documents attached between two dates".