Getting a subtotal of portal items with empty field
I built my bookkeeping system in Filemaker Pro 10, with related tables for Invoices, Jobs and Timeslips (among others). My idea is that any Job will usually have multiple timeslips, but may be billed out on different Invoices (by week or month, for example).
My Jobs layout has a tab with a portal for related timeslips (those that share the Job ID), and each timeslip has an option for Invoice ID, which is filled only when the timeslip is assigned to an invoice.
My problem is that while I can show a calculation field (on the tab but not in the portal) for ALL time assigned to the job, I cannot seem to find a way to define a calcuation field for only timeslips where Invoice is empty (which would be handy for the next round of billing). I tried this formula,
Sum ( If ( IsEmpty(Timeslips::Inv ID ); Timeslips::Duration ; 0 ) )
but what I get back is a field with a ? in it. I know I must be missing something, but I've hit the wall on my understanding of relational databases. The best I've been able to do so far is conditional formatting on the basis of the INV field being empty. It feels like what I need is a subtotal by invoice number, but I can't figure how to do that inside a portal.
I appreciate any help people can offer.