1 2 Previous Next 16 Replies Latest reply on Jul 26, 2013 12:20 PM by TomWaddington

    Copy table record data from table to table

    TomWaddington

      Title

      Copy table record data from table to table

      Post

           I'm new to FileMaker Pro 12 and have been beating my head against the wall for days trying to figure out a solution to a problem (If a solution even exists).  The problem is as follows:
           (I had no problem accomplishing this task using a script in and old DOS based database problem (Paradox 4) but have not been able to accomplish this with FileMaker Pro 12 advanced.)

           I am using two tables,(Books and Address) which have the following fields:
           Books:
           Date : InvoiceNumber : SupplierInv.# : Customer : GivenName : Street : City_Prov : PostalCode : PhoneNumber: PSTNumber : P.O.Number : Wholesale : Description :  QuotedPrice : MoneyReceived : GSTRecd : PSTRecd: TotalCost : Deposit : Profit : Shipping : InventoryCost : Outstanding : MOP : ServiceCharge : GSTPaid : PSTPaid

           Address:
           Date : InvoiceNumber : Customer : Street : City_Prov : Postal Code : PhoneNumber : PST Number : P.O.Number : Wholesale

           What I am trying to accomplish is as follows:
           When I enter data into the Books::Customer field, I would like it to query the "Address" table to see if there is a record matching the data entered and if so, ask if this is the correct Customer.  If so, it would copy the data from the "Address" table fields and paste them in the appropriate "Books" table fields.  If not, it would go to the next match from the "Address" table and ask again.  If there is no match, once the last field in the "Books" table that matches last field in the "Address" table is filled in, the appropriate data entered in the "Books" table would be copied into a new record in the "Address" table so that it could be used at a future date in a similar query.

           I had no problem accomplishing this task using a script in and old DOS based database problem (Paradox 4) but have not been able to accomplish this with FileMaker Pro 12 advanced.  I can post a copy of the portion of the Paradox script that I used to accomplish this task if that would help make the problem more clear.

        • 1. Re: Copy table record data from table to table
          philmodjunk

               Since FileMaker is a relational database, you don't acutally need any script at all for this.

               There are two basic ways to accomplish this and both are spelled out in this thread: Auto Fill

               Both methods require a relationship. Using the fields that you have currently defined, it would look like this:

               Books::Customer = Address::Customer

               But I suspect that Customer is a name field and name fields make for very unreliable match fields. An auto-entered serial number that uniquely identifies each customer makes for a better option.

          • 2. Re: Copy table record data from table to table
            TomWaddington

                 Thanks for your assistance.  I followed everything successfully up to this point:

                 "If you choose option 2, you'll need to define matching fields in Main and set each to use the Looked Up Value option to copy data from the corresponding field in Contacts."

                 I searched through "Help" to no avail to clarify this.  I have tried to decipher these instructions for a few hours with no luck.  Does this mean that I have to set up equal (=) relationships between each of the fields that I want to copy over to the other table?  I tried this and when I tried to enter a "Customer" in the Books::Customer field, using a pull-down menu from the CustID field,  I get an error saying "This field can not be modified until "Street" is given a valid value."

            • 3. Re: Copy table record data from table to table
              philmodjunk
                   

                        Does this mean that I have to set up equal (=) relationships between each of the fields that I want to copy over to the other table?

                   No. By "matching fields", I meant that for every field in Address that contains data you want to copy to Books, there must be a coresponding field defined in the Books table to hold that copied data so that you can set up a looked up value field option on it to auto-enter this data. From what I see in your post, you have already done this part. I did not mean Match fields to use in the relationship. Your Customer fields should be the only match fields.

              • 4. Re: Copy table record data from table to table
                TomWaddington

                     Ok.  My "Match field" is set up to CustomerID.  Now to import the date from the found file, I have tried using a scripttrigger on the "Value List" to run a script that checks to see if the "Customer" field is empty on the "Address" table ( IF [IsEmpty (Address::Customer)] ) and if so, copy the relevant fields from the new data input into the "Books" table to create a new "Address" table record for future use.  "Else If" there is data in the "Customer" field of the "address" table, it copies the relevent data from that record into the appropriate fields in the "Books" new record I am creating.  Needless to say, none of this worked either.

                     I still don't know how to accomplish this part of the Post that you referred me to previously: "If you choose option 2, you'll need to define matching fields in Main (Books) and set each to use the Looked Up Value option to copy data from the corresponding field in Contacts (Address)."

                      

                • 5. Re: Copy table record data from table to table
                  philmodjunk

                       To repeat, you do not need a script for this and thus you do not need a script trigger. This is accomplished via auto-enter field options specified for each field where you want to see data "looked up" from the Address table. There are step by step instructions for this in the link that I provided at the beginning of this thread.

                       You can also open the Invoices starter solution that comes with your copy of FileMaker and note how items listed on an invoice look up a unit price from the products table if you want to examine an actual file that has been set up to do this.

                       Ps. To access field options for a given field, Open Manage | Database | Fields, find the field's definition and double click it. (Or click it once to select it and then click the options button.)

                  • 6. Re: Copy table record data from table to table
                    TomWaddington

                         Ok.  I have the two tables "Books" and "Address" related with the "CustomerID" field.  In Books, I have the "CustomerID" field with a value list which lists the Customer ID number and the combined first and last name of the Customer as well as "Other" (for entering a new customer).  I have all the appropriate fields in "Books" table set to "Look-up" the data from the "Address" table fields which it copies to the similar fields in in "Books".  When I select "Other" from the Value list in "Customer", it opens a window where I enter the next higher number for a new "CustomerID" number.  On entering this, it creates a new record in the "Address" table with the new "CustomerID" number filled in correctly and then returns me to the next active field on "Books" which happens to be the Customers last name or company name.

                         Now for my problem.  I can carry on filling in the address information as per normal in the "Books" table but how do I get it to post into the new record in the "Address" table (which has the new CustomerID number only in it) automatically so that it will be there the next time I sell something to that person or company?  I tried using an Auto-enter Calculation using the following calculation but that didn't work: If ( IsEmpty ( Address::Customer) ; Lookup ( Books::Customer ))

                    • 7. Re: Copy table record data from table to table
                      philmodjunk
                           

                                When I select "Other" from the Value list in "Customer", it opens a window where I enter the next higher number for a new "CustomerID" number. 

                           Your Customer ID should be an auto-entered serial number. That way the value does not have to be manually entered and FileMaker will protect you against data entry errors and the possible problems that would ensue should you get two records with the same ID.

                           

                                On entering this, it creates a new record in the "Address" table with the new "CustomerID" number filled in correctly and then returns me to the next active field on "Books" which happens to be the Customers last name or company name.

                           Set up a button with a script that takes you back to your books layout. That script should take the automatically generated ID in the new address record and enter if for you in the matching ID field of your current Book record. WHen it does this, that should, in turn trigger the needed look up on all your fields.

                           Your script might look like this:

                           Set Variable [$CustomerID ; value: Address::CustomerID ]
                           Go to Layout ["Books" (books)]
                           Set Field [ Books::CustomerID ; $CustomerID ]

                      • 8. Re: Copy table record data from table to table
                        TomWaddington

                             I don't think you understand the flow of the database that I am trying to achieve.  I start out in the "Books" layout, enter a Customers invoice number if there is one, auto-enter an Invoice number for the transaction, move to "CustomerID" to check the value list (from "Address" table) for a previous transaction from that customer,  if the Customer is in the value list, select them so the "CustomerID" number will match the current new transaction and "LookUp all the "Address" table data from the record and copy it to the new record I am creating for this transaction,  move on to the rest of the fields in the "Books" table to complete the rest of the fields for the transaction such as price, tax, etc.  So far, I have this working correctly.

                              The problem arises when there is no matching record in the "Address" table to Lookup the data from, hence the use of the "other" in the value list for the "Books::CustomerID" field.  This problem could be addressed if the "other" function in the LookUp list could trigger a jump to the "Address" table, auto-enter a "CustomerID" field, allow the rest of the fields to be filled in and then jump back to the "Books" table and perform a LookUp of the data that was just entered into the "Address" table, copying it into the same fields in the "Books" table and then carry on with data entry in the rest of the "Books" table fields but I don't see how to achieve this.  I'm trying to make this as clear as possible so that you can hopefully understand.

                             I believe what you are suggesting is to manually go to the "Address" table, Insert a new record with an Auto-generated "CustomerID" field and then manually go to the "Books" table and start again.

                             In your suggestion, how did I get to the "Address" table from the "Books" table?

                        • 9. Re: Copy table record data from table to table
                          philmodjunk
                               

                                    This problem could be addressed if the "other" function in the LookUp list could trigger a jump to the "Address" table, auto-enter a "CustomerID" field, allow the rest of the fields to be filled in and then jump back to the "Books" table and perform a LookUp of the data that was just entered into the "Address" table, copying it into the same fields in the "Books" table and then carry on with data entry in the rest of the "Books" table fields but I don't see how to achieve this.  I'm trying to make this as clear as possible so that you can hopefully understand.

                               This is exactly what I understood you to be describing. I suggest two scripts:

                               Script 1, performed via OnObjectSave on the drop down of customers and "other":

                               Go to layout ["Address" (Address)]
                               New Record/Request

                               Put a button on this layout labeled "save" or "Return to Books" or some such to perform this script:

                               Set Variable [$CustomerID ; value: Address::CustomerID ]
                               Go to Layout ["Books" (books)]
                               Set Field [ Books::CustomerID ; $CustomerID ]

                               A more sophisticated version of this is possible where script 1 opens a new window for entering the info for the new customer and then script 2 closes that window. This can result in a kind of "pop up dialog box" for entering new customer info.

                          • 10. Re: Copy table record data from table to table
                            TomWaddington

                                 A few problems arise with this.  The "other" part of the value list requires data to be entered so I had to go back to manually entering the next record number into the "other" box and changing the "Address::CustomerID" to a LookUp of that value.  This correctly created a record in the "Address" table with the proper ID.  I had to change your first script last line from "New Record / Request" to "Goto Record/Request/Page [Last]" which took me into the correct record in the "Address" table and allowed me to enter the appropriate data.  It would be nice it would the ID value list would generate the "next" record number from the Address table and automatically append it to the value list so the "other" box wouldn't be open to the wrong data type or an incorrect entry error.

                            • 11. Re: Copy table record data from table to table
                              philmodjunk

                                   Now you have me confused here. I've assumed that CustomerID uniquely identifies a record in Address as you apparently have only two tables involved here: Books and Addresses. Do you have a third table named Customer with a customer ID?

                                   With only two tables, addresses and Books and customerID defined as an auto-entered serial number in Addresses, what you describe should not be needed, the scripts should function exactly as I posted them.

                              • 12. Re: Copy table record data from table to table
                                TomWaddington

                                     I'm sorry for the confusion.  You were right in your assumption that there are only 2 tables involved with this problem.  What it boils down to, is the "Value list" called by the "Books::CustomerID" field lists the "Address::CustomerID" field as well as the customer name as the second field and at the bottom of the list is the "Other" option which can be selected if there is no matching data you want to copy from the "Address" table.  Unfortunately you have to enter some kind of data into the "Other" box that opens up in order for the Script trigger "OnObjectSave" to work on the first script you suggested.

                                     One possible solution might be to modify your second script which returns you to the "Books" table to complete data entry.  Maybe the first action before continuing on with the second script, could be to create new record in the "Address" table which could trigger your auto-entry of a sequential "CustomerID" number and then possibly enter  the text:"New Record" into the "Address::Customer" field (which could be overwritten during the next new record entry in the "Address" table).  This would eliminate the requirement for the "Other" option as this new record created would now show up in the value list for selection.

                                     Do you think this will work or is there a better way to accomplish this?

                                     There is another problem that arrises when I select any item from the "Books::CustomerID" field value list.  it selects the correct file in the "Address" table and allows the data to be Looked up by the the "Books" table correctly and remains in the "Books" table to allow for the completion of the rest of the record.  The problem is: In doing this, say I selected Record "2" from the "Address" table; an ADDITIONAL empty record is created in the "Address" table with the "CustomerID" value of "2".  I can't figure out how to prevent this from occurring.  Any ideas?

                                     If I select the last choice from the "Value List" when creating a new record, the program goes to the "Address" table to the "New Record" (as generated by the 2nd script of yours that I modified to create the last record with the correct record number) but creates an ADDITIONAL new record in the "Address" table below that record but leaves the correct "New Record" (the one above the new blank record) selected for editing.

                                     The second script seems to work flawlessly as I intended and eliminates the need to use the "Other" option in the Value list.  It  is as follows:

                                Set Variable [$CustomerID; Value:Address::ContactID]

                                New Record/Request

                                Set Field[Address::Customer;"New Record"]

                                go to layout ["Books" (Books)]

                                Set Field [Books::ContactID; $CustomerID]

                                     (I placed the scripttrigger for the first suggested script in the "Books:Customer" field as you suggested but can't use it in the "Books::CustomerID" value list option "Other" as I mentioned previously.  Is this what you intended?

                                      

                                • 13. Re: Copy table record data from table to table
                                  philmodjunk

                                       I suggest that you not use the "other" option for this. That's a feature intended for use with custom values value lists instead of "use values from a field" value list. Add a "new customer" button next to this field and use it to start a record for a new customer.

                                       The other option is to specify "--New--" as a value in your field based value list. This can be done by adding a special record in Addresses or using a special calculation field with: List ( CustomerNameFIeld ; "--New--" ) as the field used as a source of names for your value list. I had mistakenly assumed that you were using some such option as I was under the impression that "other" did not appear when your value list was set up to list values from a field.

                                       Either way, your script can detect the fact that --New-- was entered into the field and can then generate the needed steps to create a new record in Addresses and then copy that newly generated ID value back into the current record in Books in order to look up the needed data.

                                  • 14. Re: Copy table record data from table to table
                                    TomWaddington

                                         This is what I achieve by using the second script as I outlined above.  I no longer require the "Other" part of the value list.  The problem still exists with any selection in the value list, gets the required value as it is designed, but also creates a new field in the "Address" table with the same CustomerID as the one selected from the value list.  How do I stop this from happening everytime I use the value list?

                                    1 2 Previous Next