5 Replies Latest reply on Jun 4, 2009 3:44 PM by jamie.hunt

    Calculation or relationship for if "field contains"

    jamie.hunt

      Title

      Calculation or relationship for if "field contains"

      Post

      Hi guys, 

       

      I'm sure someone can help me, I'm new to FileMaker. 

       

      I have a field with 3 repetitions. Say for example the field contains the text    "red", "green", "blue"

       

       

      What operator would I use if I want to say "If field contains the text "green"... perform whatever calculation. I can't use = because I only want it to do the calculation if it contains just one color.

       

       

      Secondly how would I set a relationship up so that it isn't if...   field "A" = field "B"     it's     field "A" contains the same as field "B" when "A" has 3 repetitions.

       

       

      Sorry if this doesn't make sense.  

        • 1. Re: Calculation or relationship for if "field contains"
          philmodjunk
            

          GetRepetition ( Field ; 1) = "green" OR GetRepetition ( Field ; 2) = "green" OR GetRepetition ( Field ; 3) = "green"

           

          I can't make heads or tails out of:

           

          "Secondly how would I set a relationship up so that it isn't if...   field "A" = field "B"     it's     field "A" contains the same as field "B" when "A" has 3 repetitions."

          Perhaps you could give an example of what you want here?

          • 2. Re: Calculation or relationship for if "field contains"
            jamie.hunt
              

            Thanks for your help... I can see how that would work, I'll give it a try!

             

             

            Sorry, I'll give you an example.

             

            I have one table set up listing different types of paints we stock, with fields for the color, size, and type. The field for type is set up for 3 repetitions because there can be matt, gloss or satin in each color.

             

            i.e.

            color: "green"    size: "500ml"    type: "matt", "satin"

            color: "red"       size: "500ml"    type: "matt", "gloss"

            color: "blue"      size  "500ml"    type: "matt", "gloss"

             

            I would like to be able to show in a portal all paints that are "matt" in which case here it would be all 3. Or for example all paints which are "gloss" so it look through all repetitions  and display 2 paints in this case.

             

            I hope that makes more sense. 

            • 3. Re: Calculation or relationship for if "field contains"
              philmodjunk
                

              I would simplify the structure to eliminate the repetitions:

               

              color: "green"    size: "500ml"    type: "matt"

              color: "green"    size: "500ml"    type: "satin"

              color: "red"       size: "500ml"    type: "matt"

              color: "red"       size: "500ml"    type: "gloss"

              color: "blue"      size  "500ml"    type: "matt"

              color: "blue"      size  "500ml"    type: "gloss"

               

              Now you can set up a global field to match with your type field and you can specify "Matt", "Gloss", "Satin" in your global field to filter the list by type. If you make your global a check box field, you can even filter by combinations of the three values.

              • 4. Re: Calculation or relationship for if "field contains"
                comment_1
                  

                jamie.hunt wrote:
                Secondly how would I set a relationship up so that it isn't if...   field "A" = field "B"     it's     field "A" contains the same as field "B" when "A" has 3 repetitions.

                Given a relationship like:

                 

                Parent::Category = Child::RepeatingCategory

                 

                any child record that has the specified category (entered into the Parent::Category field) in ANY repetition of the RepeatingCategory field will be considered a related record.

                 

                 

                BTW, the same will be true if you use checkboxes instead of a repeating field.


                • 5. Re: Calculation or relationship for if "field contains"
                  jamie.hunt
                     Thank you for your help guys. Being new to FileMaker, it's helped me out.