12 Replies Latest reply on Oct 14, 2013 2:44 PM by MichaelVoccola

    Kits, Part and Sub-Parts

    MichaelVoccola

      Title

      Kits, Part and Sub-Parts

      Post

           Working on a rather complex database and really need to figure out this major step. The biggest hurdle is a current feature.

           The core concept of the system is to allow multiple tenants use one file and, in addition to a number of other functions, cross-rent equipment to each other. This has created the need to have infintely-tiered pricing attached to lineitems in various tables. Essentially, it uses a compilation of security features, script steps and table relations to the show the correct buy and sell price for a given item depending on what company the user belongs to.

           The issue now, is inventory needs to be classified in a "new" way. Simple LineItems will now become Kits, Parts and Sub-Parts and have a field called "ItemClass" that defines them. The Inventory section is where all these relationships are initially put in place and that is all good. The next step is to add them to a job.

           The rather complex script is already in place for adding a "part". The challenge it adding that Part's Sub-Items and Kits. A Kit is made of Parts and each Part's Sub-Items.

           Currently, each LineItem has a Buy and Sell table associated with it. These are both TO's of LineItemPrices, and are filtered to show one record in each. (In practice, an item can be sold-up infinitely through various vendors, creating a Buy and Sell record in each table. In addition to the Buy and Sell prices is the Buy and Sell QTY. For inventory availability sake, the "Buy" price is the PullQTY, or how many of said item are going out on a job, where the Sell price is what we are billing the client for.

           So, now that we have covered the basis of how things currently work, I am trying to figure out how to manage all of this with the Kits, Parts and Sub-Parts model, as well as how to create the LineItem records for all the Parts and Sub-Parts when a Kit or Part is added to a job (it would be sort of a loop of the existing script I suppose.)

           To add to the complication, pricing for Kits (composed of multiple parts and their sub-parts) is done purely on the Kit level. Thus, on the Inventory side of things, a Kit auto-calculates it's price based on the price of all the parts that make it up (part price x QTY). However, a user can override this calculated cost using a second field. It is actually a third field that calcs the kit cost based on an IsEmpty(PriceOverride). When a Kit's cost is overridden, a calc on the relationship table that defines what parts make up a kit, determines the overriden price for the part, when it is in that kit. This calc adjusts the price of each part proportionatly so the total income from each part can be tracked appropriately for reporting purposes.

           Further, the aforementioned kit pricing adjustment that proportionally defines part prices needs to transition to the LineItems table, along with it's barrage of related tables and calcs.

           And finally, all of this needs to be able to print easily on a variety of .PDF reports!

            

           Any help, direction is great. OR, if you feel like you can tackle this project, let me know!

            

           - Mike

      OPPS_EMS.jpg

        • 1. Re: Kits, Part and Sub-Parts
          philmodjunk

               Warning: I'm typing this while ill with a major headcold. I'm going to provide a general outline of how to handle the "kit" inventory process, but not get into any implementation details at this time...

               Typical invoicing for purchase and/or rental of items is:

               Invoices-----<LineItems>------Inventory (sometimes called Products)

               One method for handling kits, "package deals" and the like where one line item can represent multiple invidual items or "parts" is to modify the relationship like this:

               Invoices-----<LineItems>------Inventory-----<Inventory|Parts>--------Inventory|KitMembers

               Where Inventory and Inventory|KitMembers are both table occurrences with the same data source table. Inventory|Parts is a join table for matching up an inventory item with any parts that make up a Kit item.

               Inventory::__pkInventoryID = Inventory|Parts::_fkParentInventoryID
               Inventory|KitMembers = Inventory|Parts::_fkMemberInventoryID

               Note that all items, whether individual items or kits are stored in the same table and you can set up an unending chain of kist and their member parts. Item A can have the parts 1, 2, and 3. Item 2 can have the parts X, Y and Z. Item Z can have the parts...

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

          • 2. Re: Kits, Part and Sub-Parts
            MichaelVoccola

                 Thanks Phil,

                 I am going to do my best to get this to also print on a .PDF report (invoice, pick list etc...). Sticking with the three-tier limit (Kits, Parts, Sub-Parts), I am not sure how to get these to display in the desired format.

                 Fields that identify the ItemClass will surely by used - I already have these in place. The hurdle is getting them to print - ideally indented - on a report. For example:

                  

            Audio Console (Part)

            Power Cable (Sub-Part)

            Firewire Cable (Sub-Part)

            Road-Case #001 (Sub-Part)

            Speaker Timer Kit (Kit)

                 Macbook Pro (Part)

            Power Supply (Sub-Part)

            Thunderbolt -> DVI adpater (Sub-Part)

                 Dell S2340L (Part)

            Power Supply (Sub-Part)

            IEC Cable (Sub-Part)

            DVI Cable (Sub-Part)

                 Should I be using sub-summaries with various sort orders etc... to get them to group correctly and apply formatting / indenting, or is a very elaborate set of overlapping calc fields that are indented and formatted, showing only data in the appropriate fields based on ItemClass the better/only option?

                 The confusion is that currently, my reports are based on the related table LineItemSellPrice, in order to display items that are "sold", as some items are added invisibly to a job as an expense that resides in the lineitems table, because to a vendor selling something to another for a job, it is a lineitem in the seller's system and the whole concept of the system is to automate as much as possible.

            • 3. Re: Kits, Part and Sub-Parts
              MichaelVoccola
                   

                        One method for handling kits, "package deals" and the like where one line item can represent multiple invidual items or "parts" is to modify the relationship like this:

                   

                        Invoices-----<LineItems>------Inventory-----<Inventory|Parts>--------Inventory|KitMembers

                   

                        Where Inventory and Inventory|KitMembers are both table occurrences with the same data source table. Inventory|Parts is a join table for matching up an inventory item with any parts that make up a Kit item.

                   

                        Inventory::__pkInventoryID = Inventory|Parts::_fkParentInventoryID
                        Inventory|KitMembers = Inventory|Parts::_fkMemberInventoryID

                   

                        Note that all items, whether individual items or kits are stored in the same table and you can set up an unending chain of kist and their member parts. Item A can have the parts 1, 2, and 3. Item 2 can have the parts X, Y and Z. Item Z can have the parts...

                   Also, while I understand that this approach would work in a basic scenario for billing where LineItems stores the qty of the package, does this approach doesn't seem to allow the flexibility that I would need. The user needs to be able to not only add a kit, but also change the pricing and quantity per 'part', per package. Additionally, the approach would need to drive the availability of each part in the package based on a date-range.

                   This has made me come to think I need to use a looping script that creates a new LineItem record for each part of a package, but also identifies a LineItem record's class as a Package, Part or Sub-Part using LineItems::ItemClass as well as LineItems::Parent (a package would not have a value in this field).

                   Does this make sense, or am I headed in the wrong direction?

              • 4. Re: Kits, Part and Sub-Parts
                philmodjunk

                     Actually, such a looping script is an option I had in mind for this method. That allows you to select a "package" from the list a script triggered by that selection checks to see if it is a package and then loops to generate individual line item records for each item that makes up the "package". And this can work recursively. A Package can be made up of other packages and yet the script still manages to list all the individual items as it loops through records.

                • 5. Re: Kits, Part and Sub-Parts
                  MichaelVoccola

                       I isolated the problem in a separate file which I have attached for reference. Deleting a Parent deletes all it's members, while deleting a member is independent.

                  https://www.dropbox.com/s/02lj1rci7924066/Add%20Kit%20to%20Invoice%20Isolation.fmp12

                  • 6. Re: Kits, Part and Sub-Parts
                    philmodjunk

                         That would be controlled by the options specified for "delete" in your relationships.

                    • 7. Re: Kits, Part and Sub-Parts
                      MichaelVoccola

                           Phil, I just updated the file with the delete parameters updated, as well as a script step that adds the Parent's LineItemID to the Member's. The delete records when... option is active in a new self-join of LineItems.

                           All seems to work well. I have updated me previous post to reflect this as well.

                      • 8. Re: Kits, Part and Sub-Parts
                        MichaelVoccola

                             New problem with this - how to print an invoice displaying these items on a "List" layout. I haven't been faced with something like this yet, so not sure how to approach it.

                             The layout needs to show 3 'tiers': Kits---<Parts---<SubParts, which should leave me with some combination of a body and two sub-summaries when in list view.

                             The body seems like it wants to be on the 'Kit' Level with Parts and SubParts on two trailing sub-summaries, but there isn't a clear way to list multiple sub-summaries for a single body record. I understand I can add multiple sub-summary parts to the layout, but this needs to be limitless when it comes to the one-to-many relationships I mentioned earlier (on a three-tier maximum).

                             Ist there a workaround for this, or should I just stick to conditional formatting based on the LineItem's role and keep everything in body parts?

                        • 9. Re: Kits, Part and Sub-Parts
                          philmodjunk

                               Hmmm, not what I had in mind. While there is more than one way to do this and what you describe can be made to work, I was picturing a lineitems table that lists both the "kit" and then the parts for it as individual records underneath it. IN that case a list view layout based on LineItems can list all the kits, parts and sub parts quite easily. A value in a field can also be set to "indent" the parts one tab character to the right and sub parts can be indented two, with tab stops set on the layout to get stuff to correctly align.

                               What you describe, with the kits, parts and sub-parts in different tables actually complicates the layout design issues. Off hand, I'd be inclined to set up a list view layout based on Parts with a portal to Sub Parts in the body set to slide up and re-size. But this requires all "non-kit" parts to list themselves as a "kit" with themself as the one and only part or they would be omitted from the resulting report...

                          • 10. Re: Kits, Part and Sub-Parts
                            MichaelVoccola

                                 I was able to create a double-loop script that seems to do the trick. It is a bit long, but there are a lot of other things that happen here too, like creating a PO for items if there isn't one already (for each kit-part), as well as creating buy and sell pricing in a separate table: "LineItemPrices".

                                 Essentially, the script checks if the inventory record has associated parts and completes a set of tasks. If that associated part has associated parts of it's own, it does the same for those parts. Once it hits the end of the line, it goes back to the next associated part for the original item etc...

                                 During this process, the LineItem records created are associated directly with their parents/children. The calc fields within LineItem allows for three formatting options, essentially indentations and then conditional formatting, based on the presence of a value in the fields of LineItem::ParentID and LineItem_Parent::ParentID.

                                 So, if the parent of the current record is the child of another 'parent' record, the current record is considered a Sub-Part. If it isn't it is a Part and if LineItem::ParentID is blank, then the record is considered a 'Kit'.

                                 There is a little for to it than that, but that is the basics. For example, a 'Kit' level item could actually be a 'Part' that isn't a member of a kit, but it has 'Sub-Parts'. This additional formatting and classification is carried into the field LineItem::ItemClass from Inventory::ItemClass during the script.

                                 Here is a .PDF of the script:

                            https://docs.google.com/file/d/0ByvQLu5LpvPrN3Mtb1o2VDF4UDA/edit?usp=sharing

                            • 11. Re: Kits, Part and Sub-Parts
                              philmodjunk

                                   After this thread was started, I had several other discussions touching on this issue: A list of items where each item could represent a group of other items.

                                   I ended up producing the following demo file as an answer to some often asked questions about this type of "recursive relationship structure". It deals with  BOM (Bill of Materials) rather than a sales invoice. but the relational concepts and need for looping, possibly recursive scripts is the same.

                              https://dl.dropboxusercontent.com/u/78737945/RecursiveBOMDemo.fmp12

                              • 12. Re: Kits, Part and Sub-Parts
                                MichaelVoccola

                                     Phil,

                                     For obvious reasons, that is a much clearer example of how it could work and quite a bit different from what I have going on, which is also working for my application, though I suspect the example you just posted is significantly more 'universal' for the average person reading this thread.

                                     I downloaded that demo file and will keep on hand for future reference - thanks!