1 Reply Latest reply on Oct 29, 2014 10:32 AM by philmodjunk

    Accesing records from different tables



      Accesing records from different tables


      Hi, i'm building a program for a clinic and i want to implement a notification system(email and sms) for appointments. I was thinking to use an on timer script(around 10-30 minutes) that will check an event table for records that have the current date. Every 30 min i want the program to send a notification to the client/doctor if he has an appointment later(if he was already notified that day, it doesn't send another). I'm using a loop system to check every record from that table that matches the conditions, so that is implemented and works as it should....problem is that it works only when i am in a layout connected to the Event table. As the program has over 15 tables and 100 layouts and hundreds of events a month, i don't want to force the program to go to the respective layout and parse the event table as it will mess up with what the user is doing in that precise moment on the layout he is. I am looking for a solution to be able to access the table from anywhere. I have tried with getnthrecord but it doesn't work when i am on a different table(even those that have a relation directly with the event table). As i need information from several fields for each record(like date, start hour,patient name, doctor name, event summary, control variable for events that were sent already), i can''t use summary fields. I am really at lost how to access information from those records without being physically on that table. 

        • 1. Re: Accesing records from different tables

          a) looping through records on a layout in order to find records is very slow and not necessary, Perform a find, use an ExecuteSQL query or match records via a relationship to find the records you need to use for your email notifications.

          b) This may very well still need to be done on a layout based on that table. But it need not be on the same layout that users interact with nor need it be based on the same table occurrence. (two layouts based on different occurrences of the same table will have found sets that are independent of one another) You can also open a different window in order to pull up a found set of these records.

          c) to avoid record locking issues with users that might be editing the same record, "mark" a record has having been used to send an email by setting a value in a record from a related table.