Filemaker pro relationship calculation

Relationship based on Unstored Calc. Field | FileMaker Community

filemaker pro relationship calculation

A quick google search for "FileMaker Relationship Filter" will yield some Any calculation placed on the Customer table to pull through related contact have to be done via script triggers, scripts, server-side scripting etc. FileMaker Pro has a number of native calculation functions to help for lists mentioned above was for use as a match field in a relationship. To create the filter, we will build a relationship between a table with a global This relationship will be based on three calculated fields: two in the base .. Seeking FileMaker Pro and PHP/MySQL Programmers in the Green.

The example we will be using is filtering a relationship to a Customer table, but by searching on a customers related Contacts, which are in an entirely different table. What are you Talking About? We are talking about the filtering of a relationship.

Relationships based on unstored calculations fail in WebDirect

There are a few various methods as to how this can be done, but we will be using a search field, that will match the contents of a key field generated on the related table. This article is going to assume you have knowledge about basic concepts behind filtering.

If you are unfamiliar with the concepts, there are a number of sites offering techniques on how to achieve this. A quick google search for "FileMaker Relationship Filter" will yield some results, or you can check out this great article on relationship filtering by Danny Kohn of FileMaker Inspirations.

This particular article does not use the exact technique mentioned in order to keep the complexity level to a minimum, but the technique mentioned in the article is a great one nonetheless.

filemaker pro relationship calculation

Setting up the Example Case The example we will be using to demonstrate this technique is a simple one using two tables - Customers and Contacts.

The relationship is that a Customer can have many Contacts, and so the foreign key field "Customer ID" is placed on the Contacts table. Both tables contain basic fields you would expect to find in these types of tables. The zLocatorKey fields found in both tables, are special key fields used to accomplish the filtering of the relationship. While this type of key is not exactly the most efficient in terms of size, it is relatively easy to grasp how it works to achieve the filtering.

In order to build this key, we have used a couple of custom functions, which we will not be going into detail about given the scope of this article, suffice to say you can check them out in the example file.

filemaker pro relationship calculation

The Goal We are Trying to Achieve: So, with all of this said, what is it we are actually trying to achieve here? The goal is to produce a portal based on the Customers table, which can be filtered by typing into a search field. We wish to allow the user to search by customer name, which should be a fairly straightforward task given that it is the customers table being filtered, and so the customers name resides on the table we are filtering.

How would we do this?

FileMaker Pro overview: Database relationships -

Contact information is not kept on the Customer table at all. You might think the answer would be to place the related contact information on the Customers table, but stop!

Any calculation placed on the Customer table to pull through related contact information is going to be unstored, and thus any relationship based on this calculation would break.

The destination side of any relationship must have all predicates stored. However, context always obeys one simple rule Remember this golden rules and relational concepts will become clearer the more you work with FileMaker. It took me years to truly grasp their complete nature of relationships but once I understood, I realized they were so simple. Calculation Field Options Calculation fields in Manage Database have a button in the lower right corner for setting storage options.

  • Complex Filtering Using Related Record Information
  • Unstored Calculation Fields in Relationships
  • Global Calculation Fields and Relationships

The settings found in the resulting dialog are mostly the same as the storage tab for a standard field, allowing for global storage, indexing and language. However, not all the features works quite the same and there is an additional option. In the case of global storage, a calculation field does act in many of the same ways as a regular global field. If the global calculation field references any regular fields, the formula is also updated based on values from the current record. At times, this behavior can be handy, allowing non-global data to be accessed from another table without a relationship.

With indexing, unchecking the option to "do not store calculation results -- recalculate when needed" allows the calculation to update anytime the screen is refreshed.

This is in direct opposition to stored calculation fields which update only when a referenced field in the current record is modified.

Relationship based on Unstored Calc. Field

Stored fields also calculate when exiting Manage Database and when a new record is created. Even though it may appear so from the dialog, indexing is not the most important difference between stored and unstored formulas. Some calculations may not update properly unless they are unstored such as formulas containing only Get function references.

Other calculation are forced to unstored if they reference global fields, related fields, summary fields or another unstored calculation field. Indexing is discussed at length in an article named In Depth Index so I don't see the need to repeat it's excellent information here. The FileMaker window refreshes when the record, layout or mode changes. While a calculation field displays live data, an Auto-Enter will not update unless a local referenced field is changed and the option to "do not replace existing value of field if any " is unchecked.

Otherwise, Auto-Enter calculations only calculate when the target field is empty. For example, if you want to format a phone number field, it's best to uncheck the option "do not replace existing value of field if any " so the formatting is performed every time the phone field is modified. For more information on phone number formatting, see the article Phone Filter Part One.

Auto-enter calculation fields will update when a related field is changed, as long as the dependency tree is triggered. Triggering the dependency tree requires the match field in the table to be updated. Merely updating the related value referenced in the calculation field in the related table will not update an auto-enter calculation to an auto-enter calculation field in another table. For more information on the dependency treesee this article.

Validations Calculation Options The specialty of validation calculations is curbing data entry. Validation calculations trigger only when a field is modified, or in some cases a record, as can be seen with the not empty validation option. It's best to try out these validation options to see what is meant but let me try to describe it anyhow. Imagine setting a bunch of fields to validate when empty. If FileMaker validated for empty on field modification or exit, you would have to fill the field in as soon as you touched it, causing user irritability.

Better to validate for empty when a user is leaving a record. On the other hand, if you validate for a range of values, it needs to be validated upon exiting of the field so the user is alerted immediately.

filemaker pro relationship calculation

Of course, the validation dialog does have a special option to "validate only if field has been modified" which defaults to checked. Unchecking this option allows the validation to behave like a not empty validation whenever the record is modified. Variables Variables enable scripts to pass data from place to place, most commonly bridging two tables without using a relationship.

Think of them like a clipboard where you can place the result of a calculation formula, except that you can create as many clipboards as you want! A common example might be to store a primary key in a variable using a calculation with a simple field referencego to a layout displaying records from a related table, create a new record and set the variable value into the foreign key.

The user can now enter the desired data in the child record and it will be properly linked to the parent. Only one other calculation method has this ability to carry values across records or tables as easily as a variable, and that is Script Parameters. Scripts Scripts in general allow for snapshot data. From Set Field to Replace Field Contents to Insert Calculated Result, any step that places a calculation result into a field requires the script to be run in order for the result to update.

Scripts and Auto-Enter calculations often overlap in functionality, making them competitors for the same jobs. The difference comes down to the methods by which users can update a formula. While a script can be executed in a variety of scenarios including a button, event trigger or a another script, Auto-Enter calculations only update when referenced fields are modified.

On the other hand, scripts are at the interface level, requiring them to be repeated across buttons, layouts or wherever you want them to be repeated. Auto-enter calculations are schema level so defining the formula once works anywhere the field is used. Data Viewer The Watch tab of the Data Viewer is like a cocktail napkin used to sketch out an idea at the proverbial restaurant, allowing calculations to be quickly tested against record data without disturbing the schema in any way.

The Data Viewer allows formulas to be entered and tested against any record with executing a script and possibly ruining data or disrupting a pristine layout by placing a test field on it.

FileMaker Layout And Calculation Context — thePRACTICALba

This can be quite useful in portal views and requires no scripting. Place a global search field and button above a portal for Google-like search functionality through a portal. This relationship will be based on three calculated fields: In the field options, set the new field to use global storage as shown in the figure below.

Under the storage tab in the field options, select the "Use global storage one value for all records " check box.

filemaker pro relationship calculation

FilterStart In the base table, we will create a calculated field, FilterStart. In a sort, this value would always appear at the beginning of the desired search results. The FilterStart calculation, as shown in the image above, will be as follows: Filter End Also in the base table, we will create another calculated field, FilterEnd. The FilterEnd calculated field will produce a Text result of the contents of GlobalSearch followed by the repeated letter z.

In a sort, this value would always appear at the end of the desired search results. The FilterEnd calculation, as shown in the image above, will be as follows: