11 Replies Latest reply on Aug 1, 2012 4:08 PM by Jmcl07

    Sum of Numerals Within a Field

    Jmcl07

      Title

      Sum of Numerals Within a Field

      Post

      Hi Guys,

      I am a scorer for horse events, and new to Filemaker Pro. I wanted to know if it is possible sum mutiple numerals within one field. In competitions we get dressage tests with many marks for movements and it would be easier to enter and total these marks in one field. Others have been able to do this on access but I cant as I use a mac so I am trying to make my own DB. 

      The situation is that we will receive a test with many marks that we would put in eg 767675489 and the access would give a total of these numbers. It also took into account some scores which needed to be doubled, eg score 2 needs to be doubled so 5455 would give a total of 23. It was also able to consider Half marks where a + was put after a number to add an extra 0.5, eg 5555+ would give a total of 20.5

      Is this posible to achieve in Filemaker Pro?

      Thanks for your help :) 

        • 1. Re: Sum of Numerals Within a Field
          philmodjunk

          It's possible, but given that your layout can contain a row of fields just wide enough to hold 1 or two digits and entering data in one can automatically move the cursor to the next, it seems needlessly complex.

          How does one tell that the second digit must be doubled? Is it that the second digit is always doubled?

          And do you have FileMaker Advanced?

          • 2. Re: Sum of Numerals Within a Field
            Jmcl07

            I Do have FileMaker Advanced.

            Whether the digit is doubled depends on the dressage test, an that can occur at various different marks depending on the test.

            The only reason I say this option is that I have used it before in access, and when you have about 15 - 30 scores from 2 different judges for two or more different tests, the number of fields may be a bit cumbersome for me to manage 

            • 3. Re: Sum of Numerals Within a Field
              Jmcl07

              Oh and to make it a little more complex, if a rider gets given a mark of ten, that must be considered as the DB may read that as a 1 and a 0.

              In the previous system an asterix after the 10 took care of it.

              Thanks, 

              • 4. Re: Sum of Numerals Within a Field
                davidanders

                Create a calculator interface with the button scripts entering data into fields with preferred format?

                • 5. Re: Sum of Numerals Within a Field
                  philmodjunk

                  This can all be done in FileMaker, it's a matter of implementation.

                  A portal of related records with one record for each score and pressing the number keypad's enter key can take the cursor to the next portal row to make data entry very rapid and greatly simplify the computation process. An auto-enter calcualtion can turn a + into 0.5.

                  Not only does putting all the digits in a single screen greatly complicate the calculation process, it raises a serious chance of data entry error if the wrong score gets doubled simple because a number doesn't get entered, a key "bounces" or someone enters 10 instead of 10*.

                  If you want to continue with the current data entry format, you can use advanced to set up a custom function that sums the digits, but I am still not clear how to determine which digits should be doubled. Some indicator has to be inplace (and this would be the case in Access as well) so that the computer can double only the digits that need to be doubled and not the others when summing them up.

                  This custom function does NOT double anything, but does sum the digits, substituting 0.5 for + and 10 for 10*:

                  //DressageSum ( Scores )
                  //
                  Case ( IsEmpty ( Scores ) ; 0 ;
                             Left ( Scores ; 3 ) = "10*" ; 10 + DressageSum ( Right ( Scores ; Length ( scores ) - 3 ) ;
                             Left ( Scores ; 1 ) = "+" ; 0.5 + DressageSum ( Right ( Scores ; Length ( scores ) - 1 ) ;
                             Left ( Scores ; 1 ) + DressageSum ( Right ( Scores ; Length ( scores ) - 1 )
                            ) // case

                  • 6. Re: Sum of Numerals Within a Field
                    Jmcl07

                    Thanks for this!

                    If it of any help, the access database has a field for the test that contains the multiplication for each of the movements eg 1111121122, Is there a way to just multiply this with the digits in the sum field?

                    As for error, at a competition we have to do two adds on sheets. So a person will do the first add on a caluclator, so that when it come to putting this into the database if someone does put it in incorrectly we will know about it. Its a way to ensure both are correct.

                    Thanks again.

                    • 7. Re: Sum of Numerals Within a Field
                      philmodjunk

                      It's your database, but an interface that make errors less likely sounds like a better way to go than relying on the calculater for a cross check to catch them.

                      Yes, you could pass that string as a parameter to the custom function and it could be modified to be used as a multiplier within the calculation.

                      //DressageSum ( Scores ; MultiplierString )
                      //
                      Let ( rString = Right ( MultiplierString ; Length ( MultiplierString ) - 1 ) ;

                      Case ( IsEmpty ( Scores ) ; 0 ;
                                 Left ( Scores ; 3 ) = "10*" ; 10 * Left ( MultiplierString ; 1 ) + DressageSum ( Right ( Scores ; Length ( scores ) - 3 ) ; rString ) ;
                                 Left ( Scores ; 1 ) = "+" ; 0.5 * Left ( MultiplierString ; 1 ) + DressageSum ( Right ( Scores ; Length ( scores ) - 1 ) ; rString ) ;
                                 Left ( Scores ; 1 )* Left ( MultiplierString ; 1 ) + DressageSum ( Right ( Scores ; Length ( scores ) - 1 ) ; rString )
                                ) // case

                             ) // Let

                      • 8. Re: Sum of Numerals Within a Field
                        Jmcl07

                        Thats working fine!

                        Just having a problem, when I add a + for a half point it pushes out the multiplier string by a digit?

                        Is there a way to fix that?

                        Thanks

                        • 9. Re: Sum of Numerals Within a Field
                          philmodjunk

                          I had assumed that + meant the entire score was 0.5.

                          I gather that 89+8

                          would be three scores: 8, 9.5 and 8

                          If so, then when a + is encountered, it shouldn't 'peel off' a multiplier digit when it makes the recursive call.

                          //DressageSum ( Scores ; MultiplierString )
                          //
                          Let ( rString = Right ( MultiplierString ; Length ( MultiplierString ) - 1 ) ;

                          Case ( IsEmpty ( Scores ) ; 0 ;
                                     Left ( Scores ; 3 ) = "10*" ; 10 * Left ( MultiplierString ; 1 ) + DressageSum ( Right ( Scores ; Length ( scores ) - 3 ) ; rString ) ;
                                     Left ( Scores ; 1 ) = "+" ; 0.5 * Left ( MultiplierString ; 1 ) + DressageSum ( Right ( Scores ; Length ( scores ) - 1 ) ; MultiplierString ) ;
                                     Left ( Scores ; 1 )* Left ( MultiplierString ; 1 ) + DressageSum ( Right ( Scores ; Length ( scores ) - 1 ) ; rString )
                                    ) // case

                                 ) // Let

                          • 10. Re: Sum of Numerals Within a Field
                            philmodjunk

                            Hmmm, even that won't quite work due to the fact that it's using a multiplier for the next score when a + is encountered and this won't always be the correct multiplier. 2+ might add up as 4.5 instead of 5 or 3 instead of 2.5.

                            //DressageSum ( Scores ; MultiplierString )
                            //
                            Let ( rString = Right ( MultiplierString ; Length ( MultiplierString ) - 1 ) ;

                            Case ( IsEmpty ( Scores ) ; 0 ;
                                       Left ( Scores ; 3 ) = "10*" ; 10 * Left ( MultiplierString ; 1 ) + DressageSum ( Right ( Scores ; Length ( scores ) - 3 ) ; rString ) ;
                                       Middle ( Scores ; 2 ; 1 ) = "+" ; ( Left ( Scores ; 1 ) + 0.5 ) * Left ( MultiplierString ; 1 ) + DressageSum ( Right ( Scores ; Length ( scores ) - 2 ) ; rstring ) ;
                                       Left ( Scores ; 1 )* Left ( MultiplierString ; 1 ) + DressageSum ( Right ( Scores ; Length ( scores ) - 1 ) ; rString )
                                      ) // case

                                   ) // Let

                            • 11. Re: Sum of Numerals Within a Field
                              Jmcl07

                              Works Perfectly, Thank You!!!