3 Replies Latest reply on Dec 3, 2010 2:33 PM by philmodjunk

    Calculation help: If a group of fields contain a or b or c, then return result d.

    ChristineT.

      Title

      Calculation help: If a group of fields contain a or b or c, then return result d.

      Your post

      Hello FileMaker Gurus,

      I'm having trouble setting up a long calculation and I now am wondering if a calculation is even the right thing to use.

      What we're trying to do:

      We have one Status field (StatusField) that we want to automatically change to say "OK!" once a bunch of fields (Field1, Field2...Field20) have been filled in with one of three values (a, b, or c).

      I am trying to set up an If statement for each of the 20 fields like so:

      If (Field1 contains a, b, or c) AND (Field2 contains a, b, or c) AND (Field3 cotains a, b, or c), ...AND (Field20 contains a, b, or c) THEN StatusField = "OK!"

      I am no master of code or syntax so I am having trouble setting up the statements themselves, but I am also wondering if there is an easier way to go about this.

      [Aside: I wrote a test calculation using just one of the fields and a Case function, but it doesn't take into account the fact that all the other fields must also contain a, b or c. I've pasted it here as a reference but it may not be relevant: Case ( Field1="A" ; "OK!" ; Field1="B" ; "OK!" ; Field1="C" ; "OK!" )  ]

      Thanks

      Cool

      Christine

        • 1. Re: Calculation help: If a group of fields contain a or b or c, then return result d.
          philmodjunk

          Given that you have 20 fields, you may well do better to move those 20 fields into a related table where you have 20 records with this field instead of 20 different fields.

          Using your current structure, you could insert Field1="A" or Field1="B" or Field1="C" inside each pair of parenthesis in your If statement.

          You could also use this approach:

          If [ PatternCount ( "ABC" ; Field1 ) AND PatternCount ( "ABC" ; Field2 ) AND ... Patterncount ( "ABC" ; Field20 ) ]

          • 2. Re: Calculation help: If a group of fields contain a or b or c, then return result d.
            ChristineT.

            Hi Phil,

            PatternCount totally worked—thanks!

            I'm not sure that the 20 fields we have could be moved to a separate table due to what they are tracking.  However we are new to related tables so I could be wrong.

            We're set up with 2 main tables, one for books (where each book is a record) and one for corrections (where each correction is a record related to a book).  Then for each correction record that comes in, several different people need to check other components (worksheet, lesson plan...etc) which may also need to be fixed due to the correction that came in. 

            The 20 fields that repeat in the calculation are for different people to sign off on the components they are responsible for e.g.:

            Field1 = Is the worksheet fixed?, Field2 = Is the Lesson Plan fixed?, Field3 = Is the Spanish version fixed?...etc. 

            We have also discovered that calculations/scripts containing fields from related tables do not always work for IWP users (unless of course we are doing something wrong).

            Thanks for all your help,

            Christine

            • 3. Re: Calculation help: If a group of fields contain a or b or c, then return result d.
              philmodjunk

              You could definitely set that up as a related table: the fields you'd use might be defined like this:

              BookID (foreign key to book table)
              Component (text)
              Status (fixed or not fixed, make this a number field with boolean formatting, 1 = fixed )

              From the Book Table, Count ( Components::BookID ) = Sum ( Components::status ) would be true only if all listed components have "fixed" as their status.