The DBMS server said: SQLSTATE[42S22]: Column not found: 1054 Unknown column

[meanster99]

You are using DaDaBIK version 8.0-Lerici enterprise, installed on 14-06-2017 (installation code: 125685940bf0f43a66), the latest version of DaDaBIK is 8.0-Lerici released on 24-04-2017

You are running the last release of DaDaBIK

PHP Version: 7.0.20

mysql version: 5.5.5-10.1.24-MariaDB

Web server: Apache/2.4.26 (Unix)

Client: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.36


Hi,

I am wondering why I get the following error after entering a new record into the 'tbl_treatments' table:

[08] Error: during query execution. select * from `tbl_clients` where `tbl_clients`.`fld_patientID` = '25'
The DBMS server said: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'tbl_clients.fld_patientID' in 'where clause'

I have a master/detail relationship between tbl_clients (master) and tbl_patients (detail) and then one between tbl_patients (master) and tbl_treatments (detail). My db schema is below:

-- Table structure for table `tbl_clients`

`fld_clientID` int(11) NOT NULL AUTO_INCREMENT,
`fld_title` varchar(11) DEFAULT NULL,
`fld_firstname` varchar(25) DEFAULT NULL,
`fld_lastname` varchar(25) DEFAULT NULL,
`fld_email` varchar(50) NOT NULL,
PRIMARY KEY (`fld_clientID`)

-- Table structure for table `tbl_patients`

`fld_patientID` int(11) NOT NULL AUTO_INCREMENT,
`fld_clientID` int(11) NOT NULL (FK)
`fld_petname` varchar(50) NOT NULL,
`fld_speciesID` int(11) NOT NULL,
`fld_conditionID` int(11) NOT NULL,
`fld_clinical_history` tinyint(1) NOT NULL,
`fld_xray_history` tinyint(1) NOT NULL,
`fld_oa_app` tinyint(1) NOT NULL,
`fld_consult_typeID` int(11) NOT NULL,
`fld_consult_date` date DEFAULT NULL,
`fld_insure_coID` int(11) NOT NULL,
`fld_pracID` int(11) NOT NULL,
`fld_testimonial_req` tinyint(1) NOT NULL,
`fld_testimonial_rec` tinyint(1) NOT NULL,
PRIMARY KEY (`fld_patientID`)

-- Table structure for table `tbl_treatments`

`fld_treatmentID` int(11) NOT NULL AUTO_INCREMENT,
`fld_patientID` int(11) NOT NULL (FK)
`fld_treatment_date` date DEFAULT NULL,
`fld_treatment_typeID` int(11) NOT NULL,
`fld_callback` tinyint(1) NOT NULL,
`fld_followup_date` date DEFAULT NULL,
PRIMARY KEY (`fld_treatmentID`)

I have read that nested master/detail relationships don't work with Dadabik yet, but actually, even though I get the error I mentioned, once I click back into the 'tbl_patients' table, the details table, 'tbl_treatments' shows the added record in the master/detail grid view.

I just bought version 8 of Dadabik after a long hiatus and am a bit disappointed that it appears I can't achieve what I want to, considering the simple nature of the table relationships. Is the error I'm getting to do with the nested master/detail issue, or something else? As I said, if I don't get that error, everything appears to work as I want, so I'm hoping the error is being caused by something else.

Any ideas please (I've checked the forum and saw another similar issue that required the closing of the browser after a DB sync to reset the session, but I tried that and it didn't work).

Thanks,
Matt

 

[larryk]

`tbl_clients`.`fld_patientID

client table does not have a patient ID

IMO, version 8 has many good things... I just started using dadabik and liking it (very solid, stable!!) have not found any bugs.
obviously, i found various new features I want... but i'm impressed with it

 

[eugenio]

Hello Matt,
yes, the problem as you guessed is the nested master details, DaDaBIK doesn't support nested master details and that's why it didn't work as expected.

One possible workaround in your case is probably the following: you give read permissions to tbl_patients and you add treatments by:

- clicking the patients link form the menu

- enter the patient you want to add a treatment to in edit mode (click edit)

- add the treatment as a new item.

Basically is what you are already doing I guess but without going through the clients->Patients->treatments workflow but through the clients->Patients first and then Patients->treatments workflow separately. This doesn't mean you have to delete the master/details view from clients to patients.

Best,

 

[meanster99]

Thanks Eugenio, that's what I feared. You're right though, using the workflow you suggested is a workaround, it's just a shame the nested master details doesn't work completely as expected.

Is it possible to have a create/edit form that allows a user to enter data into the master details tables simultaneously? e.g. a form that allows me to enter either client and patient, or patient and treatment data all within the same page, without having to move between tables?

 

[eugenio]

Hello,
no, each form has one linked table (or view), unless you add master details view.

Best,