14 Replies Latest reply on Jan 31, 2017 12:01 AM by syms

    Custom invoice solution with products grouped as kits


      Hello everyone,


      I have been using FileMaker for some time now and even though I somehow manage to get my way around it, there is an issue I cannot solve so far. I'm building a custom complete invoice solution which should allow me to quote more than one product kit at the time. A product kit, as its name implies, is made of different components that can change all the time. Every time you want to quote a product kit to a customer in my solution, you have to select each component from different portals (thus create a product kit) and have them shown grouped as product kits in the invoice print layout. In the case the same customer would request 2 or more product kits, you have to be able to quote a second, a third or a fourth product kit for the same invoice for him or her.


      To illustrate my example, this is what I have done in my solution. I have all the components I use to create a product kit broken down in different tables so let's say here for the sake of simplicity that we have Component A in Table TA, Component B in Table TB, Component C in Table TC, Component D in Table TD, Component E in Table TE and Component F in Table TF. I then use Portal PA, Portal PB, Portal PC, Portal PD, Portal PE and Portal PF to show records from Table TA, Table TB, Table TC, Table TD, Table TE and Table TF on Layout L1 where I can select each one of the components and show them in a Portal P1 as line items for the customer invoice. The problem I'm facing in my solution is how can I for example select Component A, Component B and Component C, have them showing as Product Kit PK1 in the Customer Invoice and then add Component D, Component E and Component F, have them organised as Product Kit PK2 in the same Customer Invoice?



      I hope my example is not too confusing. Thank you for you much anticipated answers.

        • 1. Re: Custom invoice solution with products grouped as kits

          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.

          • 2. Re: Custom invoice solution with products grouped as kits

            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.

            • 3. Re: Custom invoice solution with products grouped as kits

              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.

              • 4. Re: Custom invoice solution with products grouped as kits

                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.

                • 5. Re: Custom invoice solution with products grouped as kits

                  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.

                  • 6. Re: Custom invoice solution with products grouped as kits

                    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.

                    • 7. Re: Custom invoice solution with products grouped as kits

                      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.

                      • 8. Re: Custom invoice solution with products grouped as kits

                        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.

                        • 9. Re: Custom invoice solution with products grouped as kits

                          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?

                          • 10. Re: Custom invoice solution with products grouped as kits

                            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.

                            • 11. Re: Custom invoice solution with products grouped as kits

                              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

                              4 batteries

                              12 clips

                              10' wire


                              Kit A could also have

                              4 cogs

                              7 dohickies



                              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).

                              • 12. Re: Custom invoice solution with products grouped as kits

                                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:


                                Kit A-1:

                                4 batteries

                                12 clips

                                10 wires


                                Kit A-2:

                                4 cogs

                                7 dohickies


                                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.

                                • 13. Re: Custom invoice solution with products grouped as kits

                                  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.

                                  • 14. Re: Custom invoice solution with products grouped as kits

                                    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: