3 Replies Latest reply on Mar 22, 2012 10:06 AM by philmodjunk

    Inventory solution - user interface problem.



      Inventory solution - user interface problem.



      I got Filemaker about six months ago, and I'm loving the power it's giving me to look into my business. I run a night club and I have just developed an inventory/order system that allows my staff to take inventory and enter the ordered amount using Filemaker Go. It works fine but the people who need to use it complain that the interface isn't easy to use. 

      The previous solution was a basic excel worksheet that sorted ALL the items alphabetically:

      coca cola 25 50
      fanta 5 25
      sprite 15 12







      I developed a solution in Filemaker using three tables:


      On the order layout line items appears as a portal. This is where the problem arises.

      In the Line Items portal using a drop down value list based on the product details foreign key my crew then needs to choose a product (for instance: coca cola) and then proceed to enter amount in stock and amount ordered. 

      Because we have about 125 different products it gets cumbersome to enter each product on to the Line Items portal. I would need a solution that lists ALL of my products at one given time so that they would only need to enter the amount on stock and the amount ordered. I'm thinking of something like in table view, since my crew have gotten so used to working in alphabetically ordered excel sheet.

      Do I need to abandon working with the Line Items table to avoid using a portal and just use two tables? I'm a bit lost, so all help is welcome.

      Many thanks!

        • 1. Re: Inventory solution - user interface problem.

          You can keep the lineItems table and even the portal. Use a script to automatically load your portal with a list of all items in your inventory--either all records in Product Detail or a subset of them. You can also create a list or table view layout based on LineItems for easier data entry, but keep your relationships intact for reporting purposes.

          Here's a script that creates a new LineItem Record for every record in Product Detail:

          #Create a new Order record
          Freeze Window
          Go to Layout [Order]
          New Record/Request
          Set Variable [$OrderID ; value: Order::OrderID]
          Go to Layout [Product Detail]
          Show All Records
             Set Variable [$ProductID ; Product Detail::ProductID]
             Go To layout [LIneItems]
             New Record/Request
             Set Field [LineItems::OrderID ; $OrderID]
             Set Field [LineItems::ProductID ; $ProductID]
             Go to Layout [Product Detail]
             Go to Record/Request/Page [next ; exit after last]
          End Loop
          Go To layout [original layout]

          That's all you need if you still use the portal. (You can specify a sort order for the portal to get the LineItems to list in order.) If you decide on a layout based on LineItems, perform a find or use Go to Related Records to pull up a sorted list of LineItems records for a given Order.

          After the ordering is completed, you can, at your option, use a script to delete the LineItems that do not have a quantity specified to prune the list down to just those Line Items actually needed for your order.

          • 2. Re: Inventory solution - user interface problem.

            Hi Phil,

            I think you've heard this one before, but you rule.

            Just need to find out how to fine tune it so that it groups soft drinks with soft drinks and alcoholic beverages with alcoholic beverages. For this I have a fourth table called 'product categories'. I'll fiddle around with scripting and see if I can make it work.

            Thanks for your input, it's been a massive help.

            • 3. Re: Inventory solution - user interface problem.

              Just need to find out how to fine tune it so that it groups soft drinks with soft drinks and alcoholic beverages with alcoholic beverages.

              You should be able to manage that by specifying a sort order--either for the portal or by sorting the found set if you use a list view for data entry.

              With a list view, you can even use a sub summary part to insert a category sub heading as well as subtotals calculated from the data entered.