You have a significant problem with your design:
Then I have ten fields on my PATIENT MODALITIES layout which contain these values in the each of the ten fields, per patient modality record....
With 10 different fields in every patient record with any one of the 10 storaing any one of the possible values from your value list, any attempts to count values becomes very cumbersome and you have imposed an artificial limit of 10 such values for any given patient--a limit you need not have.
Replace these 10 fields with one related table, call it modalities and relate it to your patient table by PatientID.
You can now place a portal to Modalities on your layout in place of those 10 fields and format the single field in this portal row with your value list of modalities. This portal can have 10 portal rows, but with a scroll bar, you are not limited to 10 nor need use up space for 10 rows if you don't want to. You also can use multiple portals to modalities to arrange these in columns or a grid if you need to.
Now you can set up a report layout based on the Modalities table and Sorting with SubSummary layout parts and summary fields can work to produce a count either for one patient or a group of patients.
Hi! Being a new Filemaker developer I am very thankful for all the help I have received here. Even though I wasn’t able to meet my deadline I successfully remodeled the database according to your suggestion, wherein each treatment item (Rx Item) is now called from a set of related records via a Parent-child relationship.
Default Rx_PK :: Default Rx_FK
It also works perfectly but I have a few concerns regarding the new model.
1) How will be able to show all RX items in table view in the NEW MODEL as illustrated in the diagrams below?
2) As my main concern is to do reports in the end, it’s probably good to use your suggested model- but what is the fastest way to generate reports via IWP? Is there an easy step to summarize reports in this way?
Name of Resident: Saul Hudson
Treatments Attended: MDS Minutes Total
HEAT Therapy 55
Walking Therapy 66
Group Therapy 55
Name of Therapist: Kevin Galang
Treatments Given: MDS Minutes Total
HEAT Therapy 85
Walking Therapy 76
Group Therapy 85
Or even present these in charts? I appreciate all the help in advance as I have been constructing this medical system for almost 8 months now. I hope I can come up with a solid solution soon.
heres image one original size
here's image two
heres image 2
My first advice is not to use table view. Table view is useful for you as the developer to get a quick and flexible over view of the data, but casual users can get into trouble with some details of the layout and it also limits you in key ways that don't exist in list view.
First, your Sample report when NOT using IWP. (Reports are very limited in IWP, you may not be able to get all that you want when using a web browser to access yourdatabase.)
Set up a list view layout based on the Modalities table. Add a Sub Summary layout part "when sorted by" PatientID. Put fields, such as the patient name in the sub summary layout. Remove the Body layout part and replace it with a second sub summary part when sorted by Modality.
Put the modality field in this layout part. You can define a summary field that computes the "count of" the modality field and include it in this same sub summary part to show the sub total. If you put this same field in the first sub summary layout part, you get a count of total modality records for that patient. If you put it in the header, footer or a grand summary layout part, the same field gives you the total records in your report.
Now perform a find for the records that you want in your report, it can be for one patient, a group of patients or only for specified modalities and a range of treatment dates can also be specified.
Once you perform the find, sort your records, first by Patient, then by Modality.
The catch for IWP is that sub summary layout parts are not supported in that interface. There are ways to approximate it starting from a table where you have one record for each row in the above report, but it is not a simple thing to set up nor nearly as flexible as the sub summary report.
Back to the table view issue in your last screen shot. Your layout is not limited to fields from the RX table. You can also include fields from the Patients table. If you use an option in layout setup to make the header visible, you can include fields from Patients in the header and then perform a find to limit the modality records to only those related to a specified patient.
Even better, make this a list view layout based on modalities and you can include fields from the patients table in the header, or if you want to see data on multiple patients, in a sub summary part when sorted by patientID. (But not in IWP.)
Thank you for your reply-- the thing is we are deploying this EMR on IWP, we already purchased a server and Filemaker Server Advanced, so NOT going IWP is NOT an option :(
should I just go back to the old layout, as shown in photos one and two? According to you it is possible to count the occurences of each treatment and compile them in a report, but it will be cumbersome. If it possible, I might as well experiment on that route due to time constaints and IWP limitations that you have mentioned. What are the steps if I wanted to count occurences in dropdown lists as shown on the first diagaram?
Given all these, what are my options? Once again THANK YOU VERY MUCH!!
No do not go back to the original structure. That one's a nightmare for any kind of reporting of modality statisticts.
You'll need to carefull think through your options. If these reports can be prepared in advance and then downloaded, a script might be able to generate them as PDF's that can then inserted into container fields or viewed in web browsers.
If you must be able to set up a dynamic report where the user specifies criteria and then they get the report in their web browser, you are in for quite a bit of scripting to get the same totals and subtotals to appear in the browser.
It would require adding one new record to a report table each time a patient receives a treatment modality for the first time. The records in this related table (and this is in addition to the modalities table), would have these two data fields:
From that table, I'll call it Patient_Modality, you can set up a relationship to Modalities like this:
Patient_Modality::PatientID = Modalities::PatientID AND
Patient_Modality::Modality = Modalities::Modality.
With a layout based on this table, you can place the summary field from Modality in the body of the layout and you will see the needed sub totals for each modality. And it is possible to filter the results by a date or range of dates by including global date fields defined in Patient_Modality in the above relationship.
You'll need to set up scripting that updates the Patient_Modality table as needed each time you submit new treatment data on a given patient.
hi.. can i use other simpler routes for iwp? maybe count themusing summary and count fileds and totals instead?
How is that really simpler?
You will still end up creating one record for each patient-modality combination. It eliminates the need for linking it in a relationship to Modalities--so that part is simpler, but you pay for it with more complex scripts and keep in mind that these scripts have to correctly update such counts when you: A) add a new modality entry. B) Remove a modality entry entered/selected by mistake and C) Change a modality entry from one Modality to another. And you can't use a script trigger to perform the script to update your counts, you have to use a button to perform it.
wll this script be good in creating the rx records? i am using a similar one in another area of the database- i just need to find out if it will copy the contents of a portal field and set it to a new report... Rx_Items::Rx_Items_PK = DailyCompleted_Rx::Rx_Items_FK . Go to layout (Rx items) #add script steps here to perform a find to pull up the desired set of Rx items. (sort by PK number add script button to Default Rx Plan of patient and sort for modalities of that patient) Go to Record/request/page [first] Loop Set variable [$Rx_Items ; value: Rx_Items::Rx_Items_PK] Go to Layout [DailyCompleted_Rx] New Record/Request Set field [DailyCompleted_Rx::Rx_Items_FK; $Rx_Items] Go to Layout [Rx_Items] Go to Record/request/page [next; exit after last] End Loop
Posting from an iPad by anychance? Seems like posts from one of those always mash the text together.
pasting and reformatting so that I can read it:
Go to layout (Rx items)
#add script steps here to perform a find to pull up the desired set of Rx items.
(sort by PK number add script button to Default Rx Plan of patient and sort for modalities of that patient)
Go to Record/request/page [first]
Set variable [$Rx_Items ; value: Rx_Items::Rx_Items_PK]
Go to Layout [DailyCompleted_Rx]
Set field [DailyCompleted_Rx::Rx_Items_FK; $Rx_Items]
Go to Layout [Rx_Items]
Go to Record/request/page [next; exit after last]
This script can create records for the same patient with the same modality as previosly created records. It essentially copies the records already created during data entry. The script needs to create a new record for a specific comtination of PatientID and Modality only if that pairing of values does not already exist in the table. This is so you can produce the one row for each modality format you get with the sub summary layout parts in the summary report we are trying to reproduce in IWP.
I'd set up this relationship:
Rx_Items::PatientID_FK = DailyCompleted_Rx::PatientID_FK AND
Rx_Items::Modality = DailyCompleted_Rx::Modality
Enable "Allow creation of records via this relationship" for DailyCompleted_RX
Then use this loop for creating records in DailyCompleted_Rx, but only if one with the same two values does not already exist:
Set field [DailyCompleted_Rx::PatientID_FK; Rx_Items::PatientID_fk]
Go to Record/request/page [next; exit after last]
Note that you stay on the layout for Rx_Items.
BTW, I realized part way through that I am using names from your sample script set to work with other tables and mixing in details from the Modalities table. You'll need to subsitute some names here to fit your actual tables.