13 Replies Latest reply on Mar 18, 2010 5:21 PM by Dkesh

    How can I use checked values from a check box set in a calculation?

    HildeBoee

      Title

      How can I use checked values from a check box set in a calculation?

      Post

      <!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;w:WordDocument&gt;   &lt;w:View&gt;Normal&lt;/w:View&gt;   &lt;w:Zoom&gt;0&lt;/w:Zoom&gt;   &lt;w:HyphenationZone&gt;21&lt;/w:HyphenationZone&gt;   &lt;w:PunctuationKerning/&gt;   &lt;w:ValidateAgainstSchemas/&gt;   &lt;w:SaveIfXMLInvalid&gt;false&lt;/w:SaveIfXMLInvalid&gt;   &lt;w:IgnoreMixedContent&gt;false&lt;/w:IgnoreMixedContent&gt;   &lt;w:AlwaysShowPlaceholderText&gt;false&lt;/w:AlwaysShowPlaceholderText&gt;   &lt;w:Compatibility&gt;    &lt;w:BreakWrappedTables/&gt;    &lt;w:SnapToGridInCell/&gt;    &lt;w:WrapTextWithPunct/&gt;    &lt;w:UseAsianBreakRules/&gt;    &lt;w:DontGrowAutofit/&gt;   &lt;/w:Compatibility&gt;   &lt;w:BrowserLevel&gt;MicrosoftInternetExplorer4&lt;/w:BrowserLevel&gt; &lt;/w:WordDocument&gt; &lt;/xml&gt;&lt;![endif]  --><!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;w:LatentStyles DefLockedState=&quot;false&quot; LatentStyleCount=&quot;156&quot;&gt; &lt;/w:LatentStyles&gt; &lt;/xml&gt;&lt;![endif]  --><!--  [if !mso]&gt;&lt;object classid=&quot;clsid:38481807-CA0E-42D2-BF39-B33AF135CC4D&quot; id=ieooui&gt;&lt;/object&gt; &lt;style&gt; st1\:*{behavior:url(#ieooui) } &lt;/style&gt; &lt;![endif]  --><!--  /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal      {mso-style-parent:&quot;&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:10.0pt;      font-family:&quot;Times New Roman&quot;;      mso-fareast-font-family:&quot;Times New Roman&quot;;} @page Section1      {size:595.3pt 841.9pt;      margin:70.85pt 70.85pt 70.85pt 70.85pt;      mso-header-margin:35.4pt;      mso-footer-margin:35.4pt;      mso-paper-source:0;} div.Section1      {page:Section1;}   --><!--  [if gte mso 10]&gt; &lt;style&gt; /* Style Definitions */ table.MsoNormalTable      {mso-style-name:&quot;Vanlig tabell&quot;;      mso-tstyle-rowband-size:0;      mso-tstyle-colband-size:0;      mso-style-noshow:yes;      mso-style-parent:&quot;&quot;;      mso-padding-alt:0cm 5.4pt 0cm 5.4pt;      mso-para-margin:0cm;      mso-para-margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:10.0pt;      font-family:&quot;Times New Roman&quot;;      mso-ansi-language:#0400;      mso-fareast-language:#0400;      mso-bidi-language:#0400;} &lt;/style&gt; &lt;![endif]  -->

      Hello,

      I'm using Filemaker Pro 9. I have a file containing information about historical persons from all over Europe. As you know, the number of names in one person's full name, vary a great deal. Some have only one name, others have several. We keep each name in its own field:

       

      first name

      nickname

      middle name 1

      middle name 2

      middle name 3

      middle name 4

      middle name 5

      surname

       

      We want to differentiate between the full name and the version used by and about the person, i.e. the name that the person is or was known by, mostly this is first name + surname. To complicate this comes the fact that some persons used one of their middle names or a nickname as their first name. We have therefore created a checkbox set where we check the names that constitute “the name that the person is known by”.

       

      Now I would like to add two calculation fields to the file, both which can aggregate the checked names, one in the pattern “Firstname Surname”, and one in the opposite pattern, “Surname, Firstname”. But how can I do this? Can I do it using calculation fields? Is there any other way to do it?

       

      I’ve tried, but I can’t seem to get anything to work. Does anyone here have some advice or a solution to suggest?

      Many thanks in advance,

      kind regards Hilde

        • 1. Re: How can I use checked values from a check box set in a calculation?
          ninja
            

          Howdy HildeBoee,

          Welcome to the forum,

           

          Not having time to compress this into a single expression (I think it might be possible, but I'm not sure), let me outline a way that will work for you.  Perhaps another will simplify my approach.

           

          Have a set of calculation fields, one for each name part:

          cFirstName = If ( Filtervalues (checkboxfield ; FirstName ) = "FirstName" ; FirstNameField & " " ; "" )

          cNickname = If ( Filtervalues (checkboxfield ; Nickname ) = "Nickname" ; NicknameField & " " ; "" )

          etc...

           

          Then assemble your names in your two calculation fields:

          cFirstName & cNickname & cMiddleName1 & cMiddleName2 & ..... & cSurname

           

          and

           

          cSurname & ", " & cFirstName & cNickname & cMiddleName1 & cMiddleName2 & .....

           

          Note: "checkboxfield" is whatever field you are using for your checkbox set, "FirstName" and "NickName" are my assumed values within the checkbox field values, "FirstNameField" etc. is the field that you have the name itself stored in.

          cFirstname is a calc field showing the calculated value based on the checkbox (either FirstName or blank).

          When you compile them all together, the calc fields corresponding to names that weren't checked will be blank and thus not appear to be included.

           

          Check out FilterValues in the help screens and think through this approach.  It will work for you, but it is always best if you understand HOW it works...ask all the question you want if you get stuck.

          • 2. Re: How can I use checked values from a check box set in a calculation?
            HildeBoee
              

            Hello, and thanks!

            Unfortuneately I haven't been able to make your approach work yet. I've tried constructing calculations a little myself as well, and read the help sections, but without any luck so far.

            I'll keep trying, but if you have further ideas or suggestions, I'm happy to hear them.

            Best, Hilde

            • 3. Re: How can I use checked values from a check box set in a calculation?
              HildeBoee
                

              Hi again,

              I tried substituting FilterValues with Filter and that brought me a little bit further. I used your patterns:

               

              cFirstName = If ( Filter (checkboxfield ; "FirstName" ) = "FirstName" ; FirstNameField & " " ; "" )

              cNickname = If ( Filter (checkboxfield ; "Nickname" ) = "Nickname" ; NicknameField & " " ; "" )

               

              (I needed to put quotes around the string to filter it because if I didn't, Filemaker treated it as a field.)

              For the first value in the check box I get the results I want, but then, and it seems it's because of the = sign, in the other calculation fields I don't get the checked names.

              How can I say "If you find the string FirstName, give me the content of the FirstNameField field" without making it impossible to fetch the content of the fields matching the other checked values?

              Do you have any ideas to how I can procede with this?

              Thanks!

              Regards, Hilde

               

              • 4. Re: How can I use checked values from a check box set in a calculation?
                ninja
                  

                Howdy HildeBoee,

                 

                I'm pretty sure you want to stick with the FilterValues function.  Check through the help screens and compare to the approach suggested:

                 

                1. Have a calculation field for EACH name part (6 calc fields to filter, 2 calc fields more to reassemble)

                2. Your checkbox field will have multiple values (FirstName, SurName, MiddleName1, Nickname, etc.)

                3. For each name part, you want to check to see if that particular string is in the list of values

                4. If it is, set the calc field to hold the contents plus a space...otherwise, leave it blank.

                5. Combine the calculated field values (the 2 calc fields to reassemble).

                 

                If you use the Filter function, like

                cNickname = If ( Filter (checkboxfield ; "Nickname" ) = "Nickname" ; NicknameField & " " ; "" )

                and the checkbox set has "FirstName" AND "NickName", your filtered result should be "iNameNickName" which is not useful.

                 

                FilterValues looks for a specific string in entirety, not just the component characters like Filter does.

                 

                Check to make sure that the string you are looking for ("FirstName", etc.) is exactly what you have as options in your checkbox set (as typed in your Custom Value List).  If you are looking for "Firstname" when your value list has "First_Name" it won't work.  You are right about the quotation marks...sorry for leaving that out.

                 

                Just to be sure we are connecting, I'm talking about having SIX filtering calculation fields, one for FirstName, one for NickName, one for SurName...

                Each of these calculation fields would have a single If statement containing the FilterValues analysis.

                • 5. Re: How can I use checked values from a check box set in a calculation?
                  HildeBoee
                    

                  Thanks, I will try once again with FilterValues then :-)

                  Yes, I have one calculation field for each of the name fields and two more for reassembling the names.

                  I'll let you know how it goes!

                  Best, Hilde

                  • 6. Re: How can I use checked values from a check box set in a calculation?
                    HildeBoee
                      

                    Hi Ninja,

                    I've now tried with FilterValues, and this time it works! :-)

                    I had to subtitute = with ≥ though, because = returns nothing when the checkboxfield contains more than the string I want to find. I use this pattern:

                     

                    cNickname = If ( Filter (checkboxfield ; "Nickname" ) ≥ "Nickname" ; NicknameField & " " ; "" )

                     

                    With ≥ it seems I always get the string I want.

                    Thanks very much for your help!

                    Best, Hilde

                    • 7. Re: How can I use checked values from a check box set in a calculation?
                      ninja
                        

                      Howdy Hilde,

                       

                      Hmmmm.....very interesting...I'm not sure what logical reason there would be for the 'greater or equal' symbol to work when comparing text when the = sign doesn't.  And not sure why the = sign doesn't work for you...in my brain it should.  FMP doesn't always follow my brain, and we can all be thankful for that!

                       

                      Perhaps another on the forum with more in depth experience with 'FilterValues' could explain why it is working this way in your case.

                       

                      Anyway, I'm glad you are ending up with what you were after.  If it were me, I'd test it pretty substantially with fake cases since there is something unexplained going on (likely not unexplainable, but unexplained for now ;) )

                      • 8. Re: How can I use checked values from a check box set in a calculation?
                        philmodjunk
                          

                        Well Hilde is using Filter, not FilterValues and that's why = isn't working. Consider this example:

                         

                        Filter ( "appleoranges" ; "Apple")

                         

                        Will return: appleae

                         

                        The characters in the second parameter are filtering a selection of characters from the rest of the text in the field to give you a bit more than what's expected.

                        • 9. Re: How can I use checked values from a check box set in a calculation?
                          ninja
                            

                          Ummm, yeah...


                          HildeBoee wrote:

                           

                          I've now tried with FilterValues, and this time it works! :-)

                           

                          cNickname = If ( Filter (checkboxfield ; "Nickname" ) ≥ "Nickname" ; NicknameField & " " ; "" )


                          ....... hmmm ......


                          • 10. Re: How can I use checked values from a check box set in a calculation?
                            mrvodka
                              

                            Filter ( "appleoranges" ; "Apple")

                             

                            Will return: appleae

                             


                            Not quite. It will return: pplee

                            • 11. Re: How can I use checked values from a check box set in a calculation?
                              philmodjunk
                                

                              Yeah, it's case sensitive. I forgot to change Apple to apple when I was typing the original post.

                               

                              I meant to enter:

                               

                              Filter ( "appleoranges" ; "apple")

                              • 12. Re: How can I use checked values from a check box set in a calculation?
                                HildeBoee
                                  

                                Ooops, my fault. The calculation should read:

                                 

                                cNickname = If ( FilterValues (checkboxfield ; "Nickname" ) ≥ "Nickname" ; NicknameField & " " ; "" )

                                 

                                because that was what worked and still works. Yes, I will keep an eye on the calculations to be sure they do produce what I wanted.

                                Thanks again!

                                Hilde :-)

                                • 13. Re: How can I use checked values from a check box set in a calculation?
                                  Dkesh

                                  There reason it works when use greater than or equal to symbol is filter values return the text to filter with the paragraphsymbol

                                  example

                                  Filtervalues(checklistfield; "BOB")

                                  returns

                                  BOB¶

                                  So if u use IF(FilterValues(checklistfield;"BOB")="BOB¶";1;0)

                                  This will be the apt solution for your problem:smileyhappy: