Help with SELECT query needed!
this task is too much for me, and i ask your help please!!
I got 3 tables that are joined as 1:N as shown.
As you can see, every patient can have various visits, and his every visit can have various treatments in it.
I need to select patients to whom i did a certain treatment more than given days ago.
I.e. treatment-Botox that was done on a visit that occured 30 or more days ago.
I created a view on one form (my dashboard), where i want to see the result of the query.
So I made a query like this:
select Patients where Visits.Treatments.Name = "Botox" and Visits.'Date' < today() - 30
The result shows me patients to whom i did only Botox, and no threatment more. So, patinets to whom i did botox and other treatments (on the same or diff day) are not included in list.
Then i changed the = for LIKE:
select Patients where Visits.Treatments.Name like "Botox" and Visits.'Date' < today() - 30
Now it gives me a list that probably is correct.
But to know for sure a need to see the date of that treatment/visit to check if it selected it right.
Here is a problem - i dont know how to show that date.
In the view that i made i need columns 'Patient name' and the 'Visit date'.
But when i choose >Visits >'Visit date' from available columns in "Edit columns..." dialog, it forces me to choose First, Last, Count,...
If i choose Last, it gives me the date of the last visit of that patient, not the dte of the visit when i did botox to him.
I ask for HELP in writing the correct query and in showing the date of the visit!
This problem can be solved by turning it upside down. I mean, instead of selecting Patients, select Treatments.
To ease the tests, in the Dashboard table, I created a text field "Treament" to hold the name of the target treatment, and a number field "Days" to hold the maximum number of days. The view can be populated with the formula:
let myT := Treatment;
let myD := Days;
select Treatments where Name = myT and Visits.Date < today() - myD
In the view, you will see that you can directly define a column showing Visits > Date. In order to show the patient, you will need to use a formula:
Set the name of this formula to "Patient", and you should have the desired result.
Hi Agassi -
The reason why there is a difference between = and like is also due to the 1:N relationship. Since you are starting at Patients, Ninox creates an array of all the records linked to parent record. Patient1 could have had Botox,cleaning,etc while Patient2 only had Botox. So a search for = Botox will only bring up Patient2 that only have had Botox. While a like Botox will bring up Patient1 and Patient2.
May I recommend that you change your View to select from the Visits table. If you do a select in Patients, then try to add a field in Visits, Ninox has to ask you to summarize the field as you have a 1:N relationship. That one patient has multiple visits so Ninox has to ask you how you want to summarize the field. If you base your select on Visits then you can add field from Visits or Parent to the View with no problems as there is only a 1:1 relationship at the Visits level. You won't be able to add fields from Treatments without summarizing.
Does this help?
I've had a really good look at this problem and I don't think it can be solved with a select statement. Regardless of the table you base your select on you will fall short of a solution. I think you have to built a relationship bewteen clients and treatments, keeping track of the latest date. Either do this in a script all at once or as you go.each tme a new treatment is added to a visit.
Alaine, Fred - thank you for your answers!
I tried not to write a lot in my question to not bore the reader, but now I see I missed important part of my task.
I'm doing this to find people to whom I did botox 4 or more months ago, to repeat the procedure to them. So I dont want here to see people to whom I already repeated the botox a week or 2 months ago.
My first try was the select from Treatments as Alaine suggests, but then I understood that i don't see a way to exclude people with recent botox, because im selecting Treatments. It would be a weird select, like
select treatment=botox with date more than 120 days of a patient who does not have a treatment =botox with date less than 90 days. ))
This is why I decided to select people.
Fred, by the same reason I dont see how to select visits.
John, thank you for suggesting another approach!
You mean another relational table, or how? Could you please tell a little more?
Hi Agassi -
One way is to add a field in your Treatment table that figures out if a treatment needs to be repeated.
You can create a field called PastDue and put this in the formula:
let x120 := days(Child.Date, today()); <--this formula finds the number of days between two dates and returns a number
if x120 > 120 then "Past Due" end
Then in you Dashboard you can do your select on Botox and do a search on PastDue = "Past Due".
Or you can add another formula that it tells you when the next appointment should be then you can add that to the Dashboard. I don't use the calendar but it should show up in your calendar.
You need to build a matrix of clients and treatment names and hold the last treatment date. So I have mocked this up using two new tables TreatmentList, giving a unique list of treatments and ClientTreatment to hold the matrix. This is a join table between Clients and TreatmentList. TreatmentList has a text field called Name, ClientTreatment has a date field called Date. Use this code in a button
do as server
delete (select TreatmentList);
delete (select ClientTreatment);
for a in unique((select Treatments).Name) do
let b := (create TreatmentList);
b.(Text := a)
for c in select Clients do
for d in select TreatmentList do
let e := (create ClientTreatment);
let f := max((select Treatments where Visits.Clients = c and Name = d.Text).Visits.Date);
Clients := c;
TreatmentList := d;
Date := f
In a view use this code
select ClientTreatment where TreatmentList.Name = "Botox" and Date < today() - 30
Update the columns to show the client and date
Fred, good idea!
May be it will even help me in creating some kind of alert system, that will notify me about due treatments.
But in this case I'm not sure it will work.
Let's say I did botox to Ann and Mary on the same day of January.
In 4 months Ann came to me herself and repeated the treatment. Mary did not.
If I look at the result of your suggested solution I will see January treatments of Ann and Mary, because those are both due. And I dont see there that Ann already has a more recent botox.
Of course I can go to the record of each patient from the result list and check if she had a more recent treatment done, but I want to reduce the manual work with this thing. )
Am I right or am I missing something and it should work?
Thinking more, probably some script can be created that will flag a treatment when a new treatment of same name is created in same patient. Then in your suggestion i could filter only due treatments without that flag.
But i dont know how to create that script for now, jeje...
It seems so complicated for me, that i easily imagine that this script can do THE treatments by itself, leaving me without job )).
Im going to try this now...
In reality i have the table TreatmentList, where i store the name and other data of the procedure. I just skipped it from describing here to simplify the description, probably not doing well.
I think may be i can have a field like 'DaysToRepeat' in TreatmentList, where i will put necessary interval in days for each treatment. And your script will check it.
Big thanks! I'll report back!