6 Replies Latest reply on Jan 14, 2010 12:22 PM by trialuser1111

    How to account for records that may be empty/not exist

    trialuser1111

      Title

      How to account for records that may be empty/not exist

      Post

      I'm struggling with a calculation in my database and there must be a solution to my problem but I have not been able to figure it out by myself.  Essentially, the calculation takes a stock price from one table and multiplies it by the shares held in another table.  Here's the catch: the shares held may be a positive integer, it may be zero, it may be blank, or there may be no record at all.  The relationship for this calculation is based on the stock ticker and the companyID for the holding firm.  If the firm has never held a position in the ticker, there will be no matching record.

       

      What I want my calculation to do is substitute an a zero for the shares held if it's anything other than a positive integer.  I have tried the following, and neither has been able to account for the possibility of a non-record, I get a ? in my resulting field:

       

      Lookup (Market Data 3::Last Price) * Case ( IsValid (Holdings Prio::Q3'09) ; Holdings Prio::Q3'09 ; 0 ) / 1000000

      Lookup (Market Data 3::Last Price) * ( If ( IsValid ( Holdings::Q3'09 ) ; If ( IsEmpty (Holdings::Q3'09) ; 0; Holdings::Q3'09 ) ; 0 ) / 1000000 )

        • 1. Re: How to account for records that may be empty/not exist
          philmodjunk
            

           

          Try this expression instead:  Case ( Holdings Prio::Q3'09 > 0 ; Holdings Prio::Q3'09 ; 0 ) / 1000000 )

           

          Note that ? is filemaker telling you that "I can't display the value". Sometimes this is due to the width of your field being too narrow or it needs a different number format that'll round the result to a smaller number of decimal places.

          • 2. Re: How to account for records that may be empty/not exist
            trialuser1111
              

            That didn't do it, same results.  FWIW, I've also created an Error field which is equal to:

             

            EvaluationError ( Evaluate ( mycalculationfield ) ) and for the records where Holdings::Q3'09 > 0, the value in this field is 0.  For the records where there is no corresponding record in Holdings::Q3'09, the value is 102.

            • 3. Re: How to account for records that may be empty/not exist
              philmodjunk
                

              Well, it works for me. Just to be sure, I made the following test file to confirm.

               

              I defined two tables, Parent, Child

               

              In Parent I have two fields:

              ID (serial number)

              Calculation : Case ( Child::Value > 0 ; Child::Value ; 0 ) / 100

               

              In Child I have two fields:

              ID (number)

              Value (number)

               

              Relationship:

              Parent::ID = ChildID

               

              Here are the values I got:

              Value:                Calculation:

              5                        0.05

              0                        0

              -5                       0

              <no child record>  0*

              <Empty>             0*

               

              *Do not evaluate if all referenced fields are empty must be cleared otherwise Caclulation is empty.

               

              At no point did I get a question mark.

               

              See any differences between your design and mine?

               

              I'd check to be sure the values you are getting from the related table are the expected values. Could there be more than one related record? Is Q3'09 a number field, summary field, calculation field?

               

              • 4. Re: How to account for records that may be empty/not exist
                trialuser1111
                  

                Still not working.  I added an extra field in my layout to just give me the Q3'09 value in the holdings table, and I am getting the right values there.  The big difference I see with your test and my actual database is the nature of the relationship.  You have a simple 1-1 relationship and I have a 3-table relationship where:

                 

                Holdings Prio::CompanyID = Firms::CompanyID

                Holdings Prio::Ticker = Firms::ClientTickerKey

                Firms::ClientTickeyKey = Market Data 3

                 

                However, that relationship seems to work since I am able to get results in all instances except where there is no record in the Holdings table:

                 

                Holding Value        Calculation

                63,671                  $2.09

                0                          $0.00

                <no record>          ?

                 

                Perhaps its the way the Q3'09 field is managed in the Holdings table?  It's indexed (All), validated only during data entry with no requirements/restrictions/auto-entries.

                • 5. Re: How to account for records that may be empty/not exist
                  philmodjunk
                    

                  I don't think that's the issue. I've been reviewing the thread from top to bottom and decided to check out the EvaluationError function and the 102 error code.

                   

                  Here's what I found in the help:

                   

                  EvaluationError(calculationField) returns 102 (Field Missing) when a field [In the calculation expression] has been deleted or renamed.

                   

                  That suggests that one of your fields in your calculation is misnamed rather than that there is no related record. It would also explain the ? you are seeing.

                   

                  I'd check things over for that type of error carefully. If any of the terms in your calculation are themselves calculations, check them over for errors as well.

                  • 6. Re: How to account for records that may be empty/not exist
                    trialuser1111
                      

                    Okay I took the calculation apart and tested it bit by bit and it turns out the issue was (of course) in a completely different area than I was focusing on.  It was the Lookup() function for the Market Data table.  I took out the lookup() function and it worked fine.  My final calculation ended up being:

                     

                    Market Data 3::Last Price * Case ( Q3'09 > 0 ; Q3'09 ; 0 ) / 1000000

                     

                    Thanks for all the help.