7 Replies Latest reply on Nov 24, 2015 5:51 AM by Extensitech

    Calculation Field

    ms.15

      I have a number of records based on Table 1. On each record there is a field, Field 1, which is a Calculation Field, containing a value.

       

      On Table 2 I want to add all values from Field 1 and put the new value into Field 2, another Calculation Field.

       

      The two tables are related with a foreign key on Table 2 linking to a primary key on Table 1.

       

      What would the Script for Field 2 look like?

        • 1. Re: Calculation Field
          mikebeargie

          If Table1::PrimaryKey = Table2::ForeignKey

           

          Then your statement doesn't really make sense. That relationship is one (table1) to many (table2), so while table1 can have many table2 records associated with it, each table2 record only has one table1 record associated with it.

           

          What are the contents of table1::field1, and what is your goal for data to display in table2::field2?

          • 2. Re: Calculation Field
            jgomes384

            If I am understanding your question, you could set Field2 to an ExecuteSQL statement like the following:

             

            ExecuteSQL ( "SELECT SUM(Field1) FROM Table1 WHERE Table1ForeignKey = ?" ; "" ; "" ; Table2ForeignKey )

            • 3. Re: Calculation Field
              siplus

              Do you want to add ALL values from Field1, regardless of relationships ? That's what you say, not sure if that's what you mean.

              • 4. Re: Calculation Field
                Extensitech

                I may be reading this wrong, but try creating a calc field in table 2 with the function List ( Table 1::Field 1 ). That'll get you a list of the values in table 1::field 1 related to this record in table 2.

                 

                Or am I just way off base? As others have implied, it's a little difficult to be sure what you're trying to achieve.

                 

                Chris Cain

                Extensitech

                • 5. Re: Calculation Field
                  ms.15

                  Thank you all for good answers. Let me explain in another way:

                   

                  On one layout I have monthly expenses, Field 1.

                   

                  On another layout I have Field 2 where I want a total of all values from Field 1.

                   

                  How would this be set up?

                  • 6. Re: Calculation Field
                    mikebeargie

                    If your relationship is setup properly, you can use Sum(expenses::Field 1) from the other table. But as noted above it sounds like you may not have the relationship setup properly.

                    ExecuteSQL("SELECT SUM(Field1) FROM expenses";"";"")

                    is another option that just gets the sum of field1 regardless of relationship. Furthermore, you could modify the syntax in the above to conditionally pull results, like:

                    ExecuteSQL("SELECT SUM(Field1) FROM expenses WHERE expenseDate > ?";"";""; "12/31/2014" )

                    That would get a sum of all expenses for the current year.


                    Post some screenshots from your relationship graph highlighting your expenses table and the target table where you want to put your calculated field, this would help us make further recommendations.

                    • 7. Re: Calculation Field
                      Extensitech

                      =sum ( table 1::field 1 )?

                       

                      Chris Cain

                      Extensitech