Posted By: martin68 — 12 Aug 2016 at 7:33pm
Hi all!
I work for a pharmacy and our state board is asking for a daily report that shows all prescriptions dispensed today and, in addition, if the patient received their prescription today they also want to see all life to date transactions for that patient.
So, if nothing is dispensed today, the report is blank. If we do dispense to {dispense.patient} today, it needs to show all previous transactions (in some cases going back years).
The trigger is the dispense date {dispense.date}. However, any filter for {dispense.date} seems to limit my prescriptions {dispense.rx#} to only that date. Banging my head on the wall, so appreciate any ideas you may have.
Thank you!
Martin
Posted By: hilfy — 16 Aug 2016 at 5:30am
How are your SQL skills? You'll need to use a command or a database view for this. A command is just a SQL Select statement that you create instead of having Crystal create the SQL for you based on the tables and fields that you've added to the report. I have a blog post about how to use commands here: http://bit.ly/1LcUc1h
The basic logic of the command will be something like this:
Select
<ALL of the fields for your report!>
from <joined tables for your report>
where dispense.patient in (
select disp2.patient
from dispense as disp2
where disp2.date = <current date>)
What type of database are you using? If you're not sure how to write the query, you can post the SQL that Crystal has generated for you and I will write it for you.
-Dell
