3 Replies Latest reply on Jan 10, 2016 8:51 PM by bigtom

    Conditional value list  for inventory purchasing

    HughKendall

      I need a design solution for a database that creates a weekly order summary for a small business.  The ordering is done on one day per week, and there are about 50 items.  I want people to be able to select the items that need to be purchased on an iPad, so they will not have a scroll/mouse function.  So I need to display the items for purchase in a conditional value list.  I am having a conceptual problem with how these lists work ; how table occurrences have a contextual 'view' of the data, and asking my database to look for the right values, from the right viewpoints.

       

      Ideally, I'd like to have items for the weeks purchase order entered through a Type - Item CVL, so minimize input hassles.  I'd like to be able to generate an email to each of the three suppliers, containing the items/serial numbers/order quantity and purchase order date automatically.

       

      My first task is to create the CVL's.  I am really kind of lost on how to design this properly;

        I need to create a layout with the date, and a 2 levels of pop-up boxes (Type/Item) that enters an item into the orders table with todays date - the date and item particulars forming a single record - and then creating my purchase order by sorting all the orders by date, and then by supplier, to create the three purchase order emails

       

      I don't know if this is best done by using a portal, or not, I have seen a tutorial on CVL's that uses a "Line Items" table, and an Invoice table.

       

      Here is what I have in terms of tables and relationships.  I have tried the Conditional value lists as follows;

       

      RElationships.jpg

      My layout is "orders" based on the "Orders" table.  iIt looks like this

      Orders table.jpg

      Orders::CATEGORY is a pop up box, based on a value list "Category VL"  CategoryVL is based on a field, ITEMS::Category (Use all values). 

       

      Orders::ITEMS_FK  is a popup box based on a value list "ITEMS VL" .  ITEMS VL is based on a field, ITMES::ITEM_FK (include only related values starting from Items2)

       

      Any ideas for making the CVLs work?  I think it is in the relationships, and specifically, the table occurrences and the value list definitions as to where the Filemaker 'looks' from.  But i Cant see it.  Also, if someone has a more elegant method of design for my function, so far I'm not too far with this.... Any help appreciated.

        • 1. Re: Conditional value list  for inventory purchasing
          bigtom

          So you have 50 items that come from only 3 vendors and anywhere from 1-50 of the items need a reorder once a week.

           

          I assume that all the items from each vendor should be on one order. I also assume that the reorders are not automatic since there is user interaction. Why not automatic? Does the user need to see all the items of just the ones that are below a stock threshold for the week.

           

          I would do this with a Portal for selection and a Review/Generate Orders button. This button would script through generating the orders. Could be done with PSOS, but will work on iPad. The script can separate the items by vendor ID and go to a layout to create each order individually, make a PDF and automatically email it after review by user. If there is a known threshold for the item reorders this can be automatic and the orders simply reviewed and approved by the user, or not.

          • 2. Re: Conditional value list  for inventory purchasing
            HughKendall

            We do not track usage of items - its a small medical office, and we assume that all items are used.  For my purposes, I'd like to track my total expenses over time by item, and supplier, but thats why no automatic re-order. 

             

            At the end of each week, we go in to the store room, and choose what we need to reorder for the next week.  So the user needs to see all the items.

            • 3. Re: Conditional value list  for inventory purchasing
              bigtom

              I would still use a portal or a list view with a button to  create the orders via script. I think that is the easiest way to get it done.