0

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

null
    • Alain_Fontaine
    • 2 yrs ago
    • Reported - view

    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.

    • Fred
    • 2 yrs ago
    • Reported - view

    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?

    • John_Halls
    • 2 yrs ago
    • Reported - view

    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

    • Agassi
    • 2 yrs ago
    • Reported - view

    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. 

    • Agassi
    • 2 yrs ago
    • Reported - view

    John, thank you for suggesting another approach!

    You mean another relational table, or how? Could you please tell a little more?

    • Fred
    • 2 yrs ago
    • Reported - view

    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.

    • John_Halls
    • 2 yrs ago
    • Reported - view

    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

    • Agassi
    • 2 yrs ago
    • Reported - view

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

    • Agassi
    • 2 yrs ago
    • Reported - view

    Wow, John!
    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!

    • John_Halls
    • 2 yrs ago
    • Reported - view

    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

    • Alain_Fontaine
    • 2 yrs ago
    • Reported - view

    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 partner
    • RoSoft_Steven.1
    • 2 yrs ago
    • Reported - view

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

    The real genuine desire to help....

    • John_Halls
    • 2 yrs ago
    • Reported - view

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

    • Agassi
    • 2 yrs ago
    • Reported - view

    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)
    end;

    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);
    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

     

    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!

    • Mel_Charles
    • 2 yrs ago
    • Reported - view

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

    • Agassi
    • 2 yrs ago
    • Reported - view

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

    • Agassi
    • 2 yrs ago
    • Reported - view

    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?

    • John_Halls
    • 2 yrs ago
    • Reported - view

    Hi Agassi

     

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

     

    @Alain - have you used this technique before?

     

    Regards John

    • Alain_Fontaine
    • 2 yrs ago
    • Reported - view

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

    • Agassi
    • 2 yrs ago
    • Reported - view

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

    • Agassi
    • 2 yrs ago
    • Reported - view

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

    • Alain_Fontaine
    • 2 yrs ago
    • Reported - view

    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.

    • Mel_Charles
    • 2 yrs ago
    • Reported - view

    As said before BRILLIANT!

    • Agassi
    • 2 yrs ago
    • Reported - view

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

    • Alain_Fontaine
    • 2 yrs ago
    • Reported - view

    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.

Content aside

  • 2 yrs agoLast active
  • 31Replies
  • 3419Views