5 Replies Latest reply on Feb 2, 2015 8:53 PM by paolobkk

    Sum Listed values in field issue

    paolobkk

      Title

      Sum Listed values in field issue

      Post

      Hi everyone,

      I have a text field with multiple values to record every time a user input a quantity number in a warehouse.

      The field records just a history line (+20-10+15-5+12....).

      I need to sum up all these values and I've managed to place them in a field, clean then up and separate them by a carriage return.

      However, the SUM function does not give me the sum but a single line with all the values as it was a text. (see attachment.

      the 2 involved fields are set as NUMBER and I can't figure out what is wrong here.

      It seems FM don't see the carriage returns as repetitions but as a unique value (or list). If that so, is there any way to sum a list of values like in this case?

      Thank you for any suggestion!

      Paolo

      (FMP11 on FMS11 - Win and Mac)

      fm_test.png

        • 1. Re: Sum Listed values in field issue
          paolobkk

          After getting temporarily desperate and researching and testing around the internet different solution I've found this to save the day.

          Create a custom function like this:

          Let( [
              myList = 0 & ¶ & myList & ¶ & 0 ;
              it = Substitute( myList ; [ ¶ ; ";" ] ; [ ";;" ; ";" ] ) ;
              result =   Evaluate( "Sum(" & it & ")" ) 
                 ] ; 
           
             result   
            )

          It will sum up the values listed in the single field.

          You can find more details here: http://www.briandunning.com/cf/1309

          Cheers!

          • 2. Re: Sum Listed values in field issue
            philmodjunk

            Yet what you have entered is very vulnerable to user data entry errors and overly complicated.

            Simply setting up a system were each value can be entered into a different field of a related record will reduce the chance of data entry errors and results of ? when the data entered has an error and evaluate can't evaluate the resulting expression.

            • 3. Re: Sum Listed values in field issue
              paolobkk

              Hi Phil, thank you for replying.

              Actually the fields you see in the screenshot are accessible only by me as admin. The values are normally placed by a script, users cannot manually access these fields.

              Users input data only using a dialog box with a global field and the script place a note of their input in that field (first on the left). It's just to keep a simple history of inserted data and is not normally used for calculations.

              The problem is that users are supposed to input one number at the time using the dialog box, but for some reason they started to input the whole line of numbers in the dialog box (eg.: 1+180+840-1).

              At every input, the script add the quantity number to the total quantity field. That's why when users typed "1+180+840-1" in one time the calculation failed.

              The above custom function worked well after I've managed to list the values in a temporary field (using "replace" and calculations, nothing by hand). But it was just a 1 time correction.

              Now I need to find a way to let the first dialog box script to check the inserted number to be ONLY one number (or one negative number).

              eg: "10" or "-10" only. Not "10+20+30-5".

              I appreciate any help on this.

              Cheers

              • 4. Re: Sum Listed values in field issue
                philmodjunk

                It is still a needlessly complex format and if your users mistype the expression to produce an invalid expression, you'll have a problem. Another problem that arises is that this format makes it very difficult to audit the data entered for possible user errors. (That's one of reason that bookkeeping ledgers were invented hundreds of years ago...)

                You can set up a wide range of data entry forms for data entry--including the creation of your own form a custom dialog for data entry and each value entered should be stored--whether by script or by direct user entry into a series of individual records. The values can readily be summed via expressions such as Sum ( RelatedTable::NumberField) and your custom designed dialog can include validation expressions or a validation script that catches and rejects user errors such as entering multiple values into a single text field.

                • 5. Re: Sum Listed values in field issue
                  paolobkk

                  I understand your point and you are right. That's why I don't want them to type any expression in the dialog box (never meant to be for expressions).

                  I need to find a way to validate the custom dialog only if the input field has one number (which can be also negative) and proceed with the script.

                  Do you have any hint on this please?