Help with SELECT query needed!
Hello, guys!
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.
Patients
Visits
Treatments
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!
Big thanks!!!
Agassi
31 replies
-
@Alain I used the Choice, not multi choice. But the reference field suits me well, its ok!
Using the query already for a little while, i wonder can it be improved a little more?
The thing is that in a view of that select i'd like to see not only the date and name of the patient, but also the treatment name and the name of the product i used in that treatment.But when i add columns Treatments.Treatmentlist.Name and stock.'product name' to that view, i have to choose from "first", "last", "concat" to get that column. And i have to choose concat to see all whats done in that visit. Its not a 'big deal", i can see the botox between other treatments done on one day, but it would be cleaner to be able to show only botox treatment and the product used in that treatment.
Is it possible to achive that? If not while selecting Visits, maybe selecting treatments directly?
-
Hi Agassi -
You are correct that you will have to do your View based Treatments if you want to pull individual data from Treatments.
-
Hi,
It seems indeed possible to produce a more focused view by selecting records from the "Treatments" list.In the "Treatmentlist" table, I defined a number field "Days" to hold the "standard" number of days after which a treatment must be repeated.
In the "Treatments" table, I defined a number field "Days" to hold the number of days after which this particular instance of treatment must be repeated. When selecting a treament from the "Treatmentlist" table, this field is initialized with the "standard" value. This is done by the following formula as the "Trigger after update" of the reference field "Treatmentlist":
Days := Treatmentlist.DaysThis field can then be modified if, for some reason, a non-standard delay must be observed. In particular, if the current treatment is the last one, just put "0" in the field, or leave it blank.
I then defined a formula field "Due", computing the the last date for the next treatment with the formula:
if Days then Visits.Date + Days else null endTime to define the view in the Dashboard table:
let myT := Treatment;Here we save the selected treatment from the Choice (dynamic) field "Treatment" in the variable "myT".
(select Treatments)[Treatmentlist = myT and if Due then Due <= today() end and last(Visits.Patients.Visits[cnt(Treatments[Treatmentlist = myT])] order by Date) = Visits]We select records from the "Treatments" table, and only keep those who meet three conditions combined with "and". Those conditions are evaluated, in turn, in the context of each record of the "Treatments" table.
(select Treatments)[Treatmentlist = myT and if Due then Due <= today() end and last(Visits.Patients.Visits[cnt(Treatments[Treatmentlist = myT])] order by Date) = Visits]The first condition is that it is the kind of treatment we are interested in.
(select Treatments)[Treatmentlist = myT and if Due then Due <= today() end and last(Visits.Patients.Visits[cnt(Treatments[Treatmentlist = myT])] order by Date) = Visits]The second condition is that the limit date has been reached. We only do the test if the "Due" field is not empty.
(select Treatments)[Treatmentlist = myT and if Due then Due <= today() end and last(Visits.Patients.Visits[cnt(Treatments[Treatmentlist = myT])] order by Date) = Visits]The third condition ensures that a record is only retained if it is the last one satifying the two first conditions.
(select Treatments)[Treatmentlist = myT and if Due then Due <= today() end and last(Visits.Patients.Visits[cnt(Treatments[Treatmentlist = myT])] order by Date) = Visits]We go "up" to the (only) visit containing the treatment being checked, then "up" again to the (only) patient concerned, and "down" to the visits to get an array containing handles to all the records holding visits for that patient.
(select Treatments)[Treatmentlist = myT and if Due then Due <= today() end and last(Visits.Patients.Visits[cnt(Treatments[Treatmentlist = myT])] order by Date) = Visits]We filter the array by only keeping those visits where the treatment of interest has been applied, among others. We do this by asking that the number of treatments equal to the treatment of interest in the visit is not zero.
(select Treatments)[Treatmentlist = myT and if Due then Due <= today() end and last(Visits.Patients.Visits[cnt(Treatments[Treatmentlist = myT])] order by Date) = Visits]We order the visits of the same patient where the treatment of interest has been applied by date, and keep the last one.
(select Treatments)[Treatmentlist = myT and if Due then Due <= today() end and last(Visits.Patients.Visits[cnt(Treatments[Treatmentlist =myT])] order by Date) = Visits]If that last visit is the visit where the currently checked treatment has been applied, the third condition is met and we retain the treatment.
In the view, the name of the patient is not available as an immediate choice, and must be grabbed by a formula:I did put a toy database illustrating this here:
-
Alain, hello!
Sorry for late reply, i was seek several days.
Many thanks for your last solution, it works very well!!! And again, special thanks for such an explanation!!
For this solution to work every already finished treatment had to have days field filled by the value from TreatmentList.
So i made a button with this code to fill that field:
for oneRec in select 'TreatmentList' do
for manyRec in select Treatments where TreatmentList.Name = oneRec.Name do
manyRec.(Days := oneRec.Days)
end
endI am not sure that its an optimal way to do it, but It worked, so all the treatments already done will show up in the view.
My best regards!
-
When doing something once, finding the optimal solution is not indispensable. Nevetheless, you could have saved yourself some work by: opening the "Treatments" table, select "Update multiple records…" in the "Gear" menu, choose "Assign calculated value" for the "Days" field, and enter "Treatmentlist.Name" as the formula. Once in a while, Ninox can really be almost "nocode" :-).
In the meantime, just for fun, I updated my toy database, wich has got a new url:
https://app.box.com/s/14ai1gl2vssrdhe6sts2mygne2c4db0l.In the "Treatmentlist" table, I added a view showing the instances of the current treatment that are overdue. The formula to fill this view is a sibling of the one in the dashboard, but since we do have a relation in place, it does not need to invoke the almighty "select" command. Also, since it follows the reference, the first condition is of course true by definition and must not be checked anymore. I also added a tabular view that pinpoints the records where that view is not empty. If this is not useful for you, no problem, it was fun to make.
-
Hello Alain!
I did not think that "Update multiple records…" will work that way - very useful to know!!!
Your db is very helpful, and the tabular view is cool - we can see in a glance what treatments are due in general. Very helpful!
Dear Alain - thank you very much again!!!! You are a very good teacher!!!
Content aside
- 3 yrs agoLast active
- 31Replies
- 3641Views