1
+----- Code.fmp12 ------+
tables
...
Users
Doctors
Lens Info
Drugs
Diagnosis Codes
...
Equipment *** (Manufacturer, Model Number, Com Port, Speed, Bytes, Bits, ... 
Installed (yes/no).  1 record for each piece of equipment we support. )
...
+----------------------+

+----Exam.fmp12 --------+
tables
...
Exam *** (Eye exam data for a patient.  Multiple exams per patient)
...
+----------------------+

I am working with a Medical Records / Office Management system for eye doctors, optometrists. We provide our solution to multiple customers across the country. One of the things our software does is transfer data from equipment that is attached to a computer to a patient's exam record so the doctors and staff don't have to enter it manually. Currently, I manually modify the scripts that make the data transfer happen. I would like to set up a table containing all the information for each manufacturer and piece of equipment and have the customer select which piece of equipment they have installed and use the data from these fields in the script so I don't have to modify them manually.

In the script I need to know which data from the "Equipment" table to use to set up and transfer the data from the equipment to the exam record but I don't need data about the equipment stored in the exam record. I hope this makes sense.

My question is, how can my script acquire that data from the fields in the "Equipment" table without creating a relationship between the "Exam" table and "Equipment" table? Is it possible? If not, what would be a good way to make this possible.

My background is not databases, so I still struggle with this aspect of databases.

Thanks for all your help!

JMW
  • 13
  • 5

2 Answers2

1

You can fetch any data with the ExecuteSQL function from anywhere. That's your best option. You may also get what you need using a value list from the Equipment table and use the ValueListItems function in the script.

AndreasT
  • 2,317
  • 1
  • 17
  • 14
  • *"You can fetch any data with the ExecuteSQL function from anywhere."* No, not from "anywhere" - only from tables that are represented on the relationships graph of the current file. And if the table is placed there, why not simply make it related? – michael.hor257k Jul 22 '20 at 11:28
  • The "picture" at the stop shows that the code.fmp12 file contains the equipment table. code.fmp12 is the file where tables/lists of "stuff" are stored. For example, users, medical codes, etc. I figured given the structure of the database this was the best place to store the equipment table. The exam.fmp12 file contains data related to patients exams. To me, it doesn't make sense to store the equipment in the exam file. I just need to find out which piece or pieces of equipment the office is using so I can set the parameters, open the serial port and read the data. – JMW Jul 22 '20 at 20:07
  • The equipment table is not on the relationship graph in the current file (exam.fmp12) – JMW Jul 22 '20 at 20:16
  • @JMW If the table is not on the graph, then you cannot use ExecuteSQL() to get data from it. But apparently I need to be insulted for pointing this out. -- Do note that there is no way you will be able to get data from another file without creating some sort of link between the files (at least not without a plugin). Even running a script requires defining the other file as a data source in this file. – michael.hor257k Jul 22 '20 at 21:17
  • @michael.hor257k Thanks for the clarification. That is what I needed to know. – JMW Jul 22 '20 at 21:25
  • In the end I created a field Is Avail in the Exam table that linked to In Use in the Equipment table. While I didn’t really want to add a field about equipment to a patient’s exam, I decided that a field that could be tested to see if the equipment was connected and available for use would be logical. If the field Is Avail is “yes” then I can retrieve the data from the related record and use it to set up the com port, open it and transfer the data. – JMW Aug 27 '20 at 06:01
0

One of the most valuable tool in FileMaker is the ExecuteSQL() function, but the support documentation is not very good, this is by far the best guide you can find for learning or creating SQL queries in FileMaker.

Here is the way I manage my ExecuteSQL() calls, this will prevent the function from breaking if you ever rename a field or table, you just need to search and add the custom function: GetTableNameSQL()

Let (
[
    ~qry = "
        SELECT a. <<fieldTableA>>
        FROM <<tableA>> a
    " ;
    ~SQL =
    ExecuteSQL (
        Substitute (
            ~qry ;
            [ "<<tableA>>" ; GetTableNameSQL ( TABLE::field ; 1 ) ] ;
            [ "<<fieldTableA>>" ; GetFieldNameSQL ( TABLE::field ; 1 ) ]
        ) ; "" ; ""
    )
] ;
~SQL

)

Manuel Prado
  • 31
  • 3