Custom Sheet Reportability

[Joel344]

Hello gurus,

I created a custom sheet where I would like reportability.

There is a radio button group "PCP," which I believe is set-up properly. This refers to, "Have you visited your PCP (Primary Care Provider) during the past six months?"

When I query the sheetfield table, I would like to be able to export the names of those patients who answered "yes" and those patient's names who answered "no." I do not see where "yes" and "no" are indicated within the table. What am I missing?

Here is the table:




Regards,


Joel

[Joel344]

Answering my own question, the ReportableName needs to be different for each radio button within the RadioButtonGroup. In my example, I indicated the "yes" on the fill in form to indicate, "Has seen the PCP," and "no" to indicate "Has not seen the PCP." Next, the FieldValue field contains an "x" suggesting this patient "Has seen the PCP."

So far, so good.

Now how can I write a query that will generate the name, address, city, state, and zip for those people who did not see the PCP within 6 months?

Regards,


Joel

[Joel344]

Here is the query from http://www.opendental.com/manual/sheetsinputfield.html

I added a few more fields from patient.


/*Misc sheet field report for sheets created in date range*/
SET @SheetName='Keystone Beyond Your Smile'; /*Enter description of sheet here (sheet name)*/
SET @ReportableName='Has seen the PCP'; /*Enter the reportable name of the misc field here (from sheet field)*/
SET @FromDate='2016-01-01', @ToDate='2016-12-31'; /*Set sheet date range here*/
SELECT p.PatNum AS 'Pat#', CONCAT(p.LName,', ',p.FName) AS 'Patient Name', Gender, BirthDate, SSN, Address, Address2, City, State, Zip, HmPhone, WirelessPhone, Email, DateFirstVisit, sheet.DateTimeSheet, sheet.Description AS 'Sheet Name',
sheetfield.ReportableName AS 'FieldReportableName', sheetfield.FieldValue AS 'FieldReportableValue'
FROM sheet
INNER JOIN patient p ON p.PatNum=sheet.PatNum
INNER JOIN sheetfield ON sheetfield.SheetNum=sheet.SheetNum
WHERE sheet.Description LIKE @SheetName
AND sheetfield.ReportableName LIKE @ReportableName
AND DATE(sheet.DateTimeSheet) BETWEEN @FromDate AND @ToDate;

Problem solved.

Thanks.


Regards,


Joel

[jsalmon]

Asks a question, figures out the answer himself AND has the determination to post the answer for others to learn.