Help with SELECT query needed!


Hi Agassi

I have a working copy of this, so I know it works. It's not so complex once you break it down into parts.


I thought you might have TreatmentsList. Make sure to take out the line

delete (select TreatmentList);


Come back if you run into any problems. Good luck.


Regards John


It seems possible to solve the puzzle with only the existing tables and relations:

let myT := "," + Treatment + ",";
let myD := today() - Days;
(select Visits)[last(Patients.Visits[contains("," + text(Treatments.Name) + ",", myT)] order by Date) = Id and Date < myD]


Ninox Profile

This is why this forum is so valuable....

The real genuine desire to help....



That is so impressive Alain, very well done. I have to say that proves just how powerful the Ninox language is.

Ninox Profile

John, now i understand that i should tell that i have a table Treatmentlist in the beginning ...
That table is 1:N with Treatments table.

When i create a Visit for a patient, i create a record in Treatments, and there i choose a treatment name from Treatmentlist, and a product from Products table.

I guess this part:

delete (select TreatmentList);
delete (select ClientTreatment);
for a in unique((select Treatments).Name) do
let b := (create TreatmentList);
b.(Text := a)

is not necessary then? It is for filling a new table with treatents names, no? And has to be done once? But as i have that table, no need to execute it?

What about the line
delete (select ClientTreatment);
Should it be executed every time??

Anyway, I modified the script like this:

do as server

delete (select ClientTreatment);

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


It started to create lot of records with one patient name and different treatment in each line. And when it reached arond 5000 records, i stopped it. I did something wrong for sure.
But before digging in, I'll try the Alain's solution.

Thank you very much!


And it's exactly that help that inturn helps people like me - sloooowly climb up the coding ladder !

I will copy this thread cos I have a simpliar project I need to employ re production request

Well I have it down on paper at least - which is a start :-)

- Brilliant Guys !!!


Ninox Profile

Steven, Mel - you are right - this forum is amasing! The will to help, the curiosity, the interest in the matter is impressive!!!


Thanks to all of you!!!

Ninox Profile

Alain, Thank you so much for your help!!

I modified the query to my field names, but to be sure i used correct fields, i'll ask:

1) in the first line the word Treatment is a field or i should put a fixed treatment name like "Botox" instead of it?
Ah, probably i can create a field Treatment in that form, where i can type the word (like Botox) and the query will use it to compare...

2) what do these commas "," "," do?

3) Let me tell how i understand your select line. Correct me if i'm wrong please!

(select Visits)[last(Patients.Visits[contains("," + text(Treatments.Name) + ",", myT)] order by Date) = Id and Date < myD]

We select records from Visits, but only the ones in which
the string in Treatments.Name matches our myT,
and Date < myD.

Then we order by Date field.


4) Do I understand right that you use Patients.Visits[contains...  (i.e. you go "higher" in table relations) to search for last visit of that person?

5) what means =Id?


Hi Agassi


Use Alain's solution. Print it out and frame it.


@Alain - have you used this technique before?


Regards John


@John: if by "this technique", you mean encapsulating a condition on a reference inside the condition of a select statement, it is something I just came to while looking for the solution to this puzzle. I'll shure keep the idea for future reuse. It is indeed remarquable that Ninox does swallow such a convoluted expression, and even more remarquable that it executes it perfectly.

@Agassi : since you have a "catalog" of the possible treatments, which I think is the best way to build your database, the formula must be modified, replacing "text(Treatments.Name)" by "text(Treatments.Treatmentlist.Name)".

The strategy is to select records from the table "Visits":

(select Visits) [ boolean expression ]

The boolean expression is evaluated, in turn in the context of each visit, and the visit being checked is retained if the result is true.

(select Visits)[ … Patients.Visits …

For the visit currently evaluated, we peruse the relations to get an array containing handles to the records representing all the other visits for the same patient.

(select Visits)[ … Patients.Visits [ … text(Treatments.Treatmentlist.Name)

For each visit in the array, we get the list of the treatments received, and convert it into a comma-separated string.

(select Visits)[ … Patients.Visits[contains("," + text(Treatments.Treatmentlist.Name) + ",", myT)]

Here we filter the array to only keep the visits where the patient received at least the treatment of interest. We do this by looking for the presence of this treatment name, surrounded by commas, in the string where all the treatments received during the visit are surrounded by commas. This avoids false positives if a treatment name happens to be a substring of another treatment name. The weakness is that treatment names should not contain commas.

(select Visits)[last(Patients.Visits[contains("," + text(Treatments.Treatmentlist.Name) + ",", myT)] order by Date)

We order the filtered visits by date, and only keep the last one. We now have a single handle to the record containing the last visit for the same patient as the visit under consideration, where at least the treatment specified has been applied.

(select Visits)[last(Patients.Visits[contains("," + text(Treatments.Treatmentlist.Name) + ",", myT)] order by Date) = Id

We compare that handle with the Id of the record being checked for inclusion. If they are the same, the visit currently checked is the last visit for that patient where the treatment specified has been applied.

(select Visits)[last(Patients.Visits[contains("," + text(Treatments.Treatmentlist.Name) + ",", myT)] order by Date) = Id and Date < myD]

And finally we check if a new treatment is due.

"Treatment" and "Days" are fields in the Dashboard table where you put the treatment to be checked and the maximum number of days. Since you do have a table containing all the possible treatments, you could use a dynamic choice field instead of a text field where you must type the name of the treatment. Calling this field "Treatment", and filling it with the formula "select Treatmentlist", the formula becomes:

let myT := Treatment;
let myD := today() - Days;
(select Visits)[last(Patients.Visits[cnt(Treatments[Treatmentlist = myT])] order by Date) = Id and Date < myD]

Note that in this version, an alternate method is used to filter the visits, introducing a third level of nested conditions, and getting rid of the "comma business".

If each treatment has a standard number of days before becoming overdue, this information could be encoded in the "Treatmentlist" table and used when a treatment is selected in the dashboard.