This is a public Forum  publicRSS

Forum post

    Marc Calders
    Calculations with fields from different tablesAnswered
    Forum post posted September 1, 2010 by Marc Calders, last edited February 9, 2012 
    812 Views, 3 Comments
    Title:
    Calculations with fields from different tables
    Post:

    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

    Best Answer

    PhilModJunk

    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: http://forums.filemaker.com/posts/3d8b364a6c

    Answer

     

    • PhilModJunk

      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.

    • Marc Calders

      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 ?

    • PhilModJunk

      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: http://forums.filemaker.com/posts/3d8b364a6c