2 Replies Latest reply on Jan 16, 2013 12:26 AM by LarsGrieten

    related records

    LarsGrieten

      Summary

      related records

      Product

      FileMaker Pro

      Version

      fmp 12 advanced

      Operating system version

      win XP

      Description of the issue

      Hi, Filemaker noob over here... I am working in the medical field and I'm building a database for scientific studies and I have a question regarding scripting.

      Currently I have a patient in patients table with a unique ID.

      Patients ==> examinations ==> ECG, SPIRO, BLOOD, ....

      Every patient has a unique ID, and upon visit the patient has an examination. So creating a new record in examination will generate a unique KEY. To couple this ID to the examinations the script currently copies the Unique patient Key, goes to the ID field in examinations and the script pastes this in the according field.

      The same for the examinations. upon each visit the patient has several examinations. so for each test he takes (ECG, Spiro, blood, etc...) the unique number of the examination is copied and pasted in the table of examinations to relate this back to the same patient.

      It works fine untill now!
      However this does not feel koosjer, Is there a more professional way to populate this field to create these relations?

      Thanks a lot in advance!

      problem.png

        • 1. Re: related records
          philmodjunk

               For one thing, don't use copy and paste to move data from one table/record to another. Copy, Paste and the script steps that start with Insert all silently fail to work if the referenced field is not present on the layout and with access permitted in browse mode. If a future layout change alters that, the script can fail when it worked previously. In addition, Copy destroys any data the user may have previously copied to the clipboard, replacing it with the copied ID.

               You can avoid this by using Set variable, to copy the value into a variable (instead of the clipboard and then use set field in place of paste to copy the value from the variable to the field of the new related record.

               That said, using a script to create a properly linked related record is one of several ways that you can create that related record: http://forums.filemaker.com/posts/e37c1e878a#229064

               And have you noticed that your ECG, Blood and SPiro tables have exactly the same structure (one key field, two value fields and a date? If this will be true for all of your examinations, you don't need this third set of tables. You can add an Examination name field to examinations and include the value and date fields in the Examinations table instead of using a third set of related records.

          • 2. Re: related records
            LarsGrieten

                 Hi Phil,

                 Thanks for the response and the link! I'm trying the Set Variable and it is working well! thanks a lot!

                 Regarding the examinations, the tables are more complex, i just made this sketch for easy understanding. Each table has between 20 and 120 values, with calculation fields etc...