ExecuteSQL on a ValueList?

[noneoftheabove21]

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:[email protected]@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!

[mr_vodka]

The Execute SQL command should be done on the table itself. You can extract values from a value list using various functions such as GetValue (); LeftValues(), MiddleValues(), RightValues (). FilterValues (), Pattern (), Fitler (), etc etc. But with FMP12 you can directly ask a table for the information via SQL query.

[swl]

Hi Jon,

You can use the NthItem function to retrieve particular values in each string:

http://www.briandunning.com/cf/366

That way you could say NthItem(yourlongvaluelistitem, "@", 3 ) = 3/15/2012

in the example string you provided (I disregarded the typo 9 after the year)

As for searching for multiple values, you may have to convert the date & time to a number and then do ranged searches

So the date 3/15/2012 becomes 734577 - via this calc: GetAsNumber(GetAsDate("3/15/2012")) - then you search for 734577...734578 for anything between 3/15 & 3/16. You can convert timestamps to numbers as well and do that also.

That's my first thought,

Denis

EDIT: PS: ExecuteSQL may simplify all of this, I haven't played with it enough to determine that

[noneoftheabove21]

Thank you so much for your replies. I probably won't use the ValueList of appointments for this right now, maybe for something else later on. We'll see how it plays out with network performance on the ExecuteSQL function first I guess. I'm really excited about all of this because it means I could scrap a entire table containing 1,000,000+ records!

After reading up on some SQL basics, this is my SQL command I've come up with so far: (modified from SeedCode Free) I really hope FileMaker's ExecuteSQL function supports all of this syntax...

============================

"SELECT " & DesiredFields & " FROM " & $$TableName & " WHERE " & "(" & $$TimeCalcNumStartField & " BETWEEN " & StartRange & " AND " & EndRange & ")

AND " & $$LocationField & "='" & $$ViewingLOC & "'

AND " & Quote($$ScheduleField) & " IN ('" & $$SelectionA & "', '" & $$SelectionB & "', '" & $$SelectionC & "')

¶Union

SELECT " & DesiredFields & " FROM " & $$TableName & " WHERE " & "(" & $$TimeCalcNumEndField & " BETWEEN " & StartRange & " AND " & EndRange & ")

AND " & $$LocationField & "='" & $$ViewingLOC & "'

AND " & Quote($$ScheduleField) & " IN ('" & $$SelectionA & "', '" & $$SelectionB & "', '" & $$SelectionC & "')

¶Union

SELECT " & DesiredFields & " FROM " & $$TableName & " WHERE " & "(" & $$TimeCalcNumStartField & " < " & StartRange & " AND " & $$TimeCalcNumEndField & " > " & EndRange & ")

AND " & $$LocationField & "='" & $$ViewingLOC & "'

AND " & Quote($$ScheduleField) & " IN ('" & $$SelectionA & "', '" & $$SelectionB & "', '" & $$SelectionC & "')

ORDER BY " & Quote($$ScheduleField) & ", " & Quote ($$TimeCalcNumStartField)

======================

Selection A, B, C, and are values from three column header fields above the Calendar portal. This is so users will able to select which schedule they'd like to view and the Calendar will display it, instead of using a horizontal slider bar or something to find a schedule resource. ViewingLOC is another filter, that will cause SQL to find appointments for the selected office location.

I changed the Sort Order from "1" to the Schedule, followed by the time stamp. The hard part is figuring out how I'm going to correctly place all of this into the array for proper display. I'm still trying to decipher how SeedCodes knows which row and column number to write the events into. It seems the variables come up magically during their scripts.

I also wonder why there are two UNIONS here. Can't all of this be revised into just one SELECT statement?

- Jon