5 Replies Latest reply on Apr 13, 2012 10:16 AM by philmodjunk

    Migration Number



      Migration Number


      hello, everybody

      well this is the situation, right now we are in a transition number process (we are changing from old to new part numbers) and my supervisor wants to keep the old number as a reference in the main table. Also  he wants to do an extra table named alias that will contain only new and old numbers (no extra info) example ; the fields will be part number (new No.) alias (old No.) partalias (newold), and  aliaspart (oldnew) but I also will have build a table with Model Numbers this table will be related to Parts Table so we can see which parts are needed to build each model. how do I relate these 3 tables since in the Model table we just want the new numbers to appear.

        • 1. Re: Migration Number

          In the Main table, add a field for the original part number. If you show all records first, a single Replace Field Contents operation can copy the data from the current part number field to the new "oldNumber" field in a single batch update.

          he wants to do an extra table named alias that will contain only new and old numbers

          Hmm, how knowledgeable about relational database design is he? If you put up a table or list view of MainTable, only put up the two part number fields on it and name the layout "alias", will that satisfy him? (Why is he telling you to build such a table?)

          You can use import records to import just the old and new part numbers into such a table, you can even use the new table option to create the table when you do so.

          I also will have build a table with Model Numbers this table will be related to Parts Table so we can see which parts are needed to build each model.

          Typically, a given part can be used in more than one model and this requires setting up a join table to link model and part records so that a Model can be linked to many parts and a part can be used in many models.


          Models::__pk_ModelID = Model_Part::_fk_ModelId
          Parts::__pk_PartID = Model_Part::_fk_PartID

          And the __pk_PartID should NOT be the part number, make that a different field and define this primary key field as an auto-entered serial number. Don't let your supervisor or other users see this ID number so that they can't come up with suggestions/requirements that would make you change it. (Primary keys should be unique and never change.) Put your part number fields (old and new) wherever you need them on layouts and use them for searching and sorting. Use the hidden serial number field for your basic relationships.

          Note: in manufacturing plants, Model_Part is often referred to as a "Bill of Materials" or BOM.

          • 2. Re: Migration Number

            Thank u for ur Help :-)

            Now I have a parts list table, and a Model Table (where I can choose the parts needed for a particular model) but now I was wondering how can  I make a report w/ this table I would like to have in this report the parts needed for a model (which I think I already figured out) but I also want to appear the part number quantity needed for a particular model and the component to which that part number belongs to example fabric, frame, etc...Where should I add those fields?

            Thanks :-)

            • 3. Re: Migration Number

              I see that you've started a new thread and I've posted an answer there. In the future, if you don't get a response as fast as you'd like, you can also post a new response to your existing thread and that "bumps" it back to the top of the recent items list.

              • 4. Re: Migration Number


                Ok we are working on a database for our bill of materials. When I started working on this database my boss had already designed it, and I am the one entering all the information but now he wants me to improve it. I know the last time I asked for help I was working on a new database b/ they wanted to see if I could make a better one but I guess they preferred to keep the database the way they have it. Now  he asked me to find out how to populate the records information.



                We have these fields:  Part No.  > PartName> Part Description> In Use> Part Weight and > Aliases


                We are changing old numbers for new ones. In the part num. field we enter the old number and all its information then we create a new num. and enter the same information.  The aliases show the old number for every new number. Example.





                Part Number

                Part Name


                In use



                Light Jumper Cable





                Light Jumper Cable










                Now he wants that when he enters the alias num. all the old record description (name, description, weight etc) to be entered automatically for the new number. Is that even possible? 


                I hope I explained myself 


                • 5. Re: Migration Number

                  Define a self join relationship between this table and a second occurrence of it.

                  In Manage | Database | relationships, make a new table occurrence of YourTable by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box.

                  We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

                  Add it to your relationships like this:

                  PartsTable::alias = PartsTable 2::part number

                  Now you can open field options for the description field and set it to copy from the description field in PartsTable 2.