Creating a summary table using sql

[rkass068]

Hello, I am kinda of new to filemaker but I was reading up on some sql in FMP 12 and came across a virtual table concept. I currently am doing a database for a cancer clinic which contains 4 tables. the tables are named "Main", "Treatment", "Diagnosis" and "lab". treatment, diagnosis and lab are all related to Main. (__pk_Main=__fk_Main). I want to create a summary table that will extract the Treatment, diagnosis and lab records for each patient in "main" and then display it on a portal. Will sql be able to make this virtual table?

[Manjit Behera]

Yes, absolutely you can do that by using the Execute SQL function in FileMaker12. For example you want to show all related diagnosis, treatment and lab reports of a particular patient. Suppose the key serial of the patient is 101 i.e. __pk_Main = 101. And you have attached a patient list to a global field (say gPatientID) then the list of info you want to display for the selected patient should be like the following.

 

//Treatments details

ExecuteSQL ( "SELECT TreatmentInfo FROM Treatments WHERE __fk_Main = " & gPatientID; "" ; "¶")

 

//Diagnosis details

ExecuteSQL ( "SELECT DiagnosisInfo FROM Diagnosis WHERE __fk_Main = " & gPatientID; "" ; "¶")

 

// Pathological/Lab reports

ExecuteSQL ( "SELECT TestsInfo FROM Lab WHERE __fk_Main = " & gPatientID; "" ; "¶")

 

 

You can create different fields for each details or you can append all the details into 1 calculation field and display the same.

 

In case you want to so these data in portals with different tabs then what you will do is collect IDs from related tables instead of info and use that IDs to relate the records from respective and show the portals records.

 

 

with regards,

[rkass068]

Yes, absolutely you can do that by using the Execute SQL function in FileMaker12. For example you want to show all related diagnosis, treatment and lab reports of a particular patient. Suppose the key serial of the patient is 101 i.e. __pk_Main = 101. And you have attached a patient list to a global field (say gPatientID) then the list of info you want to display for the selected patient should be like the following.

 

//Treatments details

ExecuteSQL ( "SELECT TreatmentInfo FROM Treatments WHERE __fk_Main = " & gPatientID; "" ; "¶")

 

//Diagnosis details

ExecuteSQL ( "SELECT DiagnosisInfo FROM Diagnosis WHERE __fk_Main = " & gPatientID; "" ; "¶")

 

// Pathological/Lab reports

ExecuteSQL ( "SELECT TestsInfo FROM Lab WHERE __fk_Main = " & gPatientID; "" ; "¶")

 

 

You can create different fields for each details or you can append all the details into 1 calculation field and display the same.

 

In case you want to so these data in portals with different tabs then what you will do is collect IDs from related tables instead of info and use that IDs to relate the records from respective and show the portals records.

 

 

with regards,

Thank you so much for the reply. I get the idea. So what I want to do is display some fields from Lab, treatment, and diagnoses all in one portal so that the doctors can see how each patient is doing. Every table has a date field and I want to sort the entries by date. I can see how sql would work to join all of the tables together. How many records do I have to allow in order to show this info. Do I need a new record per patient? or just one record per table. For example one record for treatment and one for lab, one for diagnosis? Thanks so much