4 Replies Latest reply on Jun 26, 2013 1:02 PM by SteveMartino

    Inventory Report/Order

    SteveMartino

      Title

      Inventory Report/Order

      Post

           Hello Forum,

           I was trying to figure out how to make a report or layout that I can use to reorder inventory.  I would like to either make this off an existing report, adding calculations based on a formula, then subtracting that from the amount in stock (a number I manually enter), or off of a layout.

           Some additional info, the inventory is not tied to sales, but to the equipment I need to replace on an annual service (nozzles, filters, pump strainers, pump screens. All this information is in the database and shows in a report (see screenshot).  The top of the screen shot shows the report layout, the middle is an actual report, and the bottom is exactly what I would like it to do (in an Excel spreadsheet.

           Right now I Export the report to a one Excel, the copy/paste columns C & D to the Excel sheet shown, Enter the amount of stock on hand in column E, then let the calculations in F & G create the order amounts.  Then repeat for the other inventory items.

           Things I've tried (unsuccessfully):

           1.  Creating additional fields on the report to try the calculations like the spreadsheet, no luck.

           2.  Go to the layout the information in on, and tried adding some additional fields and looping script. (yikes).

           3.  Combinations of 1 & 2.

           I'm sure this is possible and I was hoping someone can point me in the right direction.

           Thanks

           Steve

           ps. sorry for the long post.

      ss33.png

        • 1. Re: Inventory Report/Order
          philmodjunk

               We'd need to know a lot more about your database design before se can suggest an answer.

               In the list view layout, each row is a different record or a group of records?

               And the numbers listed are the number of items consumed?

               But where do you store the data shown in the other spreadsheet column--the amount on hand?

          • 2. Re: Inventory Report/Order
            SteveMartino

                 Hey Phil, thanks for taking the time to respond.

                 1.  In the DB report, each row is a summary of a group of records, i.e "1.20 x 60A--1" ,shows a total of 1 nozzle that is a 1.20 x 60A.

                 2.  Not really, those numbers are based on customers equipment.  In other words these are the totals of each (in this case) nozzles that are on all customers equipment.  Come September Ist will begin to replace all the nozzles throughout the next few months (and other maintenance items).  It's just easier to put together all the items I need and order them in bulk in August.

                 3.  The amount on hand is not in the Database yet.  I would think I would need to put this on the database, unstored and maybe add it to the Report, then use a calculation field to get to my number?  Something I could not figure out.  Basically, in the near future, I will just go to my shop, and truck, add up whats on hand, and enter it into this field.

                 Also, I don't use the database for billing.  I use Quickbooks.  Detailed invoices for service are done in the field, and a overview is entered into QB.

                 If it's too complicated (for me, or for you to explain it to me) I wouldnt want to trouble you about it.  Manually (after I enter enter "in stock")it only takes about a minute to copy/paste to Excel, and I only need to do it once a year.  Like I said before, this is a learn one, create one, use one database for a single user (me), for my business.  I just love FM and what it does.....I just sit around in my free time thinking........"what else can I get it to do".  (Addicted?)

                 Thanks Phil for all your help guidance and support.

                 Regards,

                 Steve

            • 3. Re: Inventory Report/Order
              philmodjunk

                   2) then they ARE the numbers consumed as far as this report is concerned. The fact that they will be gradually replaced over time isn't relevant to the numbers shown in your report as far as I can tell.

                   3) you'll need to put them somewhere in order for you to get this system to work all in FileMaker and without having to go through the manual steps that you are now with your spread sheet.

                   This is a basic "Inventory Control" type database issue and their are two basic approaches. One uses a "transaction log" to compute current inventory levels and to trigger needed restocking orders. The other uses a number field in a Products table (one record for each part listed in your sample report) for the amount on hand with scripts that update the values shown each time a shipment is received or product is removed from inventory.

                   from what you describe here, it sounds like the transaction log method would work quite well. See this thread for a detailed description of the method: Managing Inventory using a Transactions Ledger

                   Keep in mind that you don't actually need to call this a "sales invoice", it can simply be your "work order" where you document the parts needed for a given service call.

              • 4. Re: Inventory Report/Order
                SteveMartino

                     Thank you (again and again) Phil, for your detail reply and help.  I checked out your thread, and will spend some time with it in the next week.

                     Regards

                     Steve