12 Replies Latest reply on Oct 16, 2015 11:50 PM by erolst

    add up fields with an X in them

    cmahan

      I have a small database of about 500 records.  Each record has a set of fields (letter1, letter2, letter3) etc and some of the fields have an X if the letter was signed.  I would like to know how many letters each record has signed.

       

      So, for instance, Record One has 4 fields marked with an X (out of 6) and that number would show up in a calculation  or some other type of field with the number 4.

       

      I've used Filemaker for a long time and I can do basic things but I've never been able to to do calculations (would love a recommendation for a book that walks you through it with really good basic examples).  I was able to do summary fields at one time but I don't think that what's I want here.

       

      Any help is appreciated.

       

      Connie

        • 1. Re: add up fields with an X in them
          coherentkris

          If you have fields named letter1 letter2 letter3 in the same table I suggest that you should read up on data normalization.

          A well designed database will rarely have numbered fields.

           

          Having said that if the choices in your check fields are "X" and empty then Count ( Field1 ; Field2 ; Field3 ) = 3 when all three fields have "X".

          I recommend the FielMaker training series

          • 2. Re: add up fields with an X in them
            siplus

            Are the fields numeric or text ? Do you autoenter a value in them ?

            • 3. Re: add up fields with an X in them
              cmahan

              Well actually, my fields are called sage-grouse 2014, ns carbon letter 2015, etc.  I used letter1, letter2 etc. as a simplified example. 

               

              The choices are X or empty, so you are correct there.

               

              When I try your example:

               

              Count (Global Warming Adaptation Oct 2010 ; Interior Eagle Permit Jul 2012 ; NS Carbon Rule Jun 2012 ; Funding Mar 2011 ; sage grouse Dec 2014) =

              (calculation result is number, number of repetitions is 1 (not sure whether this is relevant) and click ok

               

              I get this error message: A number, text constant, field name or “(” is expected here.

               

              Connie

              • 4. Re: add up fields with an X in them
                cmahan

                Fields are text fields, some are populated with X, some are blank and they are not auto entered. The values there now were imported from a spreadsheet.

                 

                Connie

                • 5. Re: add up fields with an X in them
                  siplus

                  Fields are text fields, some are populated with X, some are blank and they are not auto entered. The values there now were imported from a spreadsheet.

                   

                  then define a calculation field, with formula =

                   

                  Length(Field1 & Field2 & ... & Fieldn)

                  • 6. Re: add up fields with an X in them
                    cmahan

                    Yowza, I think that worked!  Length -- of course, so obvious! 

                     

                    Thanks very much.

                     

                    Connie

                    • 7. Re: add up fields with an X in them
                      erolst

                      cmahan wrote:

                      Yowza, I think that worked!  Length -- of course, so obvious! 

                       

                      Actually, it's not obvious; if you'd ever change the method to flag a field (namely, one that uses multiple characters), you may get a different result that differs from the number of non-empty fields. The previously suggested method of Count ( field1 ; field2 { ; …} ) will always work correctly, no matter what values you use.

                       

                      That being said, you should either use a join table or a single field to express the fact that a parent record has signed specified letters; then you can use either Count ( JoinTable::id ), or ValueCount ( listField ), without having to create new fields or change your method to count.

                      • 8. Re: add up fields with an X in them
                        cmahan

                        These are pretty static fields and I don't anticipate needing to change them from a simple X to anything else.  I couldn't get the Count method to work -- got the aforementioned error message ( A number, text constant, field name or “(” is expected here) and as for the rest, I'm afraid you've lost me -- but that's ok, I've got what I need from the length calculation, although I would like to know why the Count is not working.  But I REALLY  appreciate all the help. Getting even the workaround solution of Length is a huge help as I have a number of other activity fields populated with X that I can now tally up into a nice summary format and that is exactly what I needed.  Thanks!

                        • 9. Re: add up fields with an X in them
                          erolst

                          cmahan wrote:

                          I couldn't get the Count method to work -- got the aforementioned error message

                           

                          Was there a trailing '=' (equal sign) after the closing parenthesis? If so, remove it. The calculation should simply read

                           

                          Count ( yourField1 ; yourField2 …)

                           

                          cmahan wrote:

                          Getting even the workaround solution of Length is a huge help

                           

                          siplus's suggested method works, but it isn't the best method, because it will not work correctly under all circumstances – and so isn't really “correct”.

                          • 10. Re: add up fields with an X in them
                            siplus

                            There is no absolutely correct method, but if you have a water hose connected to a single faucet and you preach correctness as reacting to the possibility that hot water will come down your hose, you are wasting your time. And the reader's, too.

                             

                            Concrete, well-defined problems with no sidesteps deserve precise, working answers. Inside this sentence lies a new definition of "correctness" that you might want to investigate further, philosophically speaking.

                            • 11. Re: add up fields with an X in them
                              DavidJondreau

                              Based on your field names, and your question, it's advisable to read up on "data normalization". The way the tables and fields are set up will cause problems if you try to add features.

                              • 12. Re: add up fields with an X in them
                                erolst

                                siplus wrote:

                                There is no absolutely correct method, but if you have a water hose connected to a single faucet and you preach correctness as reacting to the possibility that hot water will come down your hose, you are wasting your time. And the reader's, too.

                                This is bad, even for an analogy …