CODEWITHSUNDEEP
relationshipfilemakertable-relationships
JMW
JMW

Reputation: 13

Filemaker, How to get data from one table to use in another when the tables don't really need to be related

+----- 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!

Upvotes: 1

Views: 542

Answers (2)

Manuel Prado
Manuel Prado

Reputation: 31

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

)

Upvotes: 0

AndreasT
AndreasT

Reputation: 2337

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.

Upvotes: 1

Related Questions