Filtering a database join using the "Filter Where" option

[nrsmoll]

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:

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?

 

[cheesegrits]

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

 

[nrsmoll]

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)

 

[cheesegrits]

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

 

[nrsmoll]

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.

Nick

 

[cheesegrits]

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

 

[nrsmoll]

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.

 

[cheesegrits]

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.

Sure, we can talk any time this coming week.

Safe travels.


Sent from my HTC6545LVW using Tapatalk

 

[nrsmoll]

Friendly bumpity bump

I've been still trying to work this out, these are some code snippets not working

chronic_pain_codes.`fk_cpd_main_raw` = visit.`fk_visit_main_raw`
visit.`fk_visit_main_raw` = chronic_pain_codes.`fk_cpd_main_raw`
{thistable}.`fk_visit_main_raw` = chronic_pain_codes.`fk_cpd_main_raw`
{thistable}.`fk_visit_main_raw` = 74       \* known patient id*\
{thistable}.fk_visit_main_raw = chronic_pain_codes.fk_cpd_main
visit.fk_visit_main_raw = chronic_pain_codes.fk_cpd_main

Effectively, I would like to filter, leaving only the chronic pain codes (issues or diagnoses) that pertain to a particular patient.

 

[cheesegrits]

OK, I think my source of confusion here is that you are using the "Filter WHERE", which is for use when building the filtering options on a list view. So you can filter what gets displayed in the dropdown filter for that element. And in that context, it doesn't make any sense to filter against a specific visit, because it's a list view showing multiple visits.

But I think what you are actually trying to do is filter in form view?

-- hugh

 

[nrsmoll]

Yes! Much like the cascading dropdown plugin.
What I really like about the DB join element is that it gave you the option to view and add a new entry using the database element.

I suspect that functionality is not possible then, and should go back to using cascading dropdowns?

 

[cheesegrits]

What I'm saying is, you shouldn't be using the Filter WHERE, just the WHERE.

And if you are trying to compare a field on the joined table with the value of an element on the form's table, use a placeholder for the latter ...

WHERE {thistable}.whatever = '{maintabale___whatever}'

May need a _raw postfix on the element placeholder.

-- hugh