My question is how to find those patients who havenot taken all the three images across multiple visits from the database? I tried a portal of the visits on the patient layout but I can not do a direct search to find those who havenot taken all the three images across their visits. It seems that I need some aggregate function to summarize the data but I did not find an appropriate function to use.
The issue you are facing is that you cannot find what is not there. If a second or third record has not been created it doesn't exist so you can't work with it.
You have to work with what does exist then use logic to make inferences. Here's one way.
Create an unstored calculation field in the patient record which counts images : count(images::_id)
An alternate method would be to use a script to push that value into a number field whenever an image is added or removed.
You now search that field for values less than three.
say your visits table is Visits and patients is Patients
in the Visits table..
assuming the img1, img2, img3 are fields and they have auto enter No. Then you could have a calculation that does something like this...
let call this field "allImages"
image1 = case ( img1 = "Yes" ; 1 ; 0 );
image2 = case ( img2 = "Yes" ; 1 ; 0 );
image3 = case ( img3 = "Yes" ; 1 ; 0 )
so "allImages" would be 1 if its a yes 3x
then in your paitent table have a calc like
Evaluate ( Substitute ( List ( Visits::allImages ) ; "¶" ; '+" ) = ValueCount ( List ( Visits::PrimaryKey ) )
if its true they have all the visits
hope this helps
You can do this with a script.
From the Patients table:
Enter Find Mode
Set Field [ img1 ; "yes" ]
Constrain Found Set [find ; img2 ; "yes" ]
Constrain Found Set [find ; img3 ; "yes" ]
#Now you got all the records with a "yes" in all three fields
Show Omitted Only
There are lots of ways you can do this, which is the beauty of FileMaker.
The quick and dirty method is always a calculation which in return weighs your database down as time goes by.
So the best is to have a field called img_count and add a 1 to it every time a new image gets added via script. Then you can easily search on this by entering <3 when you need to.
For beauty you can also add another field that would display the status:
If ( img_count > 3 ; "unfulfilled"; "fulfilled" )
And then add conditional formatting to show the text in green if it's fulfilled. Or red if it isn't.
1 of 1 people found this helpful
Sounds to me like you should further normalize the solution by having a separate table for Images. That table would have fields for:
- A unique ImageID primary key
- The image (container)
- A Patient foreign key
- A Visit foreign key
The relationship between Visits and Images would allow for Image records to be created from Visits.
Then you could have three calc fields on Patients (ImageTypeA, ImageTypeB, ImageTypeC) to indicate whether any related records exist in Images having the corresponding ImageType.
1 of 1 people found this helpful
I'd consider Malcolm's suggestion (with the detail from James).
Agnes's solution is manageable and fine if there are only a few options...as in this case. It is used as a technique in some of the starter solutions to eg. track inventory and I see people get into muddles with it all the time. The problem is that it relies on a snapshot number that has no context in terms of the transactions it relates to. If the increment gets interupted, you don't know and have no other checks in place to ensure it is right. It does have the benefits Agnes cites, though. Malcolms solution is in a dynamic state where you can actually see the image records it counts but when many records are involved can bog things down.
Thank you all for the reponses and answers. I really appreciate your help!
Patients have m Visits which have n Images potentially without the need to store the patients pk within the Image itself (however that s something you can add if required).
I would also use simple List and Count functions and Portals to aggregate the data and display it as required.
See attached example file. Two layouts to look at Patients and Visits. You can simply do a find onthe Number of Images taken field within the Patient Layout to see who has no images or 1, 2 and 3 images.