4 Replies Latest reply on Mar 27, 2012 10:41 AM by BryanN

    Creating Parent Records from Child Table/Layout/Record

    BryanN

      Title

      Creating Parent Records from Child Table/Layout/Record

      Post

      First off, thanks to all for your help so far.  I've gone through the tutorials at Lynda.com and they were worth their weight in gold.

       

      I've attached a screenshot of my relatoinships, which all should be correct.  Here is what I am trying to do:

       

      Work Orders are the main go to place for all the info.  I have multiple portals set up to child tables in order to create new childh records from it. However, the challange I have is this:

      - I would like for employees (who will only have access to some of the child tables) to be able to create a new record in the child table/layout (lets use Customer Inventory) as the example.  I would like our employee to be able to create a new record in Customer Inventory, and when doing so, assign it a Work Order Number (which is the foreign key in Customer Inventory and the primary key in Work Orders). Doing so, if the Work Order record isn't created already, will create it.  I already have it working in reverse via a portal, just looking for how to create a new parent record from the child table/layout.

       

      The only way I've gotten it to work so far, is by using the primary key field (Work Order #) from the Work Orders table in the layout for Customer Inventory instead of using the foriegn key field for Work Order #.  Is this the right way to do it or will it cause issues down the line?

       

      To simplify, I want to be able to create new records in Work Orders from the child, Customer Inventory.

      (I've already succeeded in creating a portal in Work Orders to create records in Customer Inventory... if that's the right way to do it).

       

      Let me know and I can clarify. Thanks for your help.

      Screen_Shot_2012-03-27_at_8.50.39_AM.png

        • 1. Re: Creating Parent Records from Child Table/Layout/Record
          philmodjunk

          if _pk_WO_Work_Order_Number is the primary key, it should be defined as an auto-entered serial number. If this value is being manually entered to create a new record in the Work_Orders table, you risk problems correcting data entry errors due to entering the wrong work order number. If the value in this field comes from a source outside the database, set it up as a data field so you can identify records with it, but use an auto-etnered serial number to link the records in this table to records in other tables.

          Working with the relationship you have at the moment...

          There are any number of scripted approaches that can be used to create a new work order record and then link it to a new record in Customer_Inventory. Assuming that the user enters a value in _fk_Cust_Inv_Work_Order_Number is intended to trigger creating a new work order record if the number does not exist, you can set up this simple script and perform if via the OnObjectSave script trigger on _fk_Cust_Inv_Work_Order_Number.

          Set Field [Work_Order::_pk_WO_Work_Order_Number ; Customer_Inventory::_fk_Cust_Inv_Work_Order_Number ]

          To make this work, "allow creation of records via this relationship" must be enabled for the Work_Order table occurrence in this relationship and this assumes that _pk_WO_Work_Order_Number is not an auto-entered serial number.

          • 2. Re: Creating Parent Records from Child Table/Layout/Record
            BryanN

            Phil, thanks for  responding.  The reason I don't have the _pk_Work_Order_Number set as an auto-non modify serial is that we number our Work Orders like so:

            2digit year-2digitmonth-sequentialorderuntil the end of the month... aka: 12-03-0xx (2012, March, and next available number starting at 001 working up to 999).  This ensures that they are at least unique, but you are right, it could result in data entry errors.  If there was a way to make it pre-formatted with the work orders and then have it auto enter the last 3 digits.. I'm totally not opposed to doing that, I just couldn't figure out a way to do it.

             

            Aside from the autoID conundrum, what I gather is that I should be scripting the creation of the parent record, not using it's field in the child's layout, correct?

            With that same logic, should I be doing the same thing for creating a new Contact out of the Work Orders layout?  Currently, I'm borrowing the fields from the Customer Contacts table in the Work Order layout to edit/create/select new customers (drop down box with customer ID does the selecting if they already exist and the borrowed fields from Customer Contacts can create new ones if they need to, otherwise they just show the existing customer data).

            • 3. Re: Creating Parent Records from Child Table/Layout/Record
              philmodjunk

              I didn't say you couldn't number your work orders in that fashion, only don't use it for the field in your relationships. Consider the difficulties that could ensue if you incorrectly number a work order, create related records and then have to fix the data entry error without losing the link to related records--that match on the incorrectly entered value. Put your work order number into a different field so that you can search for it by performing a find and so that you can include it in layouts, but use an auto-entered serial number as your primary key for linking work order records to other tables.

              It is indeed possible to set up a system that auto-numbers work orders in this fashion, but don't try to use it as a primary key. One key complication is that it's easy for two different users to create records that have the same work order number--whether they enter the value by hand or rely on FileMaker to do it for them. An auto-entered serial number does not have that limitation. I do suggest that you set a unique values validation rule on your work order number field to help protect yourself from getting two work orders with the same exact number.

              Note that changing over to auto-entered serial numbers for Primary Keys will require a different script as you'd need to create the new parent record then copy the value back over to the newly created child record to establish the link between the two.

              ...should be scripting the creation of the parent record, not using it's field in the child's layout, correct?

              You need the same value in both fields of the relationship: _pk_WO_Work_Order_Number and _fk_Cust_Inv_Work_Order_Number. Simply entering the number into the first field does not enter it into the second field. The script saves the user from having to enter exactly the same data twice--protecting your database from possibly data entry errors.

              should I be doing the same thing for creating a new Contact out of the Work Orders layout?

              Is the ID field in customer contacts an auto-entered serial number like it should be? If so, a script should be used, but not the one I posted earlier. Here's a "new customer" script you can perform from a layout based on the Work Orders table occurrence:

              #This script assumes that you have already created a new work order record
              Go to Layout [Customer Contact]
              New Record/Request
              Set variable [$CustID ; value: Customer_Contacts::_pk_Customer_Contact_ID]
              Go to Layout [Original Layout]
              Set Field [Work_Orders::_fk_Work_Order_Customer_ID ; $CustID ]

              Once you run this script, fields from Customer_Contacts placed on your Work_Orders based layout can be used to fill in details about the new customer contact.

              PS. Is it ever possible that a vendor could also be a customer? If so, you might want to use one table for both vendor and customer contacts so that you do not have to enter the same contact info twice when this occurs.


              • 4. Re: Creating Parent Records from Child Table/Layout/Record
                BryanN

                I didn't say you couldn't number your work orders in that fashion, only don't use it for the field in your relationships. Consider the difficulties that could ensue if you incorrectly number a work order, create related records and then have to fix the data entry error without losing the link to related records--that match on the incorrectly entered value. Put your work order number into a different field so that you can search for it by performing a find and so that you can include it in layouts, but use an auto-entered serial number as your primary key for linking work order records to other tables.

                It is indeed possible to set up a system that auto-numbers work orders in this fashion, but don't try to use it as a primary key. One key complication is that it's easy for two different users to create records that have the same work order number--whether they enter the value by hand or rely on FileMaker to do it for them. An auto-entered serial number does not have that limitation. I do suggest that you set a unique values validation rule on your work order number field to help protect yourself from getting two work orders with the same exact number.

                Note that changing over to auto-entered serial numbers for Primary Keys will require a different script as you'd need to create the new parent record then copy the value back over to the newly created child record to establish the link between the two.

                 

                I can see your point.  That's a good suggestion and I'll go ahead and implement that one.

                 

                You need the same value in both fields of the relationship: _pk_WO_Work_Order_Number and _fk_Cust_Inv_Work_Order_Number. Simply entering the number into the first field does not enter it into the second field. The script saves the user from having to enter exactly the same data twice--protecting your database from possibly data entry errors.

                Got ya.

                 

                Is the ID field in customer contacts an auto-entered serial number like it should be? If so, a script should be used, but not the one I posted earlier. Here's a "new customer" script you can perform from a layout based on the Work Orders table occurrence:

                #This script assumes that you have already created a new work order record
                Go to Layout [Customer Contact]
                New Record/Request
                Set variable [$CustID ; value: Customer_Contacts::_pk_Customer_Contact_ID]
                Go to Layout [Original Layout]
                Set Field [Work_Orders::_fk_Work_Order_Customer_ID ; $CustID ]

                Once you run this script, fields from Customer_Contacts placed on your Work_Orders based layout can be used to fill in details about the new customer contact.

                 

                Yes customers are in a serialized ID primary key.  Literally everything else in the database is auto serialized primary keys.  The only one that wasn't was the Work Order Number.

                 

                PS. Is it ever possible that a vendor could also be a customer? If so, you might want to use one table for both vendor and customer contacts so that you do not have to enter the same contact info twice when this occurs.

                 

                In our experience, no, that hasn't happened before.  Thanks for the heads up though!