Help with SELECT query needed!

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:

Visits.Patients.Name

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?

Hi Agassi

 

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.

 

Regards John

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.

Hi Agassi

 

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)
end;
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);
e.(
Clients := c;
TreatmentList := d;
Date := f
)
end
end
end

 

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

 

Regards John

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]

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.

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

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.

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.

As said before BRILLIANT!

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.