title: FileMaker - How to have a calculated field reference two tables sort of?
body: <p>Hiy guys,</p>
  
  <p>Let's say I have these three tables*:</p>
  
  <pre><code>ClownOrders
  ------------
  _pk_order_id
  status
  
  ClownOrderLines
  ----------------
  _fk_order_id
  _fk_clown_car_id
  amount
  
  ClownCars
  ---------------
  _pk_clown_car_id
  </code></pre>
  
  <p>I want to provide a calculated column in the "ClownCars" table that shows the total amount for all orders for this car, where the order status is not "canceled".</p>
  
  <p><strong>Thoughts so far</strong></p>
  
  <p>I created a new calculated field in "ClownCars" called zz_g_canceled that simply contains the constant "canceled".</p>
  
  <p>Then I created a new calculated field in "ClownOrderLines" called c_orderstatus that is ClownOrders:status. </p>
  
  <p>Then I created a new table occurrence of "ClownOrderLines" called "OpenClownOrderLines" and linked it to "ClownCars" by this criteria:</p>
  
  <p>ClownOrderLines._fk_clown_car_id = ClownCars._pk_clown_car_id and ClownOrderLines.c_orderstatus != ClownCars.zz_g_canceled.</p>
  
  <p>Finally I added this calculated field to ClownCars called totalOrdered which is sum(OpenClownOrderLines::amount)</p>
  
  <p>But the new column "totalOrdered" always shows "?" now. </p>
  
  <p>*subject matter changed to better hold your interest</p>