Help with SELECT query needed!

Ninox Profile

John, thank you!!! i'll do!

Ninox Profile

Alain, this is incrediblee!

Not only that you're solving my problem, but the way you explain things - thank you very much!

The only thing i still dont get is comparing the handle with the Id...

I'll do the query in my db and report back!

Thank you!!

A

At any time, in any place, "Id" represent a handle to the record that is part of the current context. In the conditional expression of a "select" statement, the context is, in turn, each record of the table we are selecting records from. So the comparison is between a handle to the record containing the last visit from the same patient where it got the treatment of interest, and a handle to the record being checked by the "select" statement. If they are the same, we have got the visit we are looking for, and it will be included in the array returned by the "select" statement, if the condition about the elapsed days is also met.

M

As said before BRILLIANT!

Ninox Profile

Alain, thanks for the explanation!!!!

I played with both versions - with "contains" and with "cnt". First works perfectly - the view is filled with correct records.
The second does not work, the view stays empty.
Cant figure out why...

So, i have a working select already, so thank you again!!

 


Then i tried to use a dynamic choice field as myT.

I created a dynamic choice field, filled it with the formula "select Treatmentlist", in the option Dynamic value name selected Name. The field itself works, but the view stays empty.

For now I added the Treatmentlist table as a Reference from Dashboard to Treatmentlist (N : 1), and use it as myT. It works well! Great!!!

A

Strange that it does not work with a "Choice (dynamic)" field (not "Multiple choice (dynamic)". Technically, a N:1 reference field fits, but there is a risk to inadvertently create new records in the "Treatmentlist" table.

Ninox Profile

@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?

F

Hi Agassi -

 

You are correct that you will have to do your View based Treatments if you want to pull individual data from Treatments.

A

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.Days

This 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 end

Time 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:

Visits.Patients.Name

I did put a toy database illustrating this here:

https://app.box.com/s/q21norbx4zmzal47dptk3u4x6ocn52sq

 

Ninox Profile

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
end

I 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!

 

 

 

Reply