Filtering a database join using the "Filter Where" option

Discussion in 'Professional Support' started by nrsmoll, Dec 7, 2017.

  1. nrsmoll

    nrsmoll Member

    Level: Professional
    I have a database join which I would like to use to select a diagnosis code specific to a patient.
    So once the dropdown is a selected, only the records associated with the patient should be shown.

    The database join is in the "visits" table and both the foreign key fields refer to the "main" table primary key (ID) which is the patient ID.

    I have set up a databasejoin element, and in the "Filter Where" option I have tried the following codes:

    Code (Text):
    chronic_pain_codes.`fk_chronic_pain_codes_main` = visit.`fk_visit_main`

    {thistable}.`fk_chronic_pain_codes_main` = visit.`fk_visit_main`

    visit.`fk_visit_main` = chronic_pain_codes.`fk_chronic_pain_codes_main`
    Overall, I want to filter on the two foreign keys (fk), in other words that they match each other.

    Should I be using fabrik notation, or SQL notation?
    Or have I got this all wrong?
  2. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    Which join element is it? Your description doesn't give enough information to figure out what you are doing, it'd be easier for me to look. But you have a LOT of joins ...

    -- hugh
  3. nrsmoll

    nrsmoll Member

    Level: Professional
    Ok, I thought it was a simple code notation issue.

    The join of interest is element number 3215, in group titled "visits".

    I have my "main" table (contains patient identifiers and primary key), which contains joins to two different tables: chronic_pain_codes (Issues list) & Visits.

    Main -> 1:M -> Visits (fk_visits_main)
    Main -> 1:M -> Issues (fk_chronic_pain_codes_main)

    Database join (3215) "visit_reason" found in "Visits".

    Effectively, I have several diagnoses (or issues) for each patient, as patients are allowed to have more than one issue of course. It is here that I will use the "issues" on different occasions, but want to have each diagnosis clearly coded.

    The goal is that when the doctor sees the patient, he decides on the most important diagnosis relevant to the patient for that consultation. From here, the doctor chooses from a pre-populated list of issues, only relevant to that patient (database join)
  4. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    Sorry, I'm still hopelessly confused.

    We may have to discuss this on Skype, so you can walk me through what you are trying to do.

    -- hugh
  5. nrsmoll

    nrsmoll Member

    Level: Professional
    No probs, that sounds reasonable. I'm just on my way back to aus from belgium, so maybe we can tee up a time after this weekend when I get home.

    In the meantime, the goal of the project (quite ambitious) is a clinical information system. Thus far, it seems like fabrik will be able to manage all the requirements, and more (better than Filemaker because of the added functions of joomla, though layouts are a bit easier in filemaker). The idea is that the patient creates an account, and starts filling out outcome measures essentially creating a patient file. Then, when the patient sees a doctor, the doctor fills out a "visits" form to record the visit and any procedure performed.

    While in the visits form, I want the doctor to be able to select the most pertinent issues, from a list of issues that the doctor(s) or physiotherapists have populated at an earlier time (chronic_pain_codes table) for that particular patient. The naming could be more intuitive. Thats where the join comes in. The diagnosis/issue is selected in a dropdown database join element, from a list of diagnoses (from the chronic_pain_codes table). The trouble I am having, is filtering (using the WHERE clause) the options of the database join element so that only the records relating to the patient are seen in the dropdown element on the front-end.

    Maybe I can communicate this better on skype. I'm back on monday next week.

  6. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    Yeah, I'm, confident Fabrik can do it, and we have a number users who have similar projects for medical systems.

    The only issue is that you've bitten off quite a large chunk to chew on as a first Fabrik project, and there's a limit to how much help we can provide on a subscription, even at Pro level. You might consider investing in a consultancy arrangement with us, to provide more hands on help with the design and execution.

    -- hugh
  7. nrsmoll

    nrsmoll Member

    Level: Professional
    I'm well aware of the "chunk". I have completed a "logbook" application that I am using quite successfully at the moment, and was my first go. The main app began as a research tool, but since the goal I have been envisioning for a long time, is an app marrying clinical data and research data (I am a statistician/epidemiologist as well as a doctor). Google scholar has my research profile.

    For now, I'm getting a barebones app up and running to fulfill my basic needs, and have a proof of concept while I trial my ideas, and concepts, and become more knowledgeable on the web side of things.

    I have been planning on investing in formal consultancy after my concept is further along, and I have piloted it. I wasn't sure whom would be the best option and looked up some freelancers, though I am underwhelmed with what I found (though the labor was cheap).

    I am actually on the plane right now, and will be back on the ground in a day or so, so perhaps we can chat about some consultancy agreements then.
  8. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    Sure, we can talk any time this coming week.

    Safe travels.

    Sent from my HTC6545LVW using Tapatalk

Share This Page