2 Replies Latest reply on May 23, 2011 2:15 AM by Ndaba

    Populating multiple fields from one database to another



      Populating multiple fields from one database to another


      I'd appreciate help on the easiest way to populate a selection of fields in my invoicing database using info I already have in my customer database, such as customer name, company, street address, city, state, postal code and email address. The idea is to type in a customer's name in my new invoicng database, then press an update button that retrieves the above info from the old customer database. This will spare me the ordeal of typing the whole lot in each time I create a new invoice.

      I've attempted using scripts and relationships but I'm such a novice and keep running into trouble.

      Cheers, Ndaba. 

        • 1. Re: Populating multiple fields from one database to another

          "I've attempted using scripts and relationships but I'm such a novice and keep running into trouble."

          Then let's walk through it ...

          1) Back up first

          2) Establish your Customer ID (if you do not currently have one)

          a. Create a CustomerID in your Customer table.  This must be a unique, auto-enter, FM-generated serial number.

          b. Then show all records in your Customer table, place this CustomerID field on the layout, place your cursor in it and Records > Replace Field Contents.  When it opens, select the second option 'replace with serial numbers.  Both values should be 1 and the check box 'update auto-enter' should be CHECKED.

          3) Establish your InvoiceID (do the same process in your Invoice table, establishing a unique auto-enter serial called InvoiceID.

          4) In your Invoice table, add a field called CustomerID (number).  You will enter the CustomerID into this field when you create an invoice.

          5) Establish a relationship from Customers::CustomerID = Invoices::CustomerID

          6) Now on your Customers layout, add a button called Create Invoice and the script will do this ... grab the CustomerID from you customer you are on into a variable, go to invoices, create a new invoice and set the invoices CustomerID with this variable.  It will be:

          Set Variable [ $customerID ; Customers::CustomerID ]
          Go To Layout [ layout based upon Invoices where you will add your detail invoice information ]
          Set Field [ Invoices::CustomerID ; $customerID ]
          Go To Field [ first invoices field you want to begin data entry ]

          Now to additional fields such as customer name, address etc ...

          Since your invoice knows the customer it now belongs to (that happened when you inserted the CustomerID into the invoice's CustomerID field), you don't need to copy the same data over into invoices if you don't want to.  Here are some examples of what you can do (and it is a business rule and must be decided by you):

          Customer name - Odds are the customer name won't change so you can just place the customer name directly onto the invoice layout and, since they are related, it will display (turn off entry to the field in browse mode).  Or you can insert a merge field with the customer name instead of a field.

          Address - you can do the same - simply placing the customer's address on the invoices layout.  But if the customer address might change, and you need to know where you shipped this invoice before the address changed, you will want to add fields in Invoices for the address and 'bring the address information over from Customers.

          You can 'bring information over' in three ways: 
          1) Set your Invoice::City field to LOOKUP Customers::City.
          2) Set your Invoice::City field to AUTO-ENTER Customers::City.
          3) At the end of your Create Invoice script, immediately after you have inserted the CustomerID, set the other fields, such as:

          Set Field [ Invoices::City ; Customers::City ]

          So the bottom line is this ...

          1) You do not need to bring a lot of information with you if you are relating the records and the information you want to bring comes from the parent record (the one-side in a 1-to-many or 1:n relationship).  The idea is that, by bringing only the parentID and creating a relationship, you can let the data flow THROUGH that relationship and display or access it for calcs any time you wish.

          2) If you DO need to bring additional information over to a related table (other than the ID), you don't need to bring it over when you first create the child record.  Instead, you can

          a) bring only the parent ID over and then use Set Field [] steps to set any other fields which must be static permanent data in the child.
          b) Set your child table to automatically insert that required static data by using auto-enter Lookup or calculation.

          And lastly, you don't really need to display the customer name on the invoices layout either.  You can create a value list (Manage > Value List)  and use values from field.  When the dual panes open, select your CustomerID from the Customers table in the left pane and specify 'also display values from second field' in the right pane and select your Customer Name.   Then down below, check 'only show values from second field'.

          If you then go to your Invoices CustomerID field and attach a value list (Inspector > Data tab > Control Style) and select a pop-up and specify this new value list, then when you view the customerID in your Invoice, it will display their name instead.

          That should get you going or make your eyes cross.  But if you take your time and step through this, it will all make sense when you are done and you will be way down the road to understanding relationships (which are CRITICAL in database and relational design in FileMaker).

          • 2. Re: Populating multiple fields from one database to another

            LaRetta thanks for taking the time to give me all that info. I'm going to work my way through it later on tonight. Too many other distractions here at the office. I'll let you know how I get on.

            Cheers Ndaba.