The auto entered serial ID in the parents table should be just that. However, the FK ID in the child tables is NOT an auto enter. It should be text or number and not auto entered. When you link the parent and child via the ID fields, FM automatically enters the FK in the child table when you create a record from the parent table. For example you have a field, let's say CarMake in your child table. You place this field on a layout based on the parent table. When you choose a value for Car Make a new record is created in the child table and is automatically assigned the Primary key from the parent table. You probably know all this already.
Unfortunately the relationship does not appear to be creating new records in the child table.
My child's ID field is not auto enter.
I've been thinking about it, and what I'm really looking to do is auto complete the fields in the child table using the last record created in the parent table.
I've tried using a combination of portal and script to go to the last record in the parent table, but again nothing happens.
But perhaps I'm just misunderstanding your answer.
Do you have "Allow creation of records etc enabled in the relationship definition? To do this click the square between the tables in the relationship graph and select the appropriate box.
If you do not create the record via a context (typically a portal) where "allow creation...." will automatically fill in the needed value in the FK field, you have to devise other methods to put the correct value into that field so that your defined relationship correctly links the new record to a record in the related table.
How you do that depends on the design of your system.
A script can use a variable to copy the value from the parent table to the related child table.
A script can put the current PK value in a global variable or field and then an auto-enter calculation on the FK field can copy it from that specified source.
Often the FK field is formatted with a value list so that the user can select the Record whose PK value should be entered into the field to establish the link.
So I've set the relationship between my PK and FK to allow auto creation in the child table.
I've created a portal over the PK in the Parent layout, and then wrote a script for the FK in the child layout
Go to portal Row [Select; Last]
Set Field [Child::FK[[Parent::PK]
I created a new record in the parent table, with the auto serial number. The new record was created in my child table.
However, in the parent table, while the serial is automatically created on creation of a new record, the ::autoID field created by the portal requires a manual entry before it changes to the serial ID created in the PK. Is there a way to automate that too, so it is created when the record is created?
Also, how does this work for multiple tables? To start with, I'm only working with the master and auto tables, but eventually there will be a lot more that will be related to the master. How do I direct the new record to the correct table, as when records are created they will be jumping from table to table?
I should mention I'm using the pro 12 version of FM.
Here is a basic flow chart of a little bit of the information for how I want my database to go.
Layout 1: Master List
Layout 2: Automotive List
Fields: autoID (from masterID)
Type (auto fill from master)
Make (auto fill from master)
Model (auto fill from master)
Layout 3: Building List
Fields: buildingID (from masterID)
Type (auto fill from master)
With "allow creation..." enabled, you do not need any script to create a record in the portal. You can simply enter data and the related record will be created and the necessary ID number will be copied into the related record for you.
If you use a script to create the related record, you do not need to use the portal, nor do you need "allow creation" in order for it to work, though that is an option.
The extra brackets in:
Set Field [Child::FK[[Parent::PK]
tell me that you entered Parent::PK into the repetition box--which is not where you should enter that reference to the PK field.
When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.
Here's a script that will create a new related record that does not rely on a portal nor the "allow creation" setting:
Set Variable [$ID ; Parent::PK]
Go to Layout ["Child" (Child) ]
Set Field [Child::FK ; $ID ]
Go To Layout [Original Layout]
Your answers have been most helpful.
But I'm still stuck on the aspect of multiple layouts.
I've tried experimenting with the If statement in the script, in hopes that it may direct my PK to the right child table. But it hasn't had any effect.
My thought was that the Equipment Master TYPE field would direct which table should be receiving the PK. For example, if Type is set to Pickup, then the PK would fill in the FK on the Automotive layout. And if Type is set to Duplex, it would fill in the FK on the Building layout.
You also should keep in mind that Layouts and Tables are two different things. You can create any number of different layouts and have them all refer to the same table.
The item that links a given layout to a specific table is the Tutorial: What are Table Occurrences? box in Manage | Database | Relationships and this is selected for a given layout in the "Show Records From" drop down list found in "Layout Setup...".
I created tables that shared a relationship with the Master through their IDs.
However, upon further reflection, it makes more sense that the detailed table should be filled out first, and then the fields from this detailed table will auto fill the corresponding fields in the basic master. This way, the Master table doesn't even need to be accessed.
The way I have it set up now, each table has it's unique key, which populates the master through their relationship.
Thank you so much everyone for your help. I'm not sure if I have a better grasp of the workings of databases and filemaker pro, but at the very least I'm able to set up my database to do what I want.
There are two basic approaches to what you describe. 1 method physically copies data from one table into the other. The other method simply relies on relationship links to access data in the table where the data resides without copying it.
The first method should be used only if there is a specific reason for doing so--such as preserving a "snapshot" of a value in the lookup table that may change in the future. (Prices in a catalog or products table being looked up into an invoice's line items is a typical example. You wouldn't want future price changes to alter the values used in past invoices.)
Here's a link that discusses both methods: Auto Fill