3 Replies Latest reply on Sep 1, 2010 1:08 PM by philmodjunk

    Calculations with fields from different tables



      Calculations with fields from different tables


      Hi, In my application I need to calculate with fields from different tables. In MS-Access I would have done this in VB, but I cannot see how to realise this in FileMakerPro. Can anyone explain to me ? Thanks in advance, Marc

        • 1. Re: Calculations with fields from different tables

          FileMaker gives you two options and when the values are in different tables, the results of the two options are different. With both approaches, you need a relationship linking the two tables in Manage | Database | Relationships so FileMaker knows which record in the second table holds the value to be used in the calculation.

          The best approach for most situations is to simply define a calculation field. If you need, for example to add the value in one field with the value in a field in a related record, you'd do this:

          Field + relatedTable::Field2

          The result is unstored and if there are multiple matching records in the related table, this expression uses the value of the "first" matching record. Which record is first depends on how the relationship is defined. If a sort order is defined for the relationship, that order determines which record is "first". With no sort order the first record, the first such record created is first. The downside to this calculation is that it will always be unstored. Searches and sorts on it will take longer.

          You can also define an auto-enter calculation on a data field. This can be a stored and indexed field for quicker sorts and finds. The catch here is that the calculation will evaluate correctly when the record is first created and when field 1's value is changed, but if field2 is updated, this calculation will not update automatically and this can be an undesirable result.

          • 2. Re: Calculations with fields from different tables

            So does this means there's no equivalent of the "select ... where ..." in FileMaker ?

            What about having more several instances (table 2, table 3, ...) of the same tables each time in different relationships, does that courses problems ?

            • 3. Re: Calculations with fields from different tables

              The WHERE clause in SQL defines the record set returned--not how a field calculates (except for summary calculations). In FileMaker, this is similar to performing a find to generate a found set. The Select section of a SQL expression, where you specify "join" links to other tables, is the rough equivalent of the relationships you define in Manage | Database | Relationships to link related tables of information.

              The biggest difference is that you define calculation fields in the table definition instead of in an SQL query. This is quite different from Acces and has it's own advantages and disadvantages when compared to how this is done in SQL.

              Each "instance" you refer to is called a table occurrence in FileMaker speak and each has its own box with a unqiue name in Manage | Database | Relationships. In your calculations, you refer to the table occurrence name followed by the field and your relationship definition then controls which record in the other table supplies the value for the calculation. There is a "from the context of" drop down that you sometimes need to use to specify the "starting point of reference" needed in order for a given calculation to work the way you need it to.

              This thread may help get you up to speed on table occurrences: Tutorial: What are Table Occurrences?