Working with Many to Many Relationship Tables
I am new to FileMaker. I have done extensive databse work in AlphaFive, but have little hands on experience in FileMaker. I have a complicated database schema which has many overlapping relationships. Unfortunately, none of the books or forum questions seem to address (at least clearly for me) how to do the following. I would greatly appreciate the pointers.
Specifically, I need to create scripts for buttons to perform the following functions on what I will refer to as “Relationship” portals/browses:
“Add Relationship”. Each relationship portal has a many-to-many joining table linking the current “core” record to another “core” record in another table (i.e. current “Contact” to “Company” or current “Contact” to “Property”, etc.). The relationship is created by placing both the ID Key from the current core record and the ID Key from the “related” core record in the joining table. Also contained in that joining table is the “status” of the relationship (i.e. “Active” a logical field – 1 or 0) and on some occasions, the type of relationship (i.e. “Main Company” a logical field – 1 or 0). To “Add a Relationship” a new record would need to be created in the joining table, however, first a search would need to be performed to make sure that the record exists and if not, the ability to add a new record should be triggered (note: these would be the same sequence and steps used to enter the “core” record from any other location in the database as well). Once the core record is added (if necessary), the script would enable the user to add the related core record to the joining table and any logical selections (i.e. “Active”, “Main”, etc.) could be made at that time. This action would be launched by clicking on a button adjacent to the associated portal.
“Edit Relationship”. To “Edit a Relationship” the existing record in the joining table would need to be opened. This will ultimately be restricted to specific user groups through database security controls so that errors are limited. If the “Edit” needs to take place in the related “core” table, the ability to modify the detail of the associated core record through a modal window would be necessary. This action would be launched by clicking on an edit icon on the portal row associated with the related core record.
“Delete Relationship”. To “Delete a Relationship” the existing record in the joining table would need to be deleted from the database. This will ultimately be restricted to specific user groups through database security controls so that errors are limited. In actual process a relationship would only be deleted if it was created in error as most cases would require simply a change in “status”. Still debating whether the ability to delete the actual core record from this location is needed or not. This action would be launched by clicking on an icon on the portal row associated with the related core record.
“View Related Record Details”. To view the details of each associated core record in the joining table through a modal window. This action would be launched by clicking on a specific column in the related core record in the portal (i.e. “Company” as a field in a column - not the portal row record).
“Active – Inactive – All”. Ability to filter a portal based upon the “Active” status of a related core record. The active status is kept in the joined table as a logical field. Clicking on a radio button selector seems to be the simplest user interface. Default would be “Active”.
Thanks for any "simple" directions!!!