7 Replies Latest reply on Nov 2, 2011 2:59 PM by philmodjunk

    Creating product packages, and inserting them into an invoice

    dmpjporter

      Title

      Creating product packages, and inserting them into an invoice

      Post

      I currently have an invoice system that allows me to select a product, and it is added into the line items. I am now trying to figure out how to make a package of products, (several exisitng products, put into a package) and have that so when i click on the package, it will fill in each line item with items from the package. ie. One package will contain 1 silver widget and 1 gold widget, once clicked, it will add those to their own lines on the line items.

       

      Is this possible?

       

      Thanks.

      Dave

        • 1. Re: Creating product packages, and inserting them into an invoice
          philmodjunk

          It can be done, but as far as I know, you have to write a script that checks for whether the added line item is a "package" or "kit" and if so, adds the addtional items to your line items portal. You'd then have to design your layout to make sure this script is performed each time a line item is added to your invoice--script triggers are one way to do this.

          • 2. Re: Creating product packages, and inserting them into an invoice
            dmpjporter

            I've been pondering this for a while now and I just can't seem to wrap my brain around it. In theory, I can think of how this would be done but I would need to use an array, which Filemaker just doesn't do. The issue is, I want a use to be able to create a package. If I created the packages, I would be able to script something that would insert each record, but I cannot think of a dynamic way of doing it.

             

            However, I have brainstormed an idea of somehow creating a set of line items for the packages. (ie. create a set of line items that include each product in a package) and then be able to somehow add those items into a new record. Is there a way to merge or copy records like this?

             

            I know what I am thinking, but I am not always able to express it. I appreciate the help, and would be glad to try and clarify more if needed.

             

            Thanks.

            Dave

            • 3. Re: Creating product packages, and inserting them into an invoice
              philmodjunk

              Usually, an Invoicing system consists of three tables Invoices, LineItems, Products.

               

              A portal to LineItems is usually placed on an Invoices layout to record the sale of each type of item purchased. Item names, pricing etc is usually looked up from the Products table as an item number is entered/selected in this portal.

               

              For kits you add another field to Products that's empty unless it is a package (also called a "kit") item. Then you define an extra table that you use to list all your kit items.

               

              Your Packages Table would just have three fields: Qty, PackageID and ItemID.  (both PackageID and ItemID are ID numbers from the ItemID in Products)

               

              In your Products table, you'd have a specific record for each package with the components listed in the Packages Table.

               

              Example, Item 2345 is called Picnic for two Kit.

               

              In packages, you'd record:

              Qty  PackageID          ItemID

               2     2345                    8921  (8921 in Products is the number for a dinner plate)

              1      2345                    8922  ( ID for a bottle of wine)

              2      2345                    8923  (ID for a wine goblet)

               

               

              When your script identifies a newly added item as a Package item, it uses a find or Go To Related Records to pull up the set of itemIDs for that package and loops through them using Set Field and/or Set variable/set field to copy the ID numbers and quantities into new line item records on your invoice.

               

               

              • 4. Re: Creating product packages, and inserting them into an invoice
                gizmocreative

                this is exactly what i am looking for, although i think i need a bit more instruction on how to achieve this.

                i have the 'invoice-<line item>-product relationship' happening.

                from what i have read in this thread, i need to make a 'package' item in the product table and add an empty field that only gets populated for packages only.

                next make a package table with the three fields. 

                my question is how does the relationship of this new table work? is it related somehow or is it devoid of relationship and just used to reference package items?

                i need to work this one out as this article is exactly what i am looking for. thanks!

                • 5. Re: Creating product packages, and inserting them into an invoice
                  ericjlindholm

                  This is exactly what I need.  I am trying to get this script right it damaged my database the way i wrote it.  what am i missing?

                  • 6. Re: Creating product packages, and inserting them into an invoice
                    ericjlindholm

                    I also want to make sure i have my relationship right

                    • 7. Re: Creating product packages, and inserting them into an invoice
                      philmodjunk

                      Threads this old (3 months or more) no longer appear in recent items so they are easy to miss. You can always open a new thread and include a link to an older one if appropriate so that your question is easier to see by other interested parties.

                      No script should ever damage your file. More likely, your file was already damaged and the script encountered that damage in a way that made the previously hidden damage apparent to you by locking up or generating a message that the file is damaged.

                      In this case, you have an infinite loop, as the Exit loop step checks to see if $INVID is empty, but no step inside the loop modifies this field so the loop either never executes or executes infinitely.

                      I'd think your script should work like this:

                      #Script starts from layout with portal to line items and focus is in a row of the line items portal
                      If [not isEmpty ( Inventory;:Package ) ]
                         Set Variable[$EventID ; value: LineItems::EventID]
                         Set Variable [$PackageQty ; value: LIneItems::Quantity]
                         Freeze Window
                         Go To Related Record [Show only related records; From table: Inventory Package; Using layout: "Inventory Package" (Inventory Package)]
                         Loop
                            Set Variable [$InvID ; value: Inventory Package::INV ID]
                            Set Variable [$Qty ; Value: Inventory Package::Qty]
                            Go To layout [LineItems]
                            New Record/Request
                            Set Field [LineItems::EventID ; $EventID]
                            Set Field [LineItems::Inventory ID ; $InvID]
                            Set Field [LineItems::Quantity ; $Qty * $PackageQty ]
                            Go To layout [Inventory Package]
                            Go To Record/request/page [Next ; exit after last]
                        End Loop
                        Go To layout [original layout]
                      End IF