1 2 Previous Next 18 Replies Latest reply on Jan 11, 2011 1:13 PM by trizko

    Combining tables to view inside of a portal of records.

    trizko

      Title

      Combining tables to view inside of a portal of records.

      Post

      Legend:

      []=table

      >--- = many to one

      ---< = one to many

      Tables:

      [PARTS] - tracks part model numbers and types

      [PRODUCTS] - tracks product names

      [PART_SERIALS] - tracks individually used part serial numbers

      [UNITS] - tracks individually sold product serial numbers

      [LINE_ITEMS] - tracks quantity and extended price of units

      ---------------------------------------------------------------------------------------------------------------------------------------

      I have 4 tables oriented like so:

      [LINE_ITEMS]>---[UNITS]>---[PRODUCTS]---<[PARTS]

      and

      [UNIT]---<[PART_SERIALS]>---[PARTS]

      The problem: My company sells products as a whole, BUT ALSO parts by themselves.

      How can I make this work smoothly?

        • 1. Re: Combining tables to view inside of a portal of records.
          philmodjunk

          Not sure why you have so many tables here for this. The typical invoicing system uses:

          Invoices---<LineItems>---Products

          Where Invoices is one record per customer's sales transaction, LineItems list the individual items ordered, their quantities, descriptions, unit prices and compute a line item cost. Products has one record per type of product and can list both the serial numbers, descriptions and current price for each.

          That leaves me puzzled as to why you need a table for units. If it was documented in your first post as Units---<Products, I'd guess that One unit can be listed in line items, but consists of many parts listed in products, but that's not what you posted here.

          To answer your question, you can add an ID number for the entire product in the same table where you list the parts. This allows you to sell either a product or just a part as the database treats them the same. That probably won't do it for you here. You likely need to document that if you sell Widget 345, that sells parts a, b, c and 2 zzz parts. Sticking with my table structure for sake of explanation, you can add a join table, Product_Parts that lists the ProductID for the complete product in one field and the ProductID numbers of the parts in a second field. (The number of parts can be a third field in this table.)

          Products---<Product_Parts>---Products 2  (products 2 is a second table occurence of Products)
          Products::ProductID = Product_Parts::ProductID
          Products 2::ProductID = Product_Parts::PartsID

          You can then include a script that is triggered when you select a an item in your line items portal on Invoices that checks to see if the selected item is a "kit" (consists of multiple parts in inventory). If so, the script can use the list of records in Product_Parts to list the kit's parts in the invoice and can make additional any inventory updates as well.

          • 2. Re: Combining tables to view inside of a portal of records.
            trizko

            How can I eliminate the UNITS table?

            Products is used to list the products that we carry, and units tracks the serial number of each individual product.

            Example: My company's product catalog has a product called XYZ. My company just produced 3 XYZ products with serial numbers; 123, 234, and 345.

            This needs to be tracked along with the serial numbers of each part that is inside each unit.

            • 3. Re: Combining tables to view inside of a portal of records.
              philmodjunk

              I didn't say you could do away with the units table.  I just didn't understand its purpose.

              I read your last post to say that you can have three different serial numbers for the same product name? And you can list item 123, 234 or 345 on an invoice and all will show XYZ as the product name? What's the purpose for having 3 different serial numbers?

              That seems a little odd in terms of business practice, but not a major issue in terms of database structure. In my post, substitute your units table for my products table.

              • 4. Re: Combining tables to view inside of a portal of records.
                trizko

                I'm sorry, i know its just me but I can't seem to grasp what you are saying.

                Are you suggesting that I eliminate the Parts table and just add all the part records in the Products table?

                • 5. Re: Combining tables to view inside of a portal of records.
                  philmodjunk

                  What's the difference between a "part" and a "product"?

                  If any given product consists of multiple parts and both a product and its parts need to be listed on your invoice in addition to selling individual parts, then I would combine the part and product records in a single table, but use a related table to list the ID's (FileMaker serial numbers--not manufacturer serial numbers) of all parts that make up a given Product.

                  • 6. Re: Combining tables to view inside of a portal of records.
                    trizko

                    "I read your last post to say that you can have three different serial numbers for the same product name? And you can list item 123, 234 or 345 on an invoice and all will show XYZ as the product name? What's the purpose for having 3 different serial numbers?

                    That seems a little odd in terms of business practice, but not a major issue in terms of database structure. In my post, substitute your units table for my products table."

                    Can you please elaborate on this.

                    • 7. Re: Combining tables to view inside of a portal of records.
                      philmodjunk

                      Where I mentioned using a Products table as the source of looked up values (Price etc) and as the master list of all available items that can be selected in the Line Items portal, I think you can use your Units table instead.

                      • 8. Re: Combining tables to view inside of a portal of records.
                        trizko

                        Ok Phil, I really appreciate all the help and I think I will just go your way with a table setup like so:

                        [Invoices]---<[Line_Items]>---[Products]---<[Product_Parts]>----[Parts](occurrence of [Products])

                        I have one last question, and I know you do not understand why, but I need to be able to track the serial numbers of each unit of a product sold. Where would a "Unit" or "Serial" table come into play in this setup?

                        • 9. Re: Combining tables to view inside of a portal of records.
                          philmodjunk

                          Parts is not an occurence of Products. It's a separate data source table.

                          Do the serial numbers uniquely identify a part or product? OR if you order 5 identical products, each has a different serial number?

                          And the same question applies if these are part serial numbers...

                          • 10. Re: Combining tables to view inside of a portal of records.
                            trizko

                            "Products---<Product_Parts>---Products 2  (products 2 is a second table occurence of Products)"

                            ^is this not what you told me to do? Well I did it anyway and it works so thanks for that.

                            AND

                            If you order 5 identical products, you get 5 different serial numbers, and yes the same applies for parts.

                            • 11. Re: Combining tables to view inside of a portal of records.
                              philmodjunk

                              Sorry, I'm brain sharing between two different thread and thought I was referring to the other thread here. Please disregard that statment.

                              You can track the serial numbers by adding a field to line items to record the serial number of the item sold.

                              • 12. Re: Combining tables to view inside of a portal of records.
                                trizko

                                but one line item can have a quantity of 2 or more of the same product.

                                • 13. Re: Combining tables to view inside of a portal of records.
                                  philmodjunk

                                  You can make this a text field with each serial number on a different line in the same field--that avoids a portal inside a portal problem with your line items portal.

                                  You might then use a script to use the values listed in this field to create records in a related table (Log the serial number, product ID and the invoice ID. This script could be triggered when you click whatever button marks the invoice as complete. (In our system, we label a completed invoice as "printed") The separate table would be useful for many reporting situations such as a summar report that lists every item sold by serial number possibly grouped by date sold or by invoice.

                                  Alternatively, you could build this list directly in this new related table with a portal that is separate from LineItems but synchronized via scripts with a specific row in the LineItems portal. (Click a row in LineItems and a script filters the other portal to list the serial numbers...)

                                  • 14. Re: Combining tables to view inside of a portal of records.
                                    trizko

                                    "that avoids a portal inside a portal problem with your line items portal."

                                    What do you mean by this?

                                    1 2 Previous Next