6 Replies Latest reply on Jul 14, 2012 7:39 AM by philmodjunk

    Inventory system help using FMP12



      Inventory system help using FMP12


      I am needing a little help tailoring the Inventory starter solution to my company's needs.

      We are a heating and air conditioning filter service company, and Filters are the products I'm trying to keep track of. We use filters of various sizes and quality on each of the Jobs that we perform for our Customers. I've imported our inventory list into FMP and that worked just fine.

      I have set up the following tables:

      • Customers (holds customer info and a portal pulling all jobs for that customer from the related Jobs table
      • Jobs (shows the job location, frequency, etc.)
      • Cut List (Our word for the list of what filters we're using on each job)
      • Inventory
      • Stock Transactions (Already present on the starter solution, but haven't totally figured out its purpose)


      Since we use many various filters per job, I'm trying to figure out the best way to list the inventory that each job takes, as well as the best way to quickly pull the filters used on each job out of the current inventory. It would take a long time to remove them from inventory one filter size at a time. What would be really great is to have a "Remove All From Inventory" button that would somehow compile the job's filters and remove each one and their quantity from inventory. In effect, when I "check-in" a job as complete, I'd like to be able to quickly remove the filters used on that job from inventory.

      I know I've just said the same thing a few different ways, but I'm trying to be very clear about my goal. If I could get some suggestions to point me in the right direction I would very much appreciate it. What I'm not understanding is how to get FMP to group each job's filters used into a total that can then be linked and used in a script or calculation, etc.

      Thanks so much for any and all help. Please ask for clarification of any details you need.

        • 1. Re: Inventory system help using FMP12

          Stock transactions is intended to log any change in your inventory. Receive a new shipment, create a stock transaction record for each filter type received in the shipment and enter the amounts received in the "in" field. Use some filters on a job, and log the filter type and quantity used in a new records in transactions, but enter the quantity used in the out field.

          A calculation field in Inventory then subtracts the Total out from the total in to compute the quantity on hand.

          That's not quite how I would have set this up, but it's close. By logging each change inventory, you have the ability to not only compute the current inventory on hand to to examine how levels change over time. If so see levels drop to zero or close on a frequent basis, that's a good indicator that you need to order more filters and before levels on hand drop as low as you do now.

          Seems like you would need to add a number of additional tables here to get things working for you. I'd be looking at adding a contacts table with the location, billing address and other contact info for each customer. Another table might list each type of AC unit you service that identifies the filter it uses. If some use multiple filters, it may be useful to link to a related table to list them. Another table would link a given contact to this AC record. If your customer owns multiple units of different types, each type would be listed in this table and a quantity field would list how many.

          Then you need invoicing tables where one table lists each service call to a customer and a related table lists each item for which you will charge that customer on that service call, labor, fiters, any other replacement parts would all list in that table.

          A script could then use the contact record for that customer to pull up a list of the numbers and types of AC units and then generate a list of filters from there.

          • 2. Re: Inventory system help using FMP12



            Thanks for the thoughts! I understand how the add/remove inventory transaction works, it's just not quite as efficient or time saving as I would like the system to be for my purpose. I know it would "work" if I just used that system for each size filter used on a job, but that's still a lot of data entry. I would just love a way to configure a button/script to take out all the filters used on a job.

            In my Cut List table, I have created individual fields for each filter size used on the job. There are 15 fields for the item number and 15 for the quantity used. The item number fields are linked to the inventory table and I set up a value list which shows a drop down list of all the filters in inventory. Click on the one used for the job, and then type the quantity in the Qty field. Repeat this for however many filters of each size are used on the job.

            With that in mind, is there a way then to aggregate all those item number and quantity fields into a workable formula to take them all out of inventory at once?

            By the way, I already have a Customer table that has all the billing and location data. And I use Quickbooks as our accounting system, so invoicing is all done through that.


            • 3. Re: Inventory system help using FMP12

              I would just love a way to configure a button/script to take out all the filters used on a job.

              That's why I described adding tables where you can document what equipment is owned by each customer and what filters each requires. A script can then do this for you when you get ready to make a service call.

              In my Cut List table, I have created individual fields...

              Better to set up a set of related records, one for each instead of a set of individual fields. It'll make it much easier to generate a list of all filters needed for a given job and you won't run into limits where a job requires more types of filters than you have fields.

              By the way, I already have a Customer table that has all the billing and location data.

              Good, but you need a way to list all the items, such as filters that will be used on a given service call. You'll need something similar to an invoice produced for each job even if you do not actually use it to bill the customer. The key detail is that it will list all items expected to be used on the service call and then be updated by the actual items used. (Never yet seen a business where you can predict the parts needed for every job in advance with 100% accuracy.) This list, can actually be made from records created in the transactions table.

              What I am imagining would work like this:

              1) Select a customer for a new "serviceCall" record. The system looks up the AC equipment owned by that customer and generates a list of items needed for the service call. This would include a list of filters at minimum, but could list other items if such is useful to you. You'd then review the list and modify it as needed. (Don't have a filter on hand for AC unit #5 so need to make separate call for that one in a few days when it comes in, all others look good so drop out items for AC unit #5 and keep the rest...)

              2) Then the script takes that list and creates the needed transaction records in order to update your inventory. Then, when your techs return, they'd file any updates to that as needed.

              • 4. Re: Inventory system help using FMP12

                Inventory is second only to accounting systems in complexity. Making it elegant and simple is a challenge.

                The White Paper for FMP Novices does not go into enough detail, but the basics are important.

                PDF   http://www.foundationdbs.com/Downloads/WhitePaperForFMPNovices.pdf

                • 5. Re: Inventory system help using FMP12

                  Thanks for that PDF David. I have the FMP Training series book, so hopefully this will be a good addition and fill in some gaps.


                  Another question I have: Is it possible to print the contents of just a field? If I setup a large field on a record and list out several things in it, can I then just print those contents from that field, or view it as a PDF? That would be nice. Let me be clear though, I don't want to set it up as a container and put a PDF in it, because I want to be able to change the contents and information in it, then print it. Is this possible?

                  • 6. Re: Inventory system help using FMP12

                    If you set up a layout with just that field on it, yes. But putting multiple items in the same field is seldom a good approach to use. There are other options that are much more flexible.