FileMaker Relationship and Portal tips
Category: Mac, Category: FileMaker Pro
Originally posted on my iBlog 8 January 2005.
Summary: These are some notes on my first attempts to set up portals in FileMaker Pro 7. Don't treat this as gospel. I'm still playing with this stuff.
Body: Portals allow the many related records to be displayed in rows within a layout of the original table. The portal should contain fields from the related table. New records in the related table can be created in the portal if "Allow creation of records in this table via this relationship" is turned on in the relationship between the tables (in the Relationships graph of File:Define...:Database").
******************
To prevent a field from being altered in Browse mode (for example, if you want to only allow users to enter a value into the field using a script), in Layout mode, select Field:Field Behaviour... and turn off the option to enter in Browse mode. Keep the option to enter in Find mode.
In the NatureObserver (prototype name) project, I use this option to create new datasets for a datasource, inside a portal in a layout in the datasource table. This way, if the user deletes dataset 3 of 5, they are unable to renumber the remaining datasets manually. They are forced to live with a set of records with dataset numbers 1,2,4, and 5. This is essential for relationships not to be disrupted.
******************
Deleting a row from a portal (i.e., the related record in the related table is deleted) has a potentially dangerous twist. Keep the "Delete records in this table when a record is deleted in the other table" option turned off, unless you want to start a chain of related record deletions moving up the relationships.
When forming the relationships between tables in the database (and between databases), be very careful about setting the options to create and delete records in each side of the relationship from the location of the related record. You access these options when setting up a relationship, or by double-clicking on the page icon for a relationship between two tables on the Relationships graph.
I took some time to solve a mystery of why, when I deleted one dataset (of several from one datasource), I also mysteriously deleted the datasource record for that dataset and the full citation record for that datasource. This happened because FileMaker followed up a chain of selected "Delete records in this table when a record is deleted in the other table" options. It general, it is important to have this option always turned off. However, the reverse option, "Allow creation of records in this table via this relationship" can be very useful, and I am tending to turn it on by default. It is necessary for creating new records from within a portal.
******************
The following is copied from the file "ManyMany PortalScript", dated 4 January 2005
The following set of scripts moves from a portal row in Table 1 to the related record in a Join Table and then to the related record in Table 2. The next three scripts move backwards to the original portal row. Scripts applied from a description at http://dbforums.com/t349224.html.
This system of Table1-Join-Table2 is apparently the best way to set up a many-many relationship in FileMaker Pro (in effect, by fusing a many-one and one-many relationship). I'm still missing the step of how to extend this simple example (one-one-one) to the many-many situation.
These scripts are working in my trial database, PortalManyManyTest.fp7.
First
Set Field [ Table1::GlobalNumber; Get(PortalRowNumber) ]
Go to Related Record [ From table: "JoinTable"; Using layout: "JoinTable" (JoinTable) ]
Perform Script [ "Join" ]
Join
Go to Related Record [ From table: "Table2"; Using layout: "Table2" (Table2) ]
Perform Script [ "Second" ]
Second
Go to Field [ JoinTable::ID2 ]
[ Select/perform ]
And back again...
SecondReturn
Go to Related Record [ From table: "JoinTable"; Using layout: "JoinTable" (JoinTable) ]
Perform Script [ "JoinReturn" ]
JoinReturn
Go to Related Record [ From table: "Table1"; Using layout: "Table1" (Table1) ]
Perform Script [ "FirstReturn" ]
FirstReturn
Go to Portal Row [ Table1::GlobalNumber ]
[ Select; No dialog ]
******************
I have scripted the addition of new datasets in the portal in the datasource table layout, as follows.
"Add a new Dataset in same DataSource from DataSource portal"
Set Field [ DataSource::global number; 0 ]
Go to Portal Row
[ Select; Last ]
Go to Portal Row
[ Select; Previous ]
Copy [ DataSets::Dataset_number ]
[ Select ]
Paste [ DataSource::global number ]
[ Select ]
Go to Portal Row
[ Select; Last ]
Set Field [ DataSets::Dataset_number; DataSource::global number + 1 ]
Note that since I am allowing the creation of new records from within the portal, the last row is blank, for new record creation.
I didn't want the user to see the global number, so I removed the field from this layout. But then the script stopped working. It turns out that it must be on the layout of the script. So instead I changed its text to white (and no border) to disappear from view.
[In the end, this proved unsuitable, and I decided to use a unique per-record dataset ID, which is auto-entered when the user starts to enter data on the last (new) row of the portal.]
Originally posted on my iBlog 8 January 2005.
Summary: These are some notes on my first attempts to set up portals in FileMaker Pro 7. Don't treat this as gospel. I'm still playing with this stuff.
Body: Portals allow the many related records to be displayed in rows within a layout of the original table. The portal should contain fields from the related table. New records in the related table can be created in the portal if "Allow creation of records in this table via this relationship" is turned on in the relationship between the tables (in the Relationships graph of File:Define...:Database").
******************
To prevent a field from being altered in Browse mode (for example, if you want to only allow users to enter a value into the field using a script), in Layout mode, select Field:Field Behaviour... and turn off the option to enter in Browse mode. Keep the option to enter in Find mode.
In the NatureObserver (prototype name) project, I use this option to create new datasets for a datasource, inside a portal in a layout in the datasource table. This way, if the user deletes dataset 3 of 5, they are unable to renumber the remaining datasets manually. They are forced to live with a set of records with dataset numbers 1,2,4, and 5. This is essential for relationships not to be disrupted.
******************
Deleting a row from a portal (i.e., the related record in the related table is deleted) has a potentially dangerous twist. Keep the "Delete records in this table when a record is deleted in the other table" option turned off, unless you want to start a chain of related record deletions moving up the relationships.
When forming the relationships between tables in the database (and between databases), be very careful about setting the options to create and delete records in each side of the relationship from the location of the related record. You access these options when setting up a relationship, or by double-clicking on the page icon for a relationship between two tables on the Relationships graph.
I took some time to solve a mystery of why, when I deleted one dataset (of several from one datasource), I also mysteriously deleted the datasource record for that dataset and the full citation record for that datasource. This happened because FileMaker followed up a chain of selected "Delete records in this table when a record is deleted in the other table" options. It general, it is important to have this option always turned off. However, the reverse option, "Allow creation of records in this table via this relationship" can be very useful, and I am tending to turn it on by default. It is necessary for creating new records from within a portal.
******************
The following is copied from the file "ManyMany PortalScript", dated 4 January 2005
The following set of scripts moves from a portal row in Table 1 to the related record in a Join Table and then to the related record in Table 2. The next three scripts move backwards to the original portal row. Scripts applied from a description at http://dbforums.com/t349224.html.
This system of Table1-Join-Table2 is apparently the best way to set up a many-many relationship in FileMaker Pro (in effect, by fusing a many-one and one-many relationship). I'm still missing the step of how to extend this simple example (one-one-one) to the many-many situation.
These scripts are working in my trial database, PortalManyManyTest.fp7.
First
Set Field [ Table1::GlobalNumber; Get(PortalRowNumber) ]
Go to Related Record [ From table: "JoinTable"; Using layout: "JoinTable" (JoinTable) ]
Perform Script [ "Join" ]
Join
Go to Related Record [ From table: "Table2"; Using layout: "Table2" (Table2) ]
Perform Script [ "Second" ]
Second
Go to Field [ JoinTable::ID2 ]
[ Select/perform ]
And back again...
SecondReturn
Go to Related Record [ From table: "JoinTable"; Using layout: "JoinTable" (JoinTable) ]
Perform Script [ "JoinReturn" ]
JoinReturn
Go to Related Record [ From table: "Table1"; Using layout: "Table1" (Table1) ]
Perform Script [ "FirstReturn" ]
FirstReturn
Go to Portal Row [ Table1::GlobalNumber ]
[ Select; No dialog ]
******************
I have scripted the addition of new datasets in the portal in the datasource table layout, as follows.
"Add a new Dataset in same DataSource from DataSource portal"
Set Field [ DataSource::global number; 0 ]
Go to Portal Row
[ Select; Last ]
Go to Portal Row
[ Select; Previous ]
Copy [ DataSets::Dataset_number ]
[ Select ]
Paste [ DataSource::global number ]
[ Select ]
Go to Portal Row
[ Select; Last ]
Set Field [ DataSets::Dataset_number; DataSource::global number + 1 ]
Note that since I am allowing the creation of new records from within the portal, the last row is blank, for new record creation.
I didn't want the user to see the global number, so I removed the field from this layout. But then the script stopped working. It turns out that it must be on the layout of the script. So instead I changed its text to white (and no border) to disappear from view.
[In the end, this proved unsuitable, and I decided to use a unique per-record dataset ID, which is auto-entered when the user starts to enter data on the last (new) row of the portal.]
