StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><strong>Traditional Method:</strong> (Works for FileMaker 7 onward)</p> <p>Traditionally the way to do this in FileMaker is to create additional Table Occurrences of the Coursework_Results table that are related on both "_kf_resultID" and on "type".</p> <p>1: On your "Results" table create a new calculation field for each type of coursework_result. Let us use "Classwork" as the example.</p> <ul> <li>Name the new field _k_Classwork</li> <li>Make the field type Calculation</li> <li>Set the calculation to be type Text</li> <li>Set the calculation to return the string "Classwork"</li> <li>Uncheck "Do not evaluate if all referenced fields are empty"</li> </ul> <p>Repeat this process for every "type" in the "coursework_results" table data.</p> <p>2: For each "type" make a new Table Occurrence of your "Coursework_results" table. Name it "Coursework_results_Classwork". Link it to the "Results" table in the following way:</p> <pre><code>Results::_kp_resultID \____________/ Coursework_results_Classwork::_kf_resultID Results::_k_Classwork / \ Coursework_results_Classwork::type </code></pre> <p>Repeat this process for every "type" in the "coursework_results" table data.</p> <p>3: Set the calculation of "classwork_percent" to be:</p> <pre><code>Sum ( Coursework_results_Classwork::mark ) / Sum ( Coursework_results_Classwork::max_mark ) </code></pre> <p>Then set up the other _percent fields similarly, based on their own newly created table occurrences. (ex: Coursework_results_Quiz::mark and Coursework_results_Project::mark)</p> <p><strong>Execute SQL</strong> (Works for FileMaker 12)</p> <p>FileMaker 12 gives you the new ExecuteSQL step which you can use to skip setting up new schema in your database. In this case, all you would need to do is modify the "classwork_percent" field to have the calculation:</p> <pre><code>ExecuteSQL( "SELECT SUM(mark) FROM Coursework_results WHERE _kf_resultID = ?"; ""; ""; _kp_resultID) </code></pre> <p>For more information check out FileMaker's page: <a href="http://www.filemaker.com/12help/html/func_ref3.33.6.html" rel="nofollow">http://www.filemaker.com/12help/html/func_ref3.33.6.html</a></p> <p>Also check out the FileMaker SQL Sugar ("@") Module for help building queries: <a href="http://www.modularfilemaker.org/2013/03/filemaker-sql-sugar/" rel="nofollow">http://www.modularfilemaker.org/2013/03/filemaker-sql-sugar/</a></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload