Jump to content

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

Recommended Posts

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

Link to post
Share on other sites
  • 1 month later...

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.

Link to post
Share on other sites

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 by JMW
Link to post
Share on other sites


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).




Link to post
Share on other sites

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.“

Link to post
Share on other sites

@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 by JMW
Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Similar Content

    • By MaddHatter
      (I'm using Filemaker Advanced 15 on Mac OS X El Capitan)
      I have some filtered portals that I need to turn into portals that are filtered by relationship. I can not figure out how to set this up properly.  

      I'm trying make a portal that will show me a list of all of the photos that still need to be Touched-Up. When a photo is selected for touchup the customer record is linked with the Photo by a key.

      Customer -> Photo

      After the photo is emailed out for touchup "EmailSent" goes into Photo::EmailedStatus and if the touchup comes back and needs to be sent out then "Rejected" goes into Photo::ApprovalStatus

      I tried to make a self-join relationship between Customers and CustomersJoin and make a cartesian join between the primary fields and then add the rest of the filters using Calculation Fields and Global fields but I can't figure it out because the calculation field needs to be on the left side. I'm not sure if I'm approaching this right can you help me out?

      1) What Table should the layout be based on? I have it on Customers is that correct?

      2) How can I filter this using this relationship? Am I approaching this right? I want the list to show ALL of the photos where 

      Customers::Key = Photo:::Key


      Photo::EmailedStatus = "Not Emailed"
      Photo::ApprovalStatus = "Rejected"
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.