7 Replies Latest reply on Feb 16, 2010 8:23 PM by erob

    Checklist style purchase sheet...?



      Checklist style purchase sheet...?


      Hey guys,


      We have a long list of inventory that could basically be stored in a Product Catalogue style template that FileMaker offers.


      What I want to add to this is the ability to place orders using this inventory list. I'd want to list the inventory and just have a box at the end of each row for the number of the items they'd like to take. This would then be summarized and totaled.


      I'm kinda of a noob at this and am having trouble wrapping my head around the best way to structure this list would be. I'd create a second table and have an order ID for each order...I'd list the items in a list layout...but how will I be able to create a simple number box solution at the end of each row...


      A second step would be to reduce the amount of instock items based on previous orders...


      I'd like to add that we're a production company and the items that are "ordered" are actually returned for the most part so they'd have to be restocked in the database as well...


      If you guys could give me an idea on how to head forward with this, that would be awesome. I kinda have an idea but it would be great to curb the amount of false starts and deadends I could potentially head towards.


      Thanks. Happy valentine's to all you love bugs.


        • 1. Re: Checklist style purchase sheet...?

          So the difficulty I have been facing is displaying ALL the inventory options on the Order Form. Because they are not related to the orders table I can't seem to have them all display...instead I start from the inventory page, and have created a script that creates a join table entry for each item that has been added to the order.


          There has to be an easier way to do this because this solution creates several problems: updating an order become quite a hassle and the script isn't all that pretty either.


          So I'm still wondering what would be the easiest way to allow people to scroll down the entire inventory list and just check off items they need, or fill in quantities that when committed, are subtracted from the inventory and added to the order overview.


          Any and all help would be greatly appreciated. 

          • 2. Re: Checklist style purchase sheet...?

            To show all items in a relationship, use X instead of =.


            You can define a link between your order and inventory tables that uses this cross product or "cartesian join" operator instead of the default =. To change the operator, you double click the line linking the two table occurrences in the relationship graph to bring up a dialog where you can specify the operator and many other options. Since X is an operator that tells filemaker to match all values, as far as I know, you can match up pretty much any pair of fields between the two table occurrences to make this work.


            Depending on what other relationships you've defined, you may have to create a new table occurrence of your Inventory table to do this. To make a new table occurrence, you can select an existing table occurrence with the same data source table (such as Inventory in you database), and click the button with two green plus signs.


            Once you accomplished the above task, put a portal on your layout that points to the appropriate table occurrence.


            Many folks find the terms Table and Table Occurrence confusing. To learn more, click the following link:

            Table vs. Table Occurrence (Tutorial)

            • 3. Re: Checklist style purchase sheet...?

              Hey Phil,


              Thanks so much for the advice, definitely a step in the right direction. I have the portal and it's displaying all the items on the orders page, now I'm just wondering that once I go through the portal and select all the items I'd like to place an  order for, who can I have the portal constrain the inventory list? I could use a portal filter but what I'm really aiming for is an order overview page with a total amount that can be saved for future reference.


              I guess I could use 2 separate layouts to achieve this...but maybe there's a cleaner solution. 

              • 4. Re: Checklist style purchase sheet...?
                   What exactly do you mean by "constrain the inventory list"? Do you want items with inventory zero excluded from the list? Or do you want a list only of the items purchased on that invoice? The first option can be tricky to do because of stored/unstored issues. The second option is pretty easy.
                • 5. Re: Checklist style purchase sheet...?
                     I'd need the second option, a final list of all purchased items...preferably on that can be modified or updated if needs change. THanks so much for your help.
                  • 6. Re: Checklist style purchase sheet...?

                    OK, what you need is an additional table and a 2nd portal to display the list of items purchased for that specific invoice.


                    The typical relationship for an invoicing system looks like this:


                    Invoice --<LineItems>--Products


                    Which are usually defined as

                    Invoice::InvoiceID = LineItems::InvoiceID (Select delete and create options for LineItems in the relationship details)

                    LineItems::ItemID = Products::ItemID


                    We've added a third to list all the products:

                    Invoice::InvoiceID X AllProducts::ItemID  (AllProducts is a 2nd table occurrence of products.)


                    You can place a button in the rows of the AllProducts portal (or make the entire row of fields a button) that runs this script:

                    Set Variable [ $ItemID ; Value: AllProducts::ItemID ]

                    Set variable [$InvoiceID ; Value: Invoices::InvoiceID ]

                    Freeze Window

                    Go To Layout [LineItems (LineItems)]

                    New Record/Request

                    Set field [ LineItems::InvoiceID ; $InvoiceID]

                    Set field [ LineItems::ItemID ; $ItemID]

                    Go To layout [Original Layout]


                    Now, selecting an item in your AllProducts portal adds it to your LineItems portal.


                    Note, there are numerous threads that discuss invoicing. You may want to click the advanced search link at the top of your screen and find a few of these threads to read up more on how to set up an invoicing database system.

                    • 7. Re: Checklist style purchase sheet...?
                         Phil...thank you so very much for your help. It's really opened this whole thing up for me. It's all looking good. Thanks again, hope I can take it from here.