6 Replies Latest reply on Oct 8, 2016 11:12 AM by user28222

    Unique FM design challenge, or easy solve?  Guru guidance sought!

    user28222

      Hi All,

       

      I am designing a order entry/purchase order system in FM15 with some interesting design requirements in the UI.  I hope it would be something that is a stimulating challenge for the experts to entice them to help me, or even better, the solution is so obvious, that even a caveman could do it.

       

      The client is a trucking company delivering produce to groceries and supermarket chains.

       

      The workflow for typical day is:

       

      • During daytime, orders from 'customers' are entered and processed  one by one.  Each order would of course have many order items. Very straightforward order entry system from a FM design standpoint.
      • During evening time, purchase orders are then generated in bulk (based on the aggregated sum of the individual items ordered for the day)  in the following manner:
        • If store A, store B, store C, each ordered a box of strawberries, the screen would show 3 boxes of strawberries outstanding.  The user could then designate which wholesaler to order all 3 boxes of strawberries from.  This single step should be able  include other items(eg blueberries ordered for the day.)  This process is repeated until all outstanding items have been assigned a wholesaler/supplier (order quantity has been reconciled with purchase order.)
        • The key feature is being able to assign in a single click the wholesaler to handle a whole bunch of aggregated quantities.  This would also lead to automatic generation of the purchase order records in the database.
        • In the event that the total quantity for a particular item is very large, there should be a way to break down the single quantity into two wholesalers.  So if 100 boxes of apples were ordered total, ideally I should be able to do the following two steps: assign 50(out of 100)boxes to wholesaler A, then assign the remaining 50 boxes to wholesaler B.  The system is doing auto-reconciling in the background so that use doesn't make any errors.
        • As the purchase orders are generated in an iterative manner described in b), the system would hide the items that have been assigned a wholesaler(supplier) and only show on the screen the outstanding items.

       

      The above specs led me consider the following solution:

       

      Tables: standard tables and relationships: Customers, CustomerOrders, OrderLines, Products, Suppliers(Wholesaler), PurchaseOrders, PurchaseOrderLines.

      Virtual Lists: One would be generated with ExecSQL to aggregate the outstanding orders for the day and used for the layout for the the purchase order assignment screen.

      Scripts:  Each time items are assigned to a wholesaler, the script would make sure that the Virtual List is re-calculated so only unassigned items would show on the screen, as well as the creation of new Purchase Order recrods in the backend.

       

      Let me know if you have any thoughts or comments.

       

      Thank you in advance!

       

      Best,

      Wonmoh Lee

        • 1. Re: Unique FM design challenge, or easy solve?  Guru guidance sought!
          mz5005

          I think you need to think out the functional behavior of the system first more in detail. I know a little about ERP systems and in general, you always see you can not have a system force a company how to work - you always need optimal flexibility because "special situations" always happen.

           

          So the system should not "decide anything (split 100 in 2 orders of 50), but at whatever step it makes, it should have an option for change. If not, for sure you get problems like "we have only one supplier for product X"... "yes this is a large order but we still want to place it with only one supplier" etc

           

          An important aspect of people accepting your system is that they feel in control, not being controlled.

          • 2. Re: Unique FM design challenge, or easy solve?  Guru guidance sought!
            electon

            It depends on many factors: single vs multi user, the level of automation you want to implement, etc.

            IMO counting backorder items should be based on your current inventory, tracking that is a task on itself.

             

            In an ideal world there are no screw ups but:

            You'll need to handle not only purchase orders.

             

            Receives ( suppliers may not actually deliver all goods )

            Returns ( suppliers may ship bad goods or they can be damaged in transport )

            Vendors may have preferred Suppliers and not want anything else ( especially foods that are produced by suppliers ).

            Different Vendors may have different lead times.

            Sales Orders may be cancelled, but you already purchased goods.

            Sales Orders may have partial shipments if you're not able to fulfill the order at once.

            Inventory may be damaged at the company itself.

             

            All sorts of stuff to think about and come up with business rules.

            It's often good to research and sit down with pen and paper to work out what the solution will need to do.

            • 3. Re: Unique FM design challenge, or easy solve?  Guru guidance sought!
              user28222

              Hi thanks for your valuable input on the need for 'flexibility' aka give what the client wants.

               

              If one were to provide the solution in FM(as opposed to more industrial strength ERP packages) can one do it in the way I described?  What are your thoughts on my approach of using virtual list+ExecSQL+Scripting to deisgn that purchase order screen?

               

              I ask because the client is not a large company per se and my gut feeling is that it can be implemented in FM.

               

              Thanks!

              • 4. Re: Unique FM design challenge, or easy solve?  Guru guidance sought!
                user28222

                Hi Electon,

                 

                I appreciate your help with this!

                 

                I agree that inventory management is a whole another important function, but suppose that the inventory management funcitons are not that important for this particular client... would you advise that the 'batch purchase order assignment' function is feasible in the way I proposed it?

                 

                Let me know if my virtual list + execSQL + scripting solution is a 'good enough' approach, or if you know of other approaches that would be great too!

                 

                Cheers

                • 5. Re: Unique FM design challenge, or easy solve?  Guru guidance sought!
                  electon

                  user28222 wrote:

                   

                  I agree that inventory management is a whole another important function, but suppose that the inventory management funcitons are not that important for this particular client... would you advise that the 'batch purchase order assignment' function is feasible in the way I proposed it?

                   

                  I'm not sure how to answer this one.

                  Is it possible without inventory management? yes, I guess it is.

                  Is it feasible? it should be.

                   

                  The question is too broad to answer in detail. Virtual lists, ExecuteSQL and scripts are just tools in your toolbox.

                  There are many ways to get things done in FileMaker and some prefer one over the other, it will also depend on the skill level.

                   

                  I think it comes down to what you agree on the solution will and will not do and build it down to that spec.

                  You can make it as simple or as complicated as it needs be.

                   

                  It definitely can be done in FileMaker.

                  • 6. Re: Unique FM design challenge, or easy solve?  Guru guidance sought!
                    user28222

                    So what I'm hearing is not only is it definitely doable, it can be solved with multiple approaches!

                     

                    I guess what I was wondering was is there an easier or more efficient way to make that screen.

                     

                    My approach with a little more details:

                     

                    Sales Order (Separately Entered)

                    StoreA  1 box of strawberries

                    StoreB  1 box of strawberries

                    StoreC  1 box of strawberries

                     

                    Purchase Order (Bulk order with the option to split by user)

                    A Virtual Table would be used for the following layout:

                             Item                    Qty                     Wholesaler(assigned via drop-down list)

                          Strawberries        3 boxes                   WholesalerZ

                          Blueberries          4 boxes                   WholesalerY

                    The ExecSQL used to generate the first two columns above would be something along the lines of:

                         SELECT item, SUM(order_qty)

                         FROM order_lines

                         GROUP BY item

                     

                    My virtual+list and execSQL approach (for aggregating the individual order quantities) was selected because the information on the layout would have to used again to subsequently generate the purchase order records into the database.

                     

                    Thanks!