Creating Related FileMaker Records On The Fly

From Dwayne Wright PMP, PMI-ACP
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit or other formatting of the posting may fail. For the best experience, please visit the journal directly by clicking (here)



FileMaker allows you to create new records through a relationship setting. This means you can be on a layout that is linked to a particular table occurrence and using a related field from another table occurrence, create a brand new record there (without leaving that layout ... or ... using a script).

In the bottom third of the define relationship operator box, you can see options for each table occurrence in that relationship. These settings include ... "Allow creation of records in this table via this relationship", "Delete related records in this table when a record is deleted in the other table" and Sort Records. The settings are on both sides because FileMaker relationships are bidirectional.


Here you can see the “Allow creation of records in this table via this relationship” setting in the Define Relationship Dialog box. Notice this option is present twice, although I’m only using it once here.

PORTAL METHOD OF CREATING RELATED RECORDS ON THE FLY
When a portal uses a relationship that allows the creation of related records, there is an empty portal row at the bottom of the portal. This empty row is not a related record but more like a related record in waiting. When you add data into this bottom row, a new related record is created when you commit the record. As soon as you are done, a new blank row will appear, again as a new related record in waiting.

JUST A FIELD ON A LAYOUT CREATING RELATED RECORDS ON THE FLY
Say you put a field on a layout that uses a relationship that allows the creation of related records. It's not in a portal, it is just sitting there. When there is related information, you will see data from the first related record in that related fields.

Let us also say the record you are on doesn't have any related records using the same relationship. When you type data into the field and exit / commit the record, a new related record is created automatically using that data. This is like a relational splice between table occurrences.


TROUBLESHOOT RELATED RECORD CREATION PROBLEMS

You might encounter a situation where you click the “create related records” option but FileMaker doesn’t allow you to do it. This is because a setting somewhere else in your FileMaker solution is trumping your ace. These conditions can include the relationship comparison operator in use, the type of key field in the child side of the relationship or a security privilege settings.

I remember back to boot camp when I joined the Navy at the ripe old age of eighteen. They used to grill us constantly on "attention to detail." If it was noticed that we folded a shirt out of order during an inspection, we would get drilled for a hour outside of the barracks. I was told that on a ship, it was possible to turn a valve or a combination of valves in such a way to cause an explosion on a ship. Many times, you would need to get a sign off from a number of departments before you could do ... what appeared to be ... a very basic chore.

Here are some of the valves that can be turned the incorrect direction at the incorrect moment and break the ability to create related records.

CHILD KEY FIELD TYPE - To create a related record, the child key has to be able to accept data from the parent key field. If the child key field is a calculation, summary or container field, that isn't possible. This is one reason why it is a good idea to give a prefix to key field names. This helps identify this purpose to the developer so that he / she doesn't make a change to the field that may break a relationship.

CHILD KEY FIELD PRIVILEGE - To create a related record, the child key has to be able to accept data from the parent key field. If a security setting prevents access to this field, will break the create related records feature.

CHILD TABLE PRIVILEGE - If a security setting does not allow you to create records in a table, the create related records setting will not override that setting.

LAYOUT SETUP - As you remember from our earlier discussions, a layout is tied to a table occurrence and this table occurrence is tied to relationships. It is very common to have two or more table occurrences for a table. It is quite easy to switch a layout from one table occurrence to another and when this happens ... none of the relationship settings, some of the relationship settings or all the relationship settings might break for that layout.

LAYOUT RELATED FIELD / PORTAL MISSING - If there isn't an empty related field or a portal on the current layout using this relationship, the create related records might fail.

NOT A SCROLLABLE PORTAL - If the portal that is using the relationship is not scrollable and all its rows are filled, FileMaker users and scripts cannot go to an empty row to create a new record.

COMPARISON OPERATOR IN USE - Only the ( = ) equijoin relationship operator will allow you to create related record. This is the only operator that can tell FileMaker exactly what data should be in the child key field for a newly created related record.

ANY MULTIPLE LEVEL COMPARISON WILL LIKELY FAIL - Again, this is because FileMaker will likely have troubles knowing what data string should be in the child key field for a newly created related record.

=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2007 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.