Shelter Manager Reporting

[Helpmeplease86]

Hi - I need a report that shows medical information of the animal, but also the address not just name of the current foster person, the build query doesn't allow those two to 'mix' and was wondering if there was another way? I basically need the medical "due" of the current fosters but also the address to send the medication to without having to cross reference two spreadsheets.

[Tetzor]

Hi,

The following SQL and HTML should do what you need if you copy them into a new report.

$VAR from DATE Enter from date$
$VAR to DATE Enter to date$

SELECT animal.AnimalName, animal.ShelterCode, animal.ID as AID, owner.OwnerName, Owner.ID AS OID, owner.OwnerAddress, owner.OwnerPostcode,
owner.OwnerTown, owner.OwnerCounty,owner.OwnerPostcode, 
owner.EmailAddress,
animalmedicaltreatment.DateRequired, animalmedical.TreatmentName AS MedicalType
FROM animal
INNER JOIN adoption ON adoption.ID = animal.ActiveMovementID  
INNER JOIN owner ON adoption.OwnerID = owner.ID
INNER JOIN animalmedical ON animalmedical.AnimalID = animal.ID 
INNER JOIN animalmedicaltreatment ON animalmedicaltreatment.AnimalMedicalID = animalmedical.ID 
WHERE animal.ActiveMovementType = 2 
AND animal.DeceasedDate Is Null
AND animalmedicaltreatment.DateGiven Is Null 
AND animalmedicaltreatment.DateRequired >= '$@from$'
AND animalmedicaltreatment.DateRequired <= '$@to$'
ORDER BY AnimalName;

$$HEADER
<table border="1">
<tr>
<th>Animal Name</th>
<th>Shelter Code</th>
<th>Owner Name And Address</th>
<th>Email</th>
<th>Date Required</th>
<th>Medical Type</th>
</tr>
HEADER$$

$$BODY
<tr>
<td><a target="_blank" href="animal?id=$AID">$ANIMALNAME</a></td>
<td>$ShelterCode</td>
<td><i><a target="_blank" href="person?id=$OID">$OWNERNAME</a></i><br/>
$OwnerAddress<br/>
$OwnerTown<br/>
$OwnerCounty<br/>
$OwnerPostcode</td>
<td>$EmailAddress</td>
<td>$DateRequired</td>
<td>$MedicalType</td>
</tr>
BODY$$

$$FOOTER
</table>
<p>Total: {COUNT.ANIMALNAME}</p>
FOOTER$$

Kind regards,

Jon

[Helpmeplease86]

Thank you that’s soooo great. I also forgot I need the dogs weight in it?:/Could you help add that too?SarahSent from my iPhoneOn 10 Jan 2024, at 2:58 am, Jon ***@***.***> wrote: Hi, The following SQL should do what you need if you insert it into a new report: $VAR from DATE Enter from date$ $VAR to DATE Enter to date$ SELECT animal.AnimalName, owner.OwnerName, owner.OwnerAddress, owner.OwnerPostcode, owner.OwnerTown, owner.OwnerCounty,owner.OwnerPostcode, owner.EmailAddress, animalmedicaltreatment.DateRequired, animalmedical.TreatmentName AS MedicalType FROM animal INNER JOIN adoption ON adoption.ID = animal.ActiveMovementID INNER JOIN owner ON adoption.OwnerID = owner.ID INNER JOIN animalmedical ON animalmedical.AnimalID = animal.ID INNER JOIN animalmedicaltreatment ON animalmedicaltreatment.AnimalMedicalID = animalmedical.ID WHERE animal.ActiveMovementType = 2 AND animal.DeceasedDate Is Null AND animalmedicaltreatment.DateGiven Is Null AND animalmedicaltreatment.DateRequired >= ***@***.***$' AND animalmedicaltreatment.DateRequired <= ***@***.***$' ORDER BY AnimalName; If you generate the HTML rather than writing your own, and you want the name and all the address to appear in a single field, replace the HTML for the name and address data with the following. $OwnerName $OwnerAddress $OwnerTown $OwnerCounty $OwnerPostcode Kind regards, Jon —Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you authored the thread.Message ID: ***@***.***>

[Tetzor]

Hi,

I have added weight for you below.

$VAR from DATE Enter from date$
$VAR to DATE Enter to date$

SELECT animal.AnimalName, animal.ShelterCode, animal.Weight, animal.ID as AID, owner.OwnerName, Owner.ID AS OID, owner.OwnerAddress, owner.OwnerPostcode,
owner.OwnerTown, owner.OwnerCounty,owner.OwnerPostcode, 
owner.EmailAddress,
animalmedicaltreatment.DateRequired, animalmedical.TreatmentName AS MedicalType
FROM animal
INNER JOIN adoption ON adoption.ID = animal.ActiveMovementID  
INNER JOIN owner ON adoption.OwnerID = owner.ID
INNER JOIN animalmedical ON animalmedical.AnimalID = animal.ID 
INNER JOIN animalmedicaltreatment ON animalmedicaltreatment.AnimalMedicalID = animalmedical.ID 
WHERE animal.ActiveMovementType = 2 
AND animal.DeceasedDate Is Null
AND animalmedicaltreatment.DateGiven Is Null 
AND animalmedicaltreatment.DateRequired >= '$@from$'
AND animalmedicaltreatment.DateRequired <= '$@to$'
ORDER BY AnimalName;

$$HEADER
<table border="1">
<tr>
<th>Animal Name</th>
<th>Shelter Code</th>
<th>Weight</th>
<th>Owner Name And Address</th>
<th>Email</th>
<th>Date Required</th>
<th>Medical Type</th>
</tr>
HEADER$$

$$BODY
<tr>
<td><a target="_blank" href="animal?id=$AID">$ANIMALNAME</a></td>
<td>$ShelterCode</td>
<td>$Weight lb</td>
<td><i><a target="_blank" href="person?id=$OID">$OWNERNAME</a></i><br/>
$OwnerAddress<br/>
$OwnerTown<br/>
$OwnerCounty<br/>
$OwnerPostcode</td>
<td>$EmailAddress</td>
<td>$DateRequired</td>
<td>$MedicalType</td>
</tr>
BODY$$

$$FOOTER
</table>
<p>Total: {COUNT.ANIMALNAME}</p>
FOOTER$$

Kind regards,

Jon