Kits, Part and Sub-Parts
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!