ExecuteSQL to find records in table and related tables

[Solvax]

Hi,

I'd like your opinion and advice regarding the following situation.

I have a database with several (related tables). Most of the layouts are created with portals which get populated by an SQL search function based on the Soliant tutorial. I have very limited knowledge of SQL programming. The ExecuteSQL script searches all the fields in the current table and the portal then shows the found records.

However, I also want records to show up if the entered search value matches a related field. Eg. table 1= patients and table 2= prescriptions. When searching in the prescription overview, I also want to be able to enter the patient name so all the prescriptions for that patient name show up. The patient name is a related field from table 1.

From what I understand, I need to establish a JOIN to create the SQL relationship. JOIN is known to reduce the search performance and to be avoided it seems. Can anyone guide me on how to tackle this?

- another search solution other than SQL?
- or SQL and should I approach the code?

 

"ExecuteSQL ( 

\"SELECT \" & Quote ( \"ID\" ) & \" FROM " & $get.tablename & " 
 WHERE " &
 $this.fields & "\" ; 

\"\" ; // field separator¶
\"\" ; // row separator¶

" &  $this.params & "

)"

The tablename is defined earlier in the filemaker script and the script is written to search all the fields in the defined table.

I hope my question is clear, if not, I gladly offer additional info.
You help is much appreciated.

Best regards and already happy NYE / NY!

Solvax

[comment]

13 minutes ago, Solvax said:

When searching in the prescription overview, I also want to be able to enter the patient name so all the prescriptions for that patient name show up.

Is there a reason why you cannot use Filemaker's native find for this? To see all prescriptions of a patient, you could find the patient's record in the Patients table and view their prescriptions in a portal to the Prescriptions table, based on a relationship matching on PatientID. Or you could perform the find from a layout of the Prescriptions table, and enter the patient's name into a field from the Patients table; this would also enable you to specify find criteria for the prescriptions too.

 

[Solvax]

1 hour ago, comment said:

Is there a reason why you cannot use Filemaker's native find for this? To see all prescriptions of a patient, you could find the patient's record in the Patients table and view their prescriptions in a portal to the Prescriptions table, based on a relationship matching on PatientID. Or you could perform the find from a layout of the Prescriptions table, and enter the patient's name into a field from the Patients table; this would also enable you to specify find criteria for the prescriptions too.

 

Thank you for your advice. I thought it was handy to search through all the fields with SQL without the requirement of having all the fields in a layout to search it.

[comment]

26 minutes ago, Solvax said:

I thought it was handy to search through all the fields with SQL without the requirement of having all the fields in a layout to search it.

I am not aware of any such requirement. 

IMHO, SQL should be your last choice, not first. Especially if, as you say, it's not your forte. 

--
I should also add that searching all fields in a table is rarely required - and in most cases such request is a result of inadequate structure. But if you genuinely need this, see:
https://fmforums.com/topic/65087-search-multiple-fields/

 

[Solvax]

3 minutes ago, comment said:

I am not aware of any such requirement. 

IMHO, SQL should be your last choice, not first. Especially if, as you say, it's not your forte. 

--
I should also add that searching all fields in a table is rarely required - and in most cases such request is a result of inadequate structure. But if you genuinely need this, see:
https://fmforums.com/topic/65087-search-multiple-fields/

 

Ok thank you, makes sense. I don't have the need to search all fields but I wanted so search also some fields which are not displayed on the layout. Apparently this was a misunderstanding from my side thinking that it is necessary to have the fields displayed in the layout and accessible for search requests, so will do some testing. Your help was much appreciated!

 

Edited December 30, 2020 by Solvax