3 Replies Latest reply on Aug 24, 2010 3:40 PM by LaRetta_1

    Set Field/Variable to "0", when Perform Find results = 0

    Derrenger

      Title

      Set Field/Variable to "0", when Perform Find results = 0

      Post

      First time posting in Forum, and have been able to answer all prior questions by researching these extensive boards, but this time I'm stuck. I am using FMP 11.

      I have a table (Layout#1 - which only has one record) that updates itself based on records in a related table (Layout#2 - which has several hundred records) using a script.

      I can get it to go to Layout#2, find records based on a given parameter, set the variable, go back to Layout#1, and set field... but this only works when there are actually records present based on the find.

      If no records are found, I just get a blank field, and what I really want is a "0". I've tired using If [Get (FoundCount) = 0, .... but that's not fixing the problem.

      I am using the following script, but am obviously missing something:

      Go to Layout (Layout #2)

      Show All Records

      Perform Find

      If [Get (FoundCount) = 0]

         Set Field [Layout#1::Field#1; 0]

      Else If [Get (FoundCount) > 0]

         Set Variable [$total; Value:GetAsNumber (Layout #2::Field#2)]

         Go to Layout [Layout#1]

         Set Field [Layout#1::Field#1; $total]

      End If

      Any help or guidance would be greatly appreciated... and thank you in advance for your time and assistance.

      -DD

        • 1. Re: Set Field/Variable to "0", when Perform Find results = 0
          philmodjunk

          You can only access fields of the current layout's table or fields of records that are related to the current record. When you have no record, you have no related record either. Use a variable to store the 0 result and it'll work:

          Go to Layout [Layout #2]
          Set Error Capture [on]
          Perform Find [Restore]
          If [Get (FoundCount) = 0]
                 Set Variable [$total; Value: 0]
          Else
                 Set Variable [$total; Value:GetAsNumber (Layout #2::Field)]
          End If
          Go to Layout [Layout#1]
          Set Field [Layout#1::Field; $total]

          • 2. Re: Set Field/Variable to "0", when Perform Find results = 0
            Derrenger

            Thanks Phil!

            This makes perfect sense, and worked like a charm.

            I appreciate the quick response.

            all the best,

            -DD

            • 3. Re: Set Field/Variable to "0", when Perform Find results = 0
              LaRetta_1

              If [Get (FoundCount) = 0]
                     Set Variable [$total; Value: 0]
              Else
                     Set Variable [$total; Value:GetAsNumber (Layout #2::Field)]
              End If

              ... since you will be setting the variable either way, there is no need to use the If[] test.  It can all be handled within the calculation which sets the variable (it will be particularly faster when using this theory through large record sets or while looping)  It would be simple:

              Set Variable [ $total ; Case ( not Get ( FoundCount ) ; 0 ; Layout #2::Field ) ]

              It also makes the script easier to read when it is one line compared to five.  Neither is there a need to wrap with GetAsNumber() unless the field holds number AND text (which it shouldn't).  Even if it DID hold text as well as numbers, the mathematics would work just the same.