11 Replies Latest reply on Mar 18, 2013 11:10 AM by philmodjunk

    Autofill Serial ID in Related Table

    LuckBeaLady

      Title

      Autofill Serial ID in Related Table

      Post

           I am very new to FileMaker Pro, and databases in general. 

           I would like to create a database for assets.

           A main list, which I call Asset Master which contains basic fields for each of the related tables I'm creating (autos, buildings, etc.).

           I've set assetID to auto serial ID in the Asset Master.

           What I'd like is to fill out the Asset Master first, creating the serial ID.

           Then, have that serial ID be automatically entered into a new record in a different table.

           For example,  assetID is created in Asset Master as 101 - then I go into my Auto Table and create a new record, in which the autoID 101 automatically fills.

           I've got my tables set up so each table has it's own ID (autoID, buildingID) and those each have a relationship with the assetID field.

           I've tried scripts, but I must be writing them wrong, as nothing happens. I've tried a portal, but the assetID field only populates if the autoID field is the exact same (ie. assetID 101, autoID 101 - but assetID will not fill in if autoID is 102).

           Eventually there will be around 11 individual tables that need to be autofilled from the Master List, and I'd like to be able to hide the ID fields and still know they are being populated correctly. Especially since fields in the individual tables will be autofilled by corresponding fields in the Master List (I know how to do this). As such, I cannot use a drop down menu to fill the ID (I know how to do this part too).

           Any help, or even suggestions on what I should search for would be greatly appreciated.

        • 1. Re: Autofill Serial ID in Related Table
          RickWhitelaw

               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.

          • 2. Re: Autofill Serial ID in Related Table
            LuckBeaLady

                 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. 

            • 3. Re: Autofill Serial ID in Related Table
              RickWhitelaw

                   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.

              • 4. Re: Autofill Serial ID in Related Table
                philmodjunk

                     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.

                • 5. Re: Autofill Serial ID in Related Table
                  LuckBeaLady

                       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]

                       Commit Records

                        

                       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

                       Fields: masterID

                                    Type

                                    Make

                                    Model

                       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)

                                       

                        

                  • 6. Re: Autofill Serial ID in Related Table
                    philmodjunk

                         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]
                         Freeze Window
                         Go to Layout ["Child" (Child) ]
                         New Record/Request
                         Set Field [Child::FK ; $ID ]
                         Go To Layout [Original Layout]

                    • 7. Re: Autofill Serial ID in Related Table
                      LuckBeaLady

                           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.

                      • 8. Re: Autofill Serial ID in Related Table
                        JimMac

                             I think you are MIxing Apples and Oranges.

                             Once you create the Relationship "Parent to Child" there is no need to transfer a copy of the information to the Child.wink  

                             It is available from the Parent record by the Relationship.

                             Jim...

                              

                        • 9. Re: Autofill Serial ID in Related Table
                          philmodjunk

                               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...".

                          • 10. Re: Autofill Serial ID in Related Table
                            LuckBeaLady

                                 My apologies.

                                 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.

                            • 11. Re: Autofill Serial ID in Related Table
                              philmodjunk

                                   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