6 Replies Latest reply on Oct 22, 2009 7:18 PM by DrDave

    Adding Records to a Different Database



      Adding Records to a Different Database


      I’m looping through a database (Customer) and, given a specific condition (such as Customer::Orders>10), I want to add a record to another database (BigShooter). 


      I then want to populate the new record with values from the current database record, as in BigShooter::Laname = Customer::Lname.


      I’m not sure how to structure this in FMP. Any help to get me started is appreciated.


        • 1. Re: Adding Records to a Different Database

          In Customer, define an auto-entered serial number field "CustomerID". (You have probably already done this.)

          In BigShooter, you need exactly one field: "CustomerID". (You can add other fields, but don't add any other fields such as customer name that already exist in Customer.)


          Define a relationship linking the two fields:


          Customer::CustomerID = BigShooter::CustomerID  // enable "Allow creation of related records via this relationship for the BigShooter table


          Now a single script step will assign a customer to the BigShooter table:


          Set Field [BigShooter::CustomerID; Customer::CustomerID]


          On a layout that lists records from BigShooter, simply add the fields from Customer that you need to show names, contact info, etc.


          Now that I've typed this all in, it occurs to me that you might not need the Big Shooter table at all.

          Define a calculation field in Customers, cBigShooter:


          If ( Orders>10 ; "Big Shooter" ; "" )


          Now, on a layout based on Customer, perform a find for the text "Big Shooter" in the cBigShooter field to find all such customers.

          • 2. Re: Adding Records to a Different Database

            Thank you, that helped me to get to the next step. 


            I now have a layout in the Customer database AND I have two portals in that layout -- one to BigShooter and one to TheOrders.


            If I'm looping through the Customer database, what does the script step look like, if I want to add a record to TheOrders database?


            Again, thank you for your help. 

            • 3. Re: Adding Records to a Different Database

              The script Step is New Record/Request, but you have to tell Filemaker in which table to create the new record.


              Assuming the script is triggered from a Customer based layout and that the Order record will be related to that customer by CustomerID:


              Set Variable [$CustID; value:Customer::CustomerID]

              Freeze Window

              Go To Layout ["TheOrders" (TheOrders)] //specify a layout based on TheOrders table

              New Record/Request

              Set Field [TheOrders::CustomerID; $CustId]

              Go To Layout [original layout]

              • 4. Re: Adding Records to a Different Database

                I see what you are doing here, but I'm having a problem with the "go to layout" script command



                When add the "go to layout" command to my script, the FMP interface allows me to select a script. However, I can only see my Customer-based layouts. My customer database and orders database are in two different physical files.


                IS there a way to select a layout for a different database file? 

                • 5. Re: Adding Records to a Different Database

                  When you open Manage | Database | Relationships, is there a Table Occurrence box that refers to this external file? I assumed you do.


                  Create a new layout and select that Table Occurrence as the table for your new layout.


                  By the way, what version of Filemaker are you using?


                  If you're using a fairly recent version of filemaker, it may be much easier for you to merge the files into a single file with multiple related tables.


                  If your using FMP 6 or older, the script I've posted won't work.



                  • 6. Re: Adding Records to a Different Database

                    I'm using version 10 of FMP.


                    What you suggested worked. Thank you so much....