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

[JMW]

   +----- Code.fmp12e ------+
     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 an 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!

[comment]

If you don't want to use a relationship (why not?), then use a find. Much depends on where the customer's choice of equipment will be stored.

[MartinL]

If you do not want to have a relationship between the 2 table then you might want to try using the ExecuteSQL command with a SELECT statement such as

SELECT Field1, Field2,Field3 FROM Equipment WHERE EquipmentID = EXAM.EquipmentUsedID

Jusr replace the fields with yours then you can parse th result to get the individual field data.

[JMW]

I read on stack overflow that the tables need to be on the relationships graph to use ExecuteSQL.  Is that true? 

I currently, have the equipment table in a file containing basic information used by the software and the exam table in a file containing information about eye exams.  I linked them using a single field "Used" in the Equipment table and "Is Avail" in the Exam table.  This allows me to find out if equipment is being used and if it is retrieve the data to set the COM Port and know the format of the data I will be receiving.

I'd still like to do it without linking the tables if I can.  There really isn't any need for equipment data to be in with exam data.

Thanks for all your patience and help.

Edited September 10, 2020 by JMW
Clarity

[MartinL]

Hi JMW

Yes, you would need to have an instance of the related table in the schema so you could use the table instance name in the query but it does not necessarily need to actually have a relationship to the other table.

I am assuming that you have already included the Equipment file as an External Data Source.

If you do not want to add the Equipment file to the Exam file at all I would consider creating a Web Service somewhere that you could call from a script in the Exam file which would then retrieve the data from the Equipment file using FileMaker xDBC or the FileMaker Dat API and then return the result back to the Exam file.

 

I have just watched a really good presentation by Wim Decorte about Web Hooks and although it may not be exactly what you need it gives a good understanding of Web Hooks (Web Services).

 

https://www.youtube.com/watch?v=N9GJ4sz1hSE

 

[bcooney]

I’d store EquipmentId in the exam record, and relate them. No need to bring in all equipment field data to exam. 
 

Is it only one piece of equipment per exam? If not, you need a table ExamEquipment or a multikey, EquipmentIdList in Exam. 
 

I don’t  understand this part.  “I linked them using a single field "Used" in the Equipment table and "Is Avail" in the Exam table.“

[JMW]

@bcooney it could be more than one piece of equipment but the script can identify how to process the data by looking at the model number of the equipment which is stored in the table.

I have the Equipment table in a file that contains data needed to run an office and do eye exams.  It is a list of equipment that we support with all the information required for reading the data from the equipment and importing it into an exam.  I needed some way to let the script in the exam table (in a completely separate .fmp12 file) know whether or not equipment is set up for automatic data transfer.  So, in the equipment table I have a field "In Use" and a field “Avail” in the exam table.  The user enters  “yes” by the equipment they use in their office.  I figured this would be an easy way to identify which equipment in the list is being used.  I can then retrieve all the information that is necessary to setup, transfer and import data into the exam record.

I only care about the equipment for the data transfer.  After that the equipment is irrelevant.

I didn’t think of linking the two tables using an equipment id.  Maybe because I was trying to identify whether or not equipment is being used and if so which equipment is used in a particular office.  I also don’t come from a traditional programming background and am still learning about keys and multikeys, etc.

We have over 150 offices that use the same base application.  In the past I hardcoded the information for each office but I’m trying to move away from that by creating a table with a  list of equipment we support and allowing the user to choose what they have in their office.  My goal is to remove myself from the equipment integration loop.

Thanks for the ideas.  I always learn something new from these discussions.

Edited September 16, 2020 by JMW