3 Replies Latest reply on Apr 25, 2012 6:19 AM by philmodjunk

    Linking price database

    NormShea

      Title

      Linking price database

      Post

      This is probably a pretty basic question but here goes.  I have a Parts Inventory database with fields that include Item Description, Part Number and Cost.  I also have a Equipment Maintenance Inventory database with a fields relating to repair history.  The Equipment Maintenance Inventory database has a Part Number field as well.  What I would like to do is be able to enter the part number in the Equipment Maintenance Inventory database and have it pull the Item Description and Cost from the Parts Inventory database.  I have the relationship between the two databases established and I can get it to work for the first part number.  What I can't figure out is how to get it to work for successive part numbers.  Can anyone provide me with any direction or suggestions?  I've included an image of the Equipment Maintenance Inventory database

      Thanks,

      Norm

      Equipment_Maintenance_dB.png

        • 1. Re: Linking price database
          philmodjunk

          Well, with your current design, you'd need to define 15 different relationships to 15 different occurrences of your parts inventory table--one for each of your 15 parts fields.

          A much, much simpler alternative is to add a related table where you have one record for each part listed as part of the above maintenance record.

          This would look like this:

          Maintenance----<Maintenance_Part>------Parts

          Maintenance::__pk_MaintenanceID =Maintenance_Part::_fk_MaintenanceID
          Parts::__pk_PartID = Maintenance_Part::_fk_PartID

          Fields starting with __pk should be defined as auto-entered serial numbers to serve as primary keys in these tables. Fields starting with _fk (foreign key), should be defined as plain number fields.

          On the above layout, you'd replace your 15 rows of individual fields with a portal to Maintenance_Part. Not only will this work for a single relationship to the Parts table, (my name for your inventory parts database), it will also allow you to specify any number of parts for a given maintenance record, you are no longer limited to a maximum of 15 parts.

          • 2. Re: Linking price database
            NormShea

            PhilModJunk,

            Thanks for the input.  When I was setting up all those fields, I had a feeling that I wasn't going in the right direction.  Okay, so the image below is what I've got now.  I've created a "Parts_Used" db with the common element between the "Parts_Used" db and the "Equipment_Maintenance" db being the Work Order Number.  So, in the portal when the Work Order Numbers are the same, the data is displayed.  What I'm having trouble with now is how to automate creating a new item in the "Parts Used" db portal that references the existing Work Order Number and allows entry of data in the portal window.  I tried a script (the "New" button next to Extended Cost) but I can't figure out how to get it to get it to pull the Work Order Number from the record I'm working on in the Equipment_Maintenance db and add it to the Service_event field of the Parts_Used db.  I think you were trying to provide that information in your post,

            Maintenance----<Maintenance_Part>------Parts

            Maintenance::__pk_MaintenanceID =Maintenance_Part::_fk_MaintenanceID
            Parts::__pk_PartID = Maintenance_Part::_fk_PartID

            but I'm not understanding your nomenclature.  Can I do something with a script that pulls the Work Order Number from the Equipment_Maintenance record I'm currently in and inputs it into Service_event field of the Parts_Used db or am I going in the wrong direction.  Or could you explain a little more about your nomenclature so I could use your above information.

            Thanks,

            Norm

            • 3. Re: Linking price database
              philmodjunk

              You could use a script, but there's an easier way.

              Go back to Manage | Database relationships and double click the line linking work orders to parts  used. Enable "allow creation of records via this relationship" for parts used. Now you can add parts records directly to the portal.

              Maintenance----<Maintenance_Part>------Parts

              Maintenance::__pk_MaintenanceID =Maintenance_Part::_fk_MaintenanceID
              Parts::__pk_PartID = Maintenance_Part::_fk_PartID

              To explain the notation I used...

              The first line is a 'sketch' of what you should see in Manage | database | relationships. It shows three table occurrences linked in relationships. ---< stands for a one to many relationship. Maintenance is what you have named "work orders" and Maintenance_Part is what you have named "parts used". The next two lines identify the match fields used in these relationships. Did you link Parts Used to your Parts table by a PartID field?

              If so, you can set up the matching field in Parts Used as a drop down list or pop up menu of Part IDs from your Parts table. A second field can include the part description field so that you can select a part by description and not need to know the number. You can add a description field from Parts right in the portal so that you see more than just a part number in the portal.

              Then you can add parts to your list of parts on the layout by selecting parts from this value list in the parts used::PartID field.