Hello!
My name is Jon, I started learning FileMaker about 8 months ago when I got hired for an ophthalmology specific EMR company. I was trained under FM11 Pro Advanced, but am still very new to database development in general.
I've been working on a specialized appointment scheduler module for our program, but have found that my initial approach was unsatisfactory on a network. My Calendar layout was 8 portals of related calculation fields, where each line was a record corresponding to a time (ie, 8:00am, 8:10am.) There were over 1,000,000 of these "time" records stored in this table. When booking appointments Filemaker would perform finds on these records for the correct criteria and write the appointment ID to a field for every record in the found set.
I'm looking for methods to greatly improve the speed of displaying appointments, while still maintaining the robust features needed in a medical scheduler (item overlays, overlapping IDs, and the ability to filter visible appointments by a Schedule and Location field). Ive been looking into Bruce's Virtual List technique with great interest.
I also came across this article recently describing a way to get record data from our in-house server without actually downloading any records, and that is by having a ValueList generated by a single field. As long as each item is different, it will appear on the list. I used a calculation field that concatenates the fields for each appointment into a single line, like so:
ID154@Test Patient@3/15/20129@9:00AM@9:40AM@Dr.Cranmer@Norwich@Glasses Check@
I can retrieve the entire list of appointments and all their data into a variable quickly without having to wait for FileMaker to find anything at all, which I think is absolutely amazing, especially if we are expecting to have 1,000,000+ records of appointments. The question arises though, how do I extract a particular element from a massive ValueList?
I noticed in Seed Code Free Version 12 the ExecuteSQL command is used on the events table as opposed to a traditional filemaker find request that they used earlier. I do not know anything at all about SQL queries or syntax, but my question is:
Is it possible to run a SQL query to extract a particular element from a large ValueList, or a variable? (such as all Norwich appointments between 8AM and 10AM) If so, how?
If its not possible, I probably should just resort to doing the ExecuteSQL command on the appointments table itself, but I just thought I'd ask. Your help is greatly appreciated!