Define If ( IsEmpty(Timeslips::Inv ID ); Timeslips::Duration ; 0 ) as a calculation field inside your portal's table and then compute the Sum of that calculation field on the tab. You can also define a summary field that totals this calculation field in your portal table. That field can even be used to display a running total in your portal if you like that idea.
Thanks for your suggestion. It looks as if I was pretty close before, but didn't take that record-by-record step. What I did since I posted this question was somewhat similar; I defined 2 new calculation fields in the timeslip table,
Bum( If (IsEmpty(Inv ID), 0, Duration))
Bum( If (IsEmpty(Inv ID), Duration, 0))
Then I made Sum(Timeslips::BilledTime) and Sum(Timeslips::UnBilledTime) fields on my Jobs tab.
Not sure if this has any advantage over your suggestion (except possibly making the data available in other related tables), but it seems to work.
Is there a technical reason "Sum(If(IsEmpty(..." solutions don't resolve to a number? It looks as if it would be a compact solution if I could get it to work.
Bum? If you meant Sum( If (IsEmpty(Inv ID), Duration, 0)) then this function is not doing anything for you as you only have a single value to be summed by it.
Sum is defined as strictly summing a list of values. That list can be a list of values like this: Sum ( field1; field2 ; field3 ) or Sum ( RelatedTable::Field ). It wasn't designed by FileMaker Inc. to interpret the results of your If function which doesn't return a list, the If function only references the "first" related record in your portal.
Thanks! You have explained this so clearly that I now understand why the formula didn't work the first time. I didn't realize that the Sum wouldn't parse the If responses, since it always accepted just the Field as an argument, and the IsEmpty took a field as argument.
Which of course raises the question of whether the formula can be tweaked to return a list which WILL be accessed by Sum. Repeating-type argument?
Bum was typo in the formula as posted here, though not in the DB. I should really slow down and proofread before I click the post.
One method is to set up a different relationship to your portal records that only matches invoiced values. You can use a new table occurrence pointing to the same table as your portal to do this. Then, Sum ( newTableOccurrence::Duration ) will total just the invoiced values.
I prefer putting the if calculation into the portal table, but if you want to define a relationship:
Jobs::JobID = Timeslips::JobID AND
Jobs::cOneKey < TimeSlips::InvoiceID
Where cOneKey is a calculation that returns the constant 1 or some nonblank value smaller than your initial invoice number.