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?
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 )
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.
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.
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?
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.
=sum ( table 1::field 1 )?