2 Replies Latest reply on Jul 14, 2009 10:08 AM by etripoli

    Find in Calculated Field based on Summary Fields not working

    Pine

      Title

      Find in Calculated Field based on Summary Fields not working

      Post

      Hi,

       

      I am trying to make it possible to find a "payback balance." This = months of obligation incurred by receiving grants, minus months worked to pay back the obligation). Primarily, I want to show the positive balances, which are the cases where an obligation still exists. Negative balances (less than zero) mean the obligation has been more than paid off, so I would like to (be able to) omit those. 

       

      My find for > or < than 0 is for a calculated field (payback_balance_c) that's based on two summary fields. It doesn't work! The find doesn't work for values other than zero, and the "omit" function doesn't work, either.

       

      payback_balance_c produces correct calculations, but I can't find subsets based on those calculated results. If I enter >9, >0, omit something, etc., it returns all records. If I search for a specific result that I can SEE is in there, it says no records match that request!

       

      The calculation uses two tables: Grant and Job.  In Grant, payback_balance_c = total_pb_due_s - Job::sSumMosPdBack.

       

      • total_pb_due_s = Total of pb_due_c    [where pb_due_c = If ( term = "sem" ; 4.5 ; 3 )]
      • Job::sSumMosPdBack = Total of months_paidback    [where months_paidback_c = days_paidback_c / 30, and where days_paidback_c = end_date - start_date]

      I am using FMP 10.0 ver1. All calculations are unstored.

      Suggestions would be appreciated!

        • 1. Re: Find in Calculated Field based on Summary Fields not working
          kapitaen_1
            

          some wild guessing

           

          ... try a refresh layout with options checked "clear query cache", "clear sql cache" before your search.

           

          or...

          do you have decimal numbers (oops, right word? the count of numbers after the decimal point) set to a special resolution? then the number you see is not exactly the number you have (and what you must search for). Try the display field with no decimal specification.

           

           

          greetings from germany 

          • 2. Re: Find in Calculated Field based on Summary Fields not working
            etripoli
              

            Don't use summaries in this situation.  I think you'll need to setup another relationship, a self join of the Grants table, with a calculated field that = Sum ( Grants Self by ID::ttl_months_paidback_c )

             

            Which means creating a 'ttl_months_paidback_c' in Grants that = sum ( Job::months_paidback_c )

             

            And change 'total_pb_due_s' = sum (Grants Self by ID::pd_due_c)

             

            I'm assuming the 'ID' is used to identify a single individual, and is used in the relationships from Grant to 'Grant Self by ID' and Job.