9 Replies Latest reply on Oct 17, 2014 8:06 AM by themactech.

    Relational Queries... is the one?

    themactech.

      Title

      Relational Queries... is the one?

      Post

      Hi everyone...

      First let me thank the folks that have given me some great advice and help over the past couple months. I appreciate everyone's kindness and willingness to contribute on this forum.

      I have a products table... normal stuff... primary key.. product name, etc. This table is to hold any product/service that the company has to purchase or have available for sale. Some of the products(assembly items) are used to  build other products (sub assemblies) and sub assemblies are used to build the final product (assemblies)

      I have a field called Product Use which is a drop down field where the user can select from a value list if this product is an assembly item, sub-assembly or an assembly.

      I would like to have, on my products layout, a portal where the user can select the assembly items that are used to build the sub-assemblies.

      I'm trying to figure out whether or not I need to create another TO and create a relational on the Products Use field but I think I'm confusing myself.

      Thanks so much.

      Clay

      Screenshot_2014-10-15_10.56.54.png

        • 1. Re: Relational Queries... is the one?
          philmodjunk

          Are all your assemblies, sub assemblies and products records in one and the same table?

          • 2. Re: Relational Queries... is the one?
            themactech.

            yes sir. That's the organization I was using. All Products, Materials, Assemblies, Services in one and the same table.

            • 3. Re: Relational Queries... is the one?
              themactech.

              i have a feeling I'm going to have to break the products out into separate entities like I had thought early on

              Products (assembly)
              Sub Assembly
              Materials (assembly item)

              The other end of the this is that I will also have to track inventory with the Inventory Transactions Log that was previous discussed. where subs and full assemblies will all have individual serial numbers as they are created.. and then added an removed from inventory as they are made and sold.

              So maybe breaking the entities up might make for a better solution.

              • 4. Re: Relational Queries... is the one?
                philmodjunk

                What you can use is a "Many to many self join" where you have two occurrences of Products with a join table used to link them in a many to many relationship.

                You set up a portal to the join table so that you can select existing records from products in order to build a list of materials and sub assembly items to make up what is often called the BOM (bill of materials) needed to assemble that item in your product table. A quantity field in the join table can be used to record the number of sub items needed to assemble one unit of product.

                • 5. Re: Relational Queries... is the one?
                  themactech.

                  THANKS Phil. I started working in that direction this afternoon. I will let you know how it works out.

                  • 6. Re: Relational Queries... is the one?
                    themactech.

                    This is just a follow question... just looking for an opinion.

                    Like I said above... I'm considering putting all products into one table (assembly items, sub-assembies, assemblies, services) and just tracking their ins and outs there.

                    But makes this a little unique (and maybe it's not) is that the assemblies and respective subs will all be given a unique serial number at the time they are manufactured. This where the inventory gets a little foggy for me because let's say i have one assembly with a unique manufacturing serial number. If I sell that one.. it's gone.. it's not like i can order another 10 of those because they want to track each assembly by that serial number (when the job was created, when it was manufactured and when it was sold).

                    In a typical inventory.. you have 10 pencils, you sell 10 and you order 10 more... there's no serial number involved. So that's where I get a little confused about how I want to do this. I want to track which/how many of the assemblies and respective subs they have in stock but since each one has a serial number.. do I enter each one in the PRODUCTS table as an individual record?

                    Or do I break up the entities into ASSEMBLY ITEMS, SUB ASSEMBLIES, ASSEMBLIES with each getting their own table that relates  to the Inventory Transactions log. That as Assemblies are built, I can add them into the ASSEMBLIES table along with that serial number... on the Sales Order.. the user can select one or more of the assemblies as line items and I can show the serial number along with it.

                    But then again.. as I type this... how is that any different then if I kept that all in the Products Table... maybe there isn't really a difference.. is it just my preference on how I want to handle the date? Just looking for a little clarity.

                    THANKS for listening

                    • 7. Re: Relational Queries... is the one?
                      philmodjunk

                      But will you produce more of the same item, but just with a different serial number each time?

                      Or is each product a once in a lifetime, never to be duplicated product?

                      I would guess the first as this is a common manufacturing process such as the VIN number for a car or the MFG serial number on the back/bottom of nearly anything you can purchase in the store that has been manufactured.

                      If this is the case, you'll need to set up different tables for different purposes. What we have discussed thus far is the Manufacturing Specfiications part of your database where you specify how each product will be assembled. You'd then need additional tables and relationships to manage the actual Manufacturing process. It is in those tables that you would assign your MFG serial number to each manufactured item that you add to your inventory during production.

                      • 8. Re: Relational Queries... is the one?
                        themactech.

                        yes the former is true. We will continue to create more of the same.. just with a different MFG serial for each.

                        i see.. so i can create my products table where I relate and show all the items and subs together to build an assembly.. thus a BOM table essentially... then as the subs and full assemblies are manufactured I can add them to their respective tables (Sub Table, Full Assembly Table along with their own MFG serial) that are added to the inventory and then can be removed from the inventory on a sales order.. where the sales order will show the related MFG serial as well. I think i got that right.

                        Here's a little twist... as you know Sub Assemblies have sequences... or tasks.. that need to be completed to build the Sub. As these the sequences are completed, the work is supposed to mark the task complete and sign off on it. When all the sequences are marked complete the Sub status changes to complete. But here is my question... (and thanks for hanging in there with me).

                        The sequence steps for each sub... never change. They have the same description and always in the same order to complete. I'm thinking of creating a sequence table where I can create static records for each sequence along with description and which sub they go with. Then when a new job is created and and through a script a new set of records are created.. one assembly (with new MFG serial) record, whatever number of sub-assembly records and the series of tasks that need to be created to complete these subs. The tasks will pull the respective description and such from the static sequence table. Essentially one assembly record could potential have almost 50 related records create (1 assembly x 6 subs x 8 tasks per sub). If the job order calls for 10 assemblies.. that's 500 records that need to be created with one click of a button. Once the subs are marked complete.. the assembly is marked complete and then added to the inventory.

                        Am I on the right track with that thinking?

                        THANKS

                        • 9. Re: Relational Queries... is the one?
                          themactech.

                          Phil,

                          I just wanted to thank you again for all the advice. As you may see in a recent post Products Table showing BOM portal question I was able to create the proper relationships that show essentially a BOM in a portal and a list of products the respective assembly items are used in. I really appreciate all your help up to this point. 

                          Clay