1 2 Previous Next 17 Replies Latest reply on Jun 11, 2010 11:26 AM by QuickRon

    Need help with importing data

    QuickRon

      Title

      Need help with importing data

      Post

      I created a Parts database. I have a main "Parts" table where the primary key is the field "Part_ID", but we go by the actual part number, which is a regular text field, "Part_Number".

       

      I created another table called "Sub-Numbers", because one main part number can have many "sub numbers" (which act as interchanges for cross-reference).

       

      I created a Parts Detail layout, which serves as the main layout for parts information. On this layout, I created a portal for the Sub-Numbers table, so when you look at one part record, the portal shows all/any sub numbers related to that one part number. I also set the portal to allow creation & deletion of Sub Numbers via this portal. And everything works great.

       

      But ... I'd like to be able to import a file into the Sub-Numbers table that will upload the main part numbers and their relevant sub numbers. The format of the Excel sheet is:

       

      Part_Number A     Sub Number 1
      Part_Number A     Sub Number 2
      Part_Number A     Sub Number 3
      Part_Number B     Sub Number 1
      Part_Number B     Sub Number 2
      Part_Number B     Sub Number 3
      Part_Number C     Sub Number 1

       

      So, after importing this data into the Sub-Numbers table, we should be able to see the Sub Numbers in the main Parts layout, in the portal.

       

      The challenge I'm facing is that this Excel file has the value for the "Part_Number" field from the main Parts table, not the "Part_ID", which is the primary key and which makes the relationship between the main "Parts" table and the "Sub-Numbers" table.

       

      In the "Sub-Numbers" table, I have 3 fields:


      Sub_ID     (primary key)
      Part_ID    (foreign key)
      Sub_Number (Sub Number)

       

      How can I import this "Master Sub" Excel file into the "Sub-Numbers" table using the "Part_Number" field (from the "Parts" table) as the determining or matching field? Right now, if I try to import the file, I'm not able to match the "Part_Number" with any field in the "Sub-Numbers" table ...

       

      Thank you,
      Ron




        • 1. Re: Need help with importing data
          philmodjunk

          Create a table with just those two fields and import your data into it.

          Relate this table to Parts by Part Number.

          Define a Part ID calculation field as simply: Parts:: Part ID.

          Now do a second import, importing your data from this table into your Sub-Numbers table, importing the Part ID and SubNumber from it.

          • 2. Re: Need help with importing data
            QuickRon

            Thank you, Phil!

            I've got the first 2 steps done.

             

            But, how exactly do I "Define a Part ID calculation field as simply: Parts:: Part ID"?

             

            Also, I'm familiar with importing data from an outside source, but how would I import the data from this new table into the Sub-Numbers table (ie. from within Filemaker)?

             

            Thank you,

            Ron

             

            • 3. Re: Need help with importing data
              philmodjunk

              Simply select the Parts ID field from your parts table in the specify calculation dialog. You can double click the field name in the list of fields to add it to your calculation.

               

              Importing from a different table in the same file works just like importing from an external file.

              Select a layout of the table you want to receive your imported records,

              Select Import records

              Select the file (The same one you have open) just like you would if it were a different file.....

              • 4. Re: Need help with importing data
                QuickRon

                Hi Phil.

                The main "Parts" table has only 15 records (as I'm testing). Of these, 8 of them have Sub Numbers. Following your instructions, I created the new table (calling it Sub1) with the 2 fields: "Part_Number" and "Sub_Number". After that, I imported the records into this table, and then I related this table to the "Parts" table via the "Part_Number" field.

                 

                Then I defined a "Part_ID" calculation field in the Sub1 table with Parts::Part_ID 

                 

                After doing this, I exited the Layout editing screen and took a look at the Sub1 layout in Table, and only the first part number is showing a Part_ID. All the other numbers are not showing their respective ID's. So, I then imported into the Sub_Numbers table and when I view the main "Parts" layout, only that first number is showing its respective Sub Numbers.

                 

                Would you happen to know why the other part numbers in the Sub1 table are not pulling their Part_ID from the main "Parts" table?

                 

                Thank you,

                Ron

                 

                • 5. Re: Need help with importing data
                  philmodjunk

                  It sounds like the part number text in the two fields don't actually match even though they look like they do. I'd check your data in both tables for invisible characters such as a space character that may be keeping your part numbers from matching.

                  • 6. Re: Need help with importing data
                    QuickRon

                    Hi Phil.

                    I'm not seeing any spaces or hidden characters. I went ahead and deleted all the records in the Sub1 table and I deleted all the records in the main Parts table. I then re-imported the 15 parts into the Parts table, and then re-imported the 8 parts that have Sub Numbers (a total of 19 records) into the Sub1 table.

                     

                    Now, none of the parts in the Sub1 table are showing the Part_ID (from the Parts table). So, I'm stumped.

                     

                    Any ideas?

                     

                    Thank you,

                    Ron

                     

                    • 7. Re: Need help with importing data
                      philmodjunk

                      Are both fields the same type? (Both text or both number)

                      • 8. Re: Need help with importing data
                        QuickRon

                        I have the "Part_Number" field in both tables (Parts, Sub1) and they're both Text. The "Part_Number" field in the Parts table is Indexed.

                        • 9. Re: Need help with importing data
                          philmodjunk

                          That should work, can't think why it wouldn't. If you want, you can upload an empty copy of your file to a file share site and port the link here so that I can take a look at it.

                          • 10. Re: Need help with importing data
                            QuickRon

                            That would be great!

                            Can you recommend a file sharing site?

                            Also, am I able to PM you on this forum with the link?

                             

                            Thank you,

                            Ron

                             

                            • 11. Re: Need help with importing data
                              philmodjunk

                              You can't attach a file to a PM :smileysad:

                               

                              I use http://www.4shared.com , but many others work just as well.

                               

                              PS. if You PM me I can send you an email address to which to send the file, but that keeps other participants from joining in on the fun. :smileywink:

                              • 12. Re: Need help with importing data
                                philmodjunk

                                OK, I've looked at your file.

                                 

                                I first imported the file directly into parts with unique values enforced to give me a set of matching data to check.

                                Then I imported your data into the sub1 table.

                                All the sub1 records showed the same part ID in the calcuation field.

                                I then checked the field definition and found that global storage had been specified and that's the problem here.

                                Find the field definition and double click it.

                                Click the storage button and clear the global storage check box.

                                Click OK until your back to your layout.

                                 

                                Now it should work for you.

                                • 13. Re: Need help with importing data
                                  QuickRon

                                  Hi Phil.

                                  I'll give it a shot, but just to let you know that originally I didn't have the Global storage checked on and it wasn't working. I then turned on the Global option and had left it there. But, I'll try it again with the Global option off.

                                   

                                  Thank you!

                                  Ron

                                   

                                  • 14. Re: Need help with importing data
                                    philmodjunk

                                    Assuming changing the storage back from global doesn't fix the issue...

                                     

                                    Since the problem doesn't occur when I import the data in your spreadsheet into both tables, that suggests there is an issue with the data currently stored in your parts table when you compare part numbers in the two tables.

                                    1 2 Previous Next