5 Replies Latest reply on Oct 17, 2013 10:40 AM by philmodjunk

    Importing Data to FMPro 12 from Excel

    wstonejr

      Summary

      Importing Data to FMPro 12 from Excel

      Product

      FileMaker Pro

      Version

      12.v4

      Operating system version

      OS X v 10.8.5

      Description of the issue

      Thanks to comments by PhilModJunk and TKnTexas, I was able to set up a table for importing data from Excel.  When I examine the table after the import, all of the fields are properly imported, except for the TIME BILLING ID MATCH FIELD, which remains blank.  When I switch from  the time billings data table to the time billings detail table, the newly imported data does not appear and the fees are not included in the total.

      I have checked the format of the TIME BILLING ID MATCH FIELD in FMP and Excel and they are both set to text (which is what I found in FMP). I also have the TIME BILLING MATCH FIELD in the Excel file matched with the TIME BILLING MATCH FIELD in the FPM import field mapping dialog box. The only other thing which I noticed in the manage database dialog box is that the TIME BILLING ID MATCH FIELD is listed as "Indexed, Auto-enter Calculation replaces existing values", but I am not sure how that affects the import.

      I would appreciate any suggestions.

      Steps to reproduce the problem

      Importing data from Excel version 2010

      Expected result

      All fields imported

      Actual result

      TIME BILLING ID MATCH FIELD is not imported

      Exact text of any error message(s) that appear

      None.  The program reports that all records are imported without errors.

        • 1. Re: Importing Data to FMPro 12 from Excel
          philmodjunk

               And was the column in the Excel file that contains this data mapped to a field in the TIME BILLING ID MATCH FIELD in FileMaker?

               You are describing a portal which tells us that you have two table occurrences--probably two tables linked in a relationship. The match field would seem to be the field on which the relationship is based. Normally, the parent record uses an auto-entered value--usually a serial number to uniquely identify each parent record and then this value is used to link to matching records in the child table. (The records in the portal.)

               Into which table did you import data? The Parent or the Child?

               Am I correct about the relationship?

               What kind of data is in that column of the excel file? Text, numbers or ??

               And how do you ensure that the excel file has the correct data so that the relationship will work correctly?

          • 2. Re: Importing Data to FMPro 12 from Excel
            wstonejr

                 I enclose a PNG file with the table relationships from my database.  I am trying to import to the Time Billing Data table.  

                 I cannot tell if the Time Billing ID Match Field is the parent or the child, as I do not see the terms described or defined in any of the help screens.  I also cannot tell if you are correct about the relationship.

                 The data in the column which is not importing properly is text data.  I check the formatting of the column in Excel before I try to import it.

                 I believe that this answers your questions.  If not, please let me know.

                  

            • 3. Re: Importing Data to FMPro 12 from Excel
              philmodjunk

                   Not fully, but it helps. What data type is the Time Billing ID Match Field in the Time Billing Data table? When you check this field for correctly imported data, make sure that you are looking at the correct field in the correct context. You should use a table based on the Time Billing Data table to check this, not a layout based on a related table. (gotta cover the obvious here as well as the not so obvious...)

                   How would ANY data imported into this field correctly match to records in the Time Billings table?

              • 4. Re: Importing Data to FMPro 12 from Excel
                wstonejr

                     Thanks again for your comments.  When I look at the Time Billing ID Match Field in the Time Billing Data layout, the field type is indicated as "Text".  I am viewing the data in Browse Mode and the View as Table Option in the View Menu is checked.

                     Your last question is a good one.  I thought that importing the data would be the most expedient way to add the new records to the data table.  I have not yet found a way to either copy and paste the ID into the Time Billing ID Match Field or some way to automatically populate the field so that the ID is the same as all of the records which have been manually entered (i.e., "1").  If there is another option, I am willing to consider it.

                      

                      

                      

                      

                • 5. Re: Importing Data to FMPro 12 from Excel
                  philmodjunk

                       When I look at the Time Billing ID Match Field in the Time Billing Data layout, the field type is indicated as "Text".

                       You cannot check this on the layout, you have to check it in Manage | Database | Fields. All you can see in the layout is the data "format" and this is not the same as the data type. Unfortunately, FileMaker allows you to mix number and text data in fields of type number and this can appear to work only to fail in certain situations.

                       Checking there in a copy of this file shows that this is a text field. It also shows that it auto-enters a value from a global variable. If you enabled auto-enter options during import, this may have overwritten the imported data with a null value if the variable was not currently set to a value.

                       

                            Your last question is a good one.

                       I'm not trying to tell you that you shouldn't do this or that it can't be done, but I am trying to point out how a relationship like this works. The match field in Time Billing is an auto-entered serial number value. Before imported data can be linked to a given record in Time Billing the value in TIME BILLING ID MATCH FIELD for each imported record has to be given a value that exactly matches the value of Time Billing ID of a record in the Time Billing table. Since this value is internally generated, I don't see how your excel file could possibly have the correct value to import.

                       Perhaps you have copied and pasted a value from the Time Billings::Time Billing ID field into this column in the excel file.

                       Perhaps you have modified the original design of the database to support an externally generated ID that you can import

                       or maybe you should use a different way to get the correct matching value into this field for your imported data such as creating a new Time Billings record and then using the ID created for that record with Replace FIeld Contents to update all your newly imported records to match to the new Time Billings record.

                       And other options are possible.