I suggest that the first thing to address is the data structure. I would have one table for components and another for kits (as well as other necessary tables, such as customers). The relationship between KIT and COMPONENT is one to many.
You put kits on the line items of the invoice.
Hello stcav and thank you for your answer. What you say is true and I did think go that way first, but the problem is that a KIT can't have the same configuration because they have different SKUs and can be customized to a customer requirements. For example, a KIT 1 for Customer 1 could be made of Component A, Component B and Component C but another KIT 1 for Customer 2 would be made of only Component A and Component B and another same KIT 1 for Customer 3 would have Component D, Component E and Component H. And the Customer Invoice has to breakdown the KIT 1 into a list of all the components grouped per KIT for each customer.
I'm no FM expert so I can't help with the invoice layout. I do, though, know about data structure.
I'm sure that COMPONENT should be in one table; likewise for KIT. You might have to create a unique kit for each customer (or even for each invoice for each customer) but a kit is still a collection of components.
Someone more knowledgeable might be along to help you.
Whether an item is the kit or the component, it's still a line item in your invoice items table. A component is simply related to the kit with an extra foreign key. This allows you to select any product in your price book and quickly build the invoice. Since all the items (kit and components) are in the same table (invoice items), you can show this easily on the printable invoice wherein it'll be clearly organized. An additional components table would let you store pre-made kits consisting of the primary product and it's component parts. All of this is in our free Kosmas One solution.
All you need is a single portal for invoice line items and then a way to "pick" your products. Whether you use another window or a popover, etc; it's up to you.
If kits don't exist, but are created when adding line items to an invoice, perhaps what you need is the ability to create kits for the invoice and then assign line items to the kits. Then you have each item tagged for grouping when you print your invoice (from the line item table, sorted by kit). Kits are invoice specific and not shared with another invoice. They are just a way to group line items on an invoice printout.
I concur with stcav and Eric Miller - components should be one table instead of multiple tables.
Can you tell us about those components? What unifies "Component As"? For example, are Component As all adhesives, Component Bs are all lumber, Component Cs are all sheet metal, etc.
Not only would I put all components into one table, I would put kits in the same table as well. A self join via a join table let's you build a list of components and even supports building a kit made up of other kits.
A script would check for the presence of components and add them each time you select a kit.
It's been a few years, but a search of this forum should pull up previous discussions of this issue.
Components are wires, clips, clamps, batteries, etc. I thought organizing them in different tables following their category after I could not get them all to show in the invoice printing layout I was using in my previous solution.
Thanks syms. Now, my next question, you wrote,
A product kit, as its name implies, is made of different components that can change all the time.
Are there any similarities at all with kits? In other words, do you have kits that need wires, clips, and batteries, but you just don't know WHICH wire, clip, or battery?
Even though kits are organized in series, there are similarities with them. For example 2 kits from different series can use the same wires, clips, batteries, etc. But at the same time, 2 different kits of different power or size for example can also use different type of components. That all comes down to the customer's custom solution. If 2 different customers happen to have the same requirements, I could then simply use a kit built earlier for another customer. But in the case a new customer has a specific requirement, I can as well build a kit that fits his needs.
Can you give an example? Using "series" means something for your situation, but I don't know what it means. What I'm trying to figure out is if a kit has a set of items it MUST have. There may also be items it COULD have. Items in both categories may also have options. For example
Kit A must have
Kit A could also have
IF that is the case, then Kit A would be a record in your item/product table and would have a bill of materials like so:
Item - Qty - U of M - Required
Batteries - 4 -Each - Yes
Clips - 12 - Each - Yes
Wire - 10 - Feet - Yes
Cogs - 4 - Each - No
Dohickies - 7 - Each - No
At the time you enter Kit A on the Invoice line item there would be a way to make the modifications (e.g. customer wants Cogs but not Dohickies and the 12 clips should be 4 red, 4 purple, and 4 green).
But I'm still not clear this is the case.
One last thing - if this is a point of sale system, then it makes sense for the order to be filled out on the invoice. However, if this is not a point of sale, then I recommend filling out the information on a sales order and follow the process of sales order - pick - pack - ship - invoice. Its more data but it gives you more granularity over the process (for example, if a user orders items but you cannot fulfill them all at one time).
Sorry for not being clear the first time. Kits are organized in Series wherein they have different SKUs. A simple way to view it would be that Kit A belongs to Series A and Kit B belongs to Series B. Within Series A, we will have Kit A-1 and Kit A-2 as different SKUs of Kit A and within Series B we will have Kit B-1 and Kit B-2 as different SKUs of Kit B.
Now even though they might belong to the same Series A, Kit A-1 and Kit A-2 might or might not have the same components. That would depend on a customer's custom solution. Just like you stated, we could have:
The same approach can also apply to Kit A and Kit B. They could or could not have the same components even if they belong to different Series. The important note is that some components can't be applied to a certain Series while others can be used within all of them.
About your last point, the solution I'm building is not a point of sale system. It has to be able to follow an order through the same process you just described, giving the user the ability to not only build Kits from components at the moment an order is received but also to save them for a later use in the case another customer requires the same custom solution another just ordered.
A custom kit, that is, a collection of items that will only ever be sold once to one customer, smacks of marketing more than data design. The usual idea of a kit is that it contains contains all the bits and pieces you need for a common purpose.
What's the difference between a buying custom-kit and buying a few different things at the same time? One is a kit. Otherwise it is the same. However you choose to model it, the kit is going to represent a group. It's going to be a record that links to several other records ( components ) - much like an invoice! And, I suspect, that you'll have to itemise the components in the kit on the invoice, otherwise, who knows what was in Custom Kit #523. So, why not just itemise them on the invoice in the first place.
Follow the advice given by Eric, Barbara and Phil. Put your kits and your components together in one table. You do need a second table, which is kit_components, so that you can keep track of which compenents are in a kit.
Hello everyone and thank you again for your all your answers, they did help me get somewhere in solving my problem. What I did, as most of you suggested, was to have all my components into one table. I then linked that table to a Kit table from where I used conditional value lists to be able to select and add components to a kit record.
To break down a kit into its components on the quote print layout, I created calculations fields listing all the components of a kit as well as their part numbers and respective quantities. Now on the quote setup layout, I have a portal linking to the line items and a popover that i use to show all the kits records to import. So far so good.
The problem I'm facing now is that while adding a kit to the line items portal with a script i have written, FileMaker will spread the lists from the calculation fields through all the fields of my portal instead of just adding them into a respective field. My question is how do I organize the lists i have calculated to fit into a field of my portal.
Below is the script that I use: