5 Replies Latest reply on Nov 2, 2010 10:14 AM by philmodjunk

    Getting a subtotal of portal items with empty field

    GeorgeMack

      Title

      Getting a subtotal of portal items with empty field

      Post

      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.

      George

        • 1. Re: Getting a subtotal of portal items with empty field
          philmodjunk

          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.

          • 2. Re: Getting a subtotal of portal items with empty field
            GeorgeMack

            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,

            BilledTime:

                 Bum( If (IsEmpty(Inv ID), 0, Duration))

            UnBilledTime:

                 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.

            Best wishes,

            George

            • 3. Re: Getting a subtotal of portal items with empty field
              philmodjunk

              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.

              • 4. Re: Getting a subtotal of portal items with empty field
                GeorgeMack

                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.

                George

                • 5. Re: Getting a subtotal of portal items with empty field
                  philmodjunk

                  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.