6 Replies Latest reply on May 10, 2011 11:32 AM by alheron

    Grand Total problem

    alheron

      Title

      Grand Total problem

      Post

      OK - first a disclosure - total newbie here Cry

      I'm using FM Pro11, loving every minute of it but run into a problem so here goes...

      I've got a table of products with their cost and retail prices.  I'm building a simple stock ordering solution so for ease of use the products are selected by Category ( perform Find, Restore and select field Category) which works well.  I have a Summary field which gives a total value to the goods ordered so far.  All works well until I go to the next Category, then my Summary field resets to 0 as it only works on the current found set.  What I need is some kind of Order Total which will retain the value of stock ordered to date without resetting every time I create a new found set (by selecting a new Category)

      I would imagine the answer to this is laughably simple, but it has so far eluded me so I'm giving in and asking help from those more experienced please.

      Al

        • 1. Re: Grand Total problem
          philmodjunk

          "resets to zero"? I would think you would get the new total of the new found set, but perhaps you are starting with an empty found set each time you select a different category. How do you tell which records are part of the same order?

          Sounds like you need some more tables here. Purchase orders to order stock can be handled just like Invoices to sell stock, it's just that the money travels in the opposite direction.

          Thus, you need this structure:

          PurchaseOrders---<LineItems>----Products

          Here's a very simple Invoicing demo file created by Comment: 

          http://fmforums.com/forum/showpost.php?post/309136/

          Just think PurchaseOrder when you see Invoice in the tables and layouts of this demo file.

          With this setup, you can either define a calculation field with Sum in Purchase orders like this: Sum ( LineItems::TotalCost ) or a summary field in LineItems that computes the total of TotalCost to compute the total cost of your order. Since you have one record for every Order in PurchaseOrders, you can place either field on a PurchaseOrders layout to see the total for that order. The Sum function will update more smoothly when you first enter items to be purchased into a portal to LineItems on the purchase layout, the summary field works well to compute totals and subtotals in a report based on LineItems if you place that field inside a sub summary part.

          Oh yes, and your Category field can still be used to control a conditional value list for selecting products when you use the portal to add items to your order.

          • 2. Re: Grand Total problem
            alheron

            "resets to zero"? I would think you would get the new total of the new found set, but perhaps you are starting with an empty found set each time you select a different category. How do you tell which records are part of the same order?

            I do get the new total of the new found set each time, before any quantities have been entered into the found set then the total returns zero, but then adds correctly as quantity is selected for each item in the found set.  If I 'revisit' a found set to amend or add quantities this is also correctly recorded.

            In this solution all ordered products are part of the same order so I don't need to identify them on an order by order basis.  The end goal is to move it across to iPhones for the actual order picking process using FMGo, and it is actually working very well apart from this one problem.  I was hoping to get by with a single table solution for simplicity (and inexperience) but I can see the merit in what you suggest.

            I have downloaded the sample Working Example file so I'll tear it apart and try and learn some more!

            Thanks

            Al

            • 3. Re: Grand Total problem
              philmodjunk

              In this solution all ordered products are part of the same order so I don't need to identify them on an order by order basis.

              Yes, but how could this possibly be true when you next create an order? Are you deleting the records each time you finish with them?

              • 4. Re: Grand Total problem
                alheron

                In this solution all ordered products are part of the same order so I don't need to identify them on an order by order basis.

                Yes, but how could this possibly be true when you next create an order? Are you deleting the records each time you finish with them?

                Yes, each time the order is completed the codes and quantities are exported as a .csv for pasting into a suppliers web-site to commit the order.  Any reports can be printed at this time, and the supplier also provides a record of the order.

                Following confirmation of the order all ordered quantities are zeroed out on the iPhone and any new stock or price changes are added.  Each time the ordering process starts there is a fresh copy of the data on the iPhone.  I did say it was simple Wink

                Al

                • 5. Re: Grand Total problem
                  philmodjunk

                  Simple, dangerously so, IMO. I would think you would want to keep a record of your order in your database for future reference--especially to resolve issues that arise when someone makes a mistake with the order.

                  There are ways to do this with two tables ItemsOrdered and Products. You can place your summary field inside a sub summary part added to your database with "when sorted by" your category field.

                  If you keep your records sorted by the category field--which should be fairly automatic once you sort the records the first time, you'll see the sub totals for each category displayed in the sub summary part. (This assumes that you are using FileMaker 10 or newer.)

                  This assumes that selecting a different category keeps all the current records in your found set.

                  • 6. Re: Grand Total problem
                    alheron

                    Oh dear! You're right of course Phil but I was looking for a quick simple solution as the environment doesn't justify a very comprehensive one.  I'm going to rip the demo apart and rework it for my own purposes, and hopefully learn a few things in the process.  I'll work on this over the next couple of days around other commitments and come back either with more questions or a big smile on my face.

                    Thanks for your time so far.

                    AlSmile