6 Replies Latest reply on Jul 17, 2014 10:07 AM by philmodjunk

    converting excel file and connecting to another converted excel file

    HollyGiovannetti

      Title

      converting excel file and connecting to another converted excel file

      Post

           I have to excel files. One is a list of finished product our company makes, each with an individual item number. Then I have an excel file that inventories all the parts we use in the shop. These also have item numbers. I need to link one product to many item numbers, but most item numbers will go to many different products. Here are my problems. While all the inventory parts have individual numbers, they are not listed at all with which finished product they go into. My plan is to convert the files into filemaker, but I am not sure how to connect the finished product with the many parts that go into it. I know that I will need a table connecting the finished product and the parts inventory tables. I know that each has to have a parent key in there individual table and a child key in the middle connecting table. What I can't wrap my head around, is how to connect the many parts with the finished product. How to I go through each individual part and connect it to each finished product it is a part of?  

        • 1. Re: converting excel file and connecting to another converted excel file
          philmodjunk

               Do you plan to use the BOM table that I recommended? If so, I can tell you how to import this data.

          • 2. Re: converting excel file and connecting to another converted excel file
            HollyGiovannetti

                 yes, my plan is to use the BOM table to connect Finished Products table to Inventory Parts table  :)

            • 3. Re: converting excel file and connecting to another converted excel file
              philmodjunk

                   Then this data needs to appear in two tables, Parts and the BOM. If you have a "master list" file of all parts, you can import that file into parts. If you do not, let me know and I'll show you how to use these individual excel files to set up the records in Parts without getting any duplications.

                   But to import into the BOM table, here's the steps for a manual import. A scripted import will be very similar:

                   Go to a layout based on the BOM table.

                   Select Import Records | File from the File Menu

                   Use the dialog that opens to find and select the Excel file from which you want to import this data.

                   Keep the default settings to add new records, but only map the column with the itemID to an ItemID field in the BOM table. If there is a qty, column in the excel file map this to a Qty filed in the BOM. No other data is likely to be needed for this import unless you have some sort of cost figures included that are specific to this BOM (as opposed to a cost figure that is the same for a given part no matter where it is used. That cost figure should be imported into the parts table.) Any name or description field need not be imported here as that data will be drawn from your parts table.

                   Import your records.

                   Then, if the ProductID field in the BOM is not present on your layout, add it to your layout (or to your table view).

                   Click into this field on one of the records just imported and type in the Product ID.

                   Then use Replace Field Contents from the Records menu to copy this value to all the parts records.

              • 4. Re: converting excel file and connecting to another converted excel file
                HollyGiovannetti

                     So 1st I need to import the finished product table and the parts inventory table, adding in the pk for both tables so that when I set up the BOM table, it will have the fk for both right?

                • 5. Re: converting excel file and connecting to another converted excel file
                  philmodjunk

                       That raises a whole new issue and is the right question to ask here. "ItemID" is, apparently, data being imported from your excel file. Yet the __pk and _fk values in a database are normally internally generated values. The trick is to use ItemID in a special, temporary use relationship to pull in the needed __pk value from Parts. The __pk value from Products isn't as big a deal as you only need to manually enter it once and then use Replace Field Contents to assign it to the other newly imported BOM records.

                       Here's how to do it for __pkPartID

                       Make a new table occurrence of products and link it in like this:

                       BOM::ItemID = Parts|ItemID::ItemID

                       with the correct data imported into Parts (enable auto-enter options so that the __pkPartID will generate a serial number value in each imported record), you can import your records and use:

                       Replace Field Contents on the BOM::_fkPartID field to copy over the value of Parts|ItemID::__pkPartID into each newly imported BOM record. Once you have all your data imported, you can delete the BOM::ItemID field and also the Parts|ItemID table occurrence.

                  • 6. Re: converting excel file and connecting to another converted excel file
                    philmodjunk

                         Just had a major Brain Short Circuit with that last post. I've corrected the error here in the forum. but if you are reading these responses as email, you won't see the corrected copy until you pull this thread up in the forum.