New Record Only Relationships

New Record Only Relationships

You might not want to have to write scripts to create new related records for display in your portals. Scrolling to find them can still be an issue in some cases, and a script still requires a user action to be executed or performed.

An alternative is what we're calling "new record only" relationships. To establish them, you need to rely on the auto-entry options in FileMaker Pro, the triggering behavior of the Evaluate function, and the order in which FileMaker Pro performs certain tasks.

The technique described here enables you to use related fields through a relationship that allows creation of new records, just as if you were planning to allow users to scroll to the end of a portal, but that doesn't require you to scroll to the bottom of a portal to do so. The related fields you'll use provide a data entry area outside the portal that is always available to create new related records. When users type data into the fields, a new record is instantly created, but then on committing those new edits, the relationship is immediately invalidated so that these new record fields remain blank and ready for more data input.

This then obviates the need for scripting, and opens up some possibilities for user interface and working with portals. We'll often choose to place the layout objects for this technique in a pop-up window.

Consider the Relationships Graph shown in Figure 16.5, in which a neighborhood is related to many houses. The second of the two relationships is set to allow for the creation of related records. Note that we have created two foreign IDs in the related table occurrences and that the two relationships are different.

Figure 16.5. This Relationships Graph represents two equijoin relationships. The temp relationship is set to allow creation of related records.

The rest of this technique relies on field definitions. The basic approach is that you allow the _kf_TEMPneighborhood field to be populated via the Allow Creation of Records in This Table via This Relationship setting, but then immediately set the field to zero when the record is committed.

This requires that two tasks happen: that the _kf_neighborhood gets set, and that, only after this field is set, _kf_TEMPneighborhood is cleared.

To accomplish the first task, set _kf_neighborhood to _kf_TEMPneighborhood, using an auto-entry calculation. An instant before the temp field is cleared, it will contain a proper value for this house record's neighborhood ID.

The second task, clearing the temp field, is accomplished if you set the auto-entry options for _kf_TEMPneighborhood, turning off the Do Not Replace Existing Value for Field (If Any), with this formula:

Evaluate ( If ( IsEmpty (_kf_neighborhood); _kf_TEMPneighborhood; 0); _kf_neighborhood )

Notice that the _kf_neighborhood is defined as a trigger for the Evaluate function. When _kf_neighborhood is populated, by its own auto-entry pointing to _kf_TEMPneighborhood, that event then triggers _kf_TEMPneighborhood 's auto entrywhich then sets itself to zero. The technique assumes that you have no records in your neighborhood table with a _kp_neighborhood of zero. Because the second relationship based on the _kf_TEMPneighborhood is invalidated after this process is complete, you can place data entry fields on a layout, tied to your second temp relationship, that are then cleared after a new record is committed.

This is quite an advanced technique, but it is elegant in that it requires no scripting.