3 Replies Latest reply on Jun 7, 2013 6:38 AM by philmodjunk

    autofilling an order form from an inventory table



      autofilling an order form from an inventory table


           Hi everyone,

           I am at best an amatuer filmaker user, but never the less am responsible for maintaining some databases for my laboratory to track our chemicals and so on. I am trying to set up an ordering and inventory database for the lab. I want it to be fairly basic, just so the lab members can request items from the inventory be ordered and then a list of all the orders and their status be generated. I have made three tables, one for inventory, one for the order form itself, and one which I want to act as a list of all orders that we can use to see which ones have been ordered and received etc.

           What I would like to do is have a button on the order form that we can use to search for items in the inventory and then select the found result and use it to autofill the order form. I just can't seem to figure out how to do this. I must admit that my script writing is limited at best. If anyone has an idea of how to implement this I would be greatful.


        • 1. Re: autofilling an order form from an inventory table

               Is this how you have related your three tables?


               Orders::__pkOrderID = ItemsOrdered::_fkOrderID
               Inventory::__pkInventoryID = ItemsOrdered::_fkInventoryID

               Your table occurrence and field names, of course, may differ from mine.

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

               With that set up, a portal to ItemsOrdered can be placed on the Orders layout and a value list listing InventoryID's and a name or description field can be set up to use to select items to be ordered. More sophisticated approaches may also be used, but that's a basic set up that may at least get you started in the right direction.

               What kind of criteria might your users use to pull up a found set of records in Inventory? A script can certainly be created that loops through the records found to create matching records in ItemsOrdered that also get the OrderID of the current Order record.

               Though the names are different, this is the same basic set up you'll find in the invoices starter solution that came with Filemaker 11 or a similar one that comes with Filemaker 12. You may find it helpful to look at the layouts/scripts/value lists used in those starter solutions.

          • 2. Re: autofilling an order form from an inventory table

                 Thanks PhilModJunk. I had set up the realtionships with more like this

                 Items Ordered---<Orders>-----Inventory

                 I had hoped to have it so the users search for the name of the chemical, for instance a search for glucose would bring up the glucose entry in teh inventroty and they could then simply selct this to fill out a new order request form.

                 I will have a go at the solution you suggest and let you know how I go.

                 Thanks for the help.

            • 3. Re: autofilling an order form from an inventory table

                   You do need to swap around the Items Ordered and Orders tables, but a name based search is indeed possible. Not only can a value list that enters the Inventory ID also display the name of the item so that you can tell that you are selecting 'glucose', but once you have this working, you can adapt the method in the following demo file to get a name based look up system that enters the inventory ID and be able to auto-complete on the value list.

                   I do, however, observe that your example would appear (to me anyway), to be different from what you specified in your original post. In that, you describe pulling up a found set of records and ordering all of the items in that found set, but your example of what you might want to find in the inventory table sounds much more likely to be a case of finding and adding a single item to the order with each search of your inventory. If you need to find and select individual items, the drop down list methods should work quite well. But if you also want to be able to find an entire group of inventory items and add all of them to your order, that can also be done without changing the basic approach that I have outlined here.

                   The demo file:

                   FileMaker 12 users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
                   Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7