Calculation field with ExecuteSQL

[kerry]

Hello -

I'm new to SQL and Filemaker calculations, so forgive me if this is completely remedial. I'd be appreciative of any resources you think might be helpful. 

My team is responsible for course scheduling in higher education, and we have an outdated student system that, while great for data entry, is limited in its view options. We use a filemaker as a tool for viewing course details from different tables in aggregate and in relation to each other.

For this calculation purpose, we have two tables, "data" and "CRN". Data contains the only information in our filemaker database that doesn't exist anywhere else (notes, calculated fields, etc.). The table CRN comprises information updated daily from a download out of our student system. Complicating things, CRN stands for "course reference number," the ID number for each unique course section, and is the name of the key field that connects the relationship between the data and CRN tables. I don't have any control over renaming the CRN table nor the CRN field.

We have a series of courses (Schedule_Type YW) with a specific meeting pattern that causes a sorting problem on a particular layout. These courses have regular weekly 2-hr meetings (e.g., Mon 1200-1400 from September 1-December 15 with a MEET_TYPE of "CLAS") as well as a one-off weekend meeting (e.g., Fri 1700-2000 October 11, Sat 0900-1700 October 12, Sun 0900-1300 October 13, each with a MEET_TYPE of "HYB"). This means that one CRN 12345 has 4 meeting lines (one CLAS and 3 HYB). In a list layout in our data table only one line per CRN is displayed (which is what we want), but with any of the 4 meeting lines (randomly displayed, which we don't want).

I'm looking to create a calculated field that pulls the regular meeting time (MEET_TYPE = "CLAS") into one concatenated field (Day + begin time like "M 1200") so that we can use it in various reports.

So far, I have a calculated field that seems to find all CLAS meeting lines, not just the one from unique course. It's further along than I expected to get, but I'm not sure where else to go from here. Any idea how to fix this?

If (
    CRN::Schedule_Type = "YW"; 
    ExecuteSQL ( 
    	"SELECT C.Day_code, C.Begin_Time 
        FROM CRN C, data D 
        WHERE C.CRN = D.CRN 
        AND C.MEET_TYPE = 'CLAS'";
        "" ;
        ""
    );
    ""
)
/*executes into field with very long list of course times with breaks between them: 
"TR,1800
R,1710
F,1730
W,2000
R,1740..." */

Thanks!

[Wim Decorte]

As a side note, consider this: https://www.soliantconsulting.com/blog/executesql-filemaker-performance/

and especially the performance demo file that I link to in that blog post.

Depending on the circumstances it can be a really bad idea to use ExexcuteSQL() in a calculation field.  If you will have a fair amount of records, and that calculation field fires when the user has an open record in the target table, it can cause severe slowdowns in your solution.

Personally I would use a regular non-calc field and have it updated as part of the scripted workflow that updates the meeting times.

Chances are that your report will be a lot more performant.