I use a Code by ChrisD of OpenDental.
/* List of all patients in 'Patient' status with a specific Guarantor PatNum (99 in example) and email address*/
SET @GuarantorPatNum=99; /* use 0 for patients from any family */
SET @EmailAddress='
service@opendental.com'; /* Leave as empty quotes for any email address, including none. */
/*--------------------DO NOT MODIFY BELOW THIS LINE--------------------*/
/* Query code written/modified 07/22/2024:ChrisD */
SELECT
patient.PatNum
FROM patient
WHERE patient.PatStatus = 0 /* 'Patient' PatStatus*/
/* Remove either of the following lines to search for only one or the other */
AND IF(@GuarantorPatNum, patient.Guarantor = @GuarantorPatNum, TRUE)
AND IF(LENGTH(@EmailAddress), patient.Email = @EmailAddress, TRUE)
;
/* List of all patients in 'Patient' status with the same guarantor as a specific PatNum (99 in example)*/
SET @FamilyMemberPatNum=99;
/*--------------------DO NOT MODIFY BELOW THIS LINE--------------------*/
/* Query code written/modified 07/22/2024:ChrisD */
SELECT
patient.PatNum
FROM patient
WHERE patient.Guarantor IN (
SELECT pat.Guarantor
FROM patient pat
WHERE pat.PatNum = @FamilyMemberPatNum
)
AND patient.PatStatus = 0 /* 'Patient' PatStatus*/
;
Edit 20251018: Open Dental was so friendly to provide this query to me for free because I am posting my workflows in this forum, give proper attribution and because I don't know anything about SQL. If you are commercial you should IMHO pay for the this service or provide some code. Please give something back to this forum.
Joerg