10 Replies Latest reply on Jul 19, 2010 2:13 PM by philmodjunk

    Summary and calculation fields don´t work

    PabloToscano

      Title

      Summary and calculation fields don´t work

      Post

      Some times summary fields do not work,  when used for calculating fields placed in a linked or a related table. As you can see in the image. The summary field is being used to to calculate the total of fields which in turn calculate the unitary price of an article multiplied by the number ordered. The summary field not working is under the name of "Gastos", and only represents the amount of the last customer not the total of the found set

      tables.png

        • 1. Re: Summary and calculation fields don´t work
          philmodjunk

          Please describe your tables and how they are linked in a relationship. Please indicate in which table, you've defined a summary field for "Gastos" and the specific options you've selected for it.

          • 2. Re: Summary and calculation fields don´t work
            PabloToscano

            The summary field "Gastos" is in the table "Gastos copiar 2", and is a summary of a field within the same table, named "total_gastos_evento" which is a calculation field that adds up, a bunch of numeric fields created in the same table "gastos copiar 2" and a couple of numeric fields in "Regalos" and "Nomina copiar" tables 

            • 3. Re: Summary and calculation fields don´t work
              etripoli

              Try using a calculated field in the parent table, that sums the values from the records in the related table.  Here's my rule-of-thumb:

              Use a Summary field if:

              It needs to go in a sub-summary, header, or footer layout part

              It needs to be in the the same table as the data it is summing

              Use a Calculated field containing the Sum() function if:

              The summary field needs to be displayed in the parent record

              • 4. Re: Summary and calculation fields don´t work
                philmodjunk

                To repeat: Please describe your tables and how they are linked in a relationship.

                You've indicated that this is a related table it's important to know how it is related to the table specified in layout setup... of the layout of which you posted your screen shot.

                • 5. Re: Summary and calculation fields don´t work
                  PabloToscano

                  I hope I understood the question. All tables are related using a numerical field thats corresponds to the customer number ID, they all have this field and I used it to create the relationship between them. I have no problems when creating a portal or just copying fields from one table to another. the only thing is that calculation fileds do not work properly, when used in a lay out or report that differs from the table calculation fields were created in the first place.   

                  • 6. Re: Summary and calculation fields don´t work
                    philmodjunk

                    Your tables and relationships have a great deal to do with how summary fields and calculation fields will work. The design of your layout is another factor.

                    So far, I can tell you have more than one table an that they all are linked by a customer ID field. One of these is in a table called "Gastos copiar 2" ( is that the table name in Manage | Database | Fields or the name of the table occurrence box in Manage | Database | relationships? ) and this is apparently a different table than that on which your layout is based.

                    That's really not much to go on to figure out why your field isn't computing as you expect it to.

                    Are there many related records in "Gastos copiar 2" for each record in your layout's table?
                    Is this a self join to the same table and "Gastos copiar 2" is a 2nd table occurrence.

                    • 7. Re: Summary and calculation fields don´t work
                      PabloToscano

                      Fisrt of all,  I really want to thank you for your help, my lack of resources in communicating in english,  have made it more difficult than it should be  (too technical for me).

                      Yes, "gastos copiar 2" is the name of the table in Manage/database/fields  (tables) , and you are correct,  the layout is based on "BOSSA NUEVE" (a different table). Again yes, there are many related records in "gastos copiar 2" in my layout. I have no 2nd table occurrences at all,  all tables are independent, and have the same name in the manage /database/relationship, as theya are named in manage/database/fields (tables) exactly as a named them when I created them

                      • 8. Re: Summary and calculation fields don´t work
                        philmodjunk

                        That should work for you. As Etripoli suggests, you can also define a calculation field in Bossa Nueve that uses the sum function, but either approach should work. Here's a demo file that uses both a summary field in the child table (corresponds with your "gastos copiar 2") and a calculation field defined in the parent table (Bossa Nueve in your file):  http://www.4shared.com/file/6W8iI8Rd/RelatedSummaryField.html

                        See if you can spot any differences that explain why this isn't working for you.

                        • 9. Re: Summary and calculation fields don´t work
                          PabloToscano

                          I am puzzled, As in the example you sent me,  and as well as  etripoli suggested, I tried using a calculation field in the parent table using sum(gastos copiar 2::total_gastos_eventos) I get a result, but only for one record at the time. If I perform a search,  and see the result for the found set, the field is blank in the lay out report of the parent table. 

                          • 10. Re: Summary and calculation fields don´t work
                            philmodjunk

                            "I get a result, but only for one record at the time."

                            Check your values in the fields that link your two tables. That result suggests that only one record in "gastos copiar 2" links to the current record in the other table.

                            "If I perform a search,  and see the result for the found set, the field is blank in the lay out report of the parent table. "

                            On which table are you performing the find (That's determined by your layout), with what criteria, when and where do you see the blank field?