7 Replies Latest reply on Jan 26, 2010 9:32 AM by Senti

    Need Help With Inventory Count and Management



      Need Help With Inventory Count and Management


      i work for an antiques dealer. three years ago, i set up an Inventory file, a Contacts file and a Line Item file, which are related. we do not create invoices with filemaker (or on a computer at all) -- our actual customer invoices are hand-written. (please don't laugh or ask why!) so we actually enter the information for each item sold into the Line Item file instead of an Invoice file. the Line Item file is named "Invoices" (just to make this more confusing for you) and uses lookups to fill in information from the Inventory file and the Contacts file.


      the Inventory file is our main resource, and is used to track lots of information (how many pieces of an item were purchased ("Amount Purchased"), where and when something was purchased, whether it is at another location, additional costs such as restoration, etc). this file basically tracks information with simple calculations. so far, so good.



      75-80% of our items are one-of-a-kind (one in/one out) but 20-25% are multiples, so there could be 50 of one item under one item ID, which means that theoretically, 50 people could purchase one each of the same item. also, people return things sometimes, and we keep a record of that also. (one in/one out/one back in) sale information (who purchased, invoice number, date purchased, price, discounts, shipping cost, number of pieces sold and/or returned, etc.) is entered in the Line Item/Invoices file.

      i have a portal set up on each Inventory record that shows who purchased an item, when it was purchased, how many were purchased and the invoice number - information from the Line Item/Invoices file. this is just fine.


      BUT i need the Inventory file to keep track of the current inventory of each item and i am having trouble with this.


      in the Line Item/Invoices file, i created a sub-summary part (when sorted by the item ID) to get the total number of that particular item sold (this also takes into account any returns). this summary field is named "Net Quantity Sold" and i put a portal in the Inventory file that shows this number. but i want to deduct "Net Quantity Sold" from the "Inventory:Amount Purchased" number for each item in the Inventory file whenever we sell something. i can't get it to work. i created a new field in the Inventory file called "Current Inventory" i set the calculation for that field:  "Amount Purchased - Invoices::Net Quantity Sold".  even after i sort the Line Item/Invoices file and the sub-summary calculations are correct, the field "Current Inventory" in the Inventory file remains blank. i also tried putting that same calculation field in the Line Items/Invoices file (Inventory::Amount Purchased - Net Quantity Sold)  and if there was only one of an item sold, the calculation worked just fine, but if there were multiples of the item sold and/or returned, the result was really screwy (-312 of one item)


      (note that i also have another sub-summary part in the Line Item/Invoices file that summarizes each invoice. and there is a training grand summary part also)


      what am i doing wrong? any help? i am really a novice, so keep it simple if possible!!!


      also, I am using FM pro Advanced 8.0.


      THANK YOU anyone in advance!




        • 1. Re: Need Help With Inventory Count and Management

          OK, I won't ask, I'll just silently wonder....:smileywink:


          There are several ways to handle this. One way is to change your inventory table into just a Price or MasterList table, one record per item, descriptions and prices recorded here.

          Then do your inventory management from your LineItems (Invoices in your case) table.


          You'd log the acquisition and sale of items in two fields Acquired, Sold for this example.

          Make a calculation field, cBal, defined as Acquired - Sold.

          Define a summary field, sBalance as Total of cBal. Choose the running total option and select the "Restart summary when sorted by" option specifying a field that uniquely identifies each type of item you sell.


          Now you can set up a list view layout that looks a bit like this:



          Enter browse mode and sort your records by the "sorted by" field you specified in the settings for sBalance, then by Date and you should see a checkbook register like listing of all your items being sold and acquired. The last entry for each type of item will show your current inventory in sBalance.

          • 2. Re: Need Help With Inventory Count and Management

            wow that was a fast response, but i don't think we could switch to using the Line Item/Invoice file as our primary file. it took me 2 YEARS (yes, 2 years) to get people here to accept this database as something more functional than word files, handwritten lists and handwritten ledger books (which they still use) are you getting the picture? i have some screen grabs to show you how much information is in the Inventory file - it's a lot. and they finally get it. well, mostly. ok. i'll tell you the reason we hand-write invoices - the owner cannot figure out how to send emails with attachments (or print anything or open any program or shut her computer down or use an ATM) and is indignant at the suggestion that perhaps she may not be able to create an invoice in filemaker, so perhaps she should let the other people here do all the invoicing so it could be computerized. the nerve of suggesting that!boy did i get in trouble. so we hand-write invoices and then type each sale into filemaker AND quickbooks. again - don't ask.


            the Inventory file is set up as one record per item.


            i took some screengrabs. i hope i inserted them correctly.INVENTORY screengrab #1screen grab INVENTORY 2screen grab INVOICE


            i really think we need to be able to see the current inventory in the Inventory file.


            oh please, let this be possible. i cannot go through another two years of convincing.


            THANk YOU!!!!!

            • 3. Re: Need Help With Inventory Count and Management

              Sounds like your boss is a real antique :smileywink:


              The advantage to the inventory log approach is that it can show you how your inventory levels change over time.


              To do it in your current inventory table...


              You appear to have a relationship linking Inventory to your "invoices" table. (Just because you hand enter from paper invoices doesn't mean you can't have a true invoices table with a portal of related line items by the way. It would make data entry from the paper invoices easier.)


              There's a function called sum that can compute the total value of a field in a group of related records.


              Sum(Invoices::quantitySold) would give you the quantity sold for a given item in your inventory table. (Define this in your Inventory table).

              You seem to have one number field in each inventory record to record how many items where purchased.


              Inventory::Amount Purchased - Sum(Invoices::quantitySold)


              Should compute the inventory on hand for each item in your inventory table.


              Note: You may have to change field and table names in these examples to match names defined in your database.

              • 4. Re: Need Help With Inventory Count and Management
                   now you have the picture - thanks so much - i'll give it a try on monday - have a great weekend!!!!!
                • 5. Re: Need Help With Inventory Count and Management

                  sorry to be a pest, but that suggestion isn't working. i created both fields in my Inventory file, but the fields remain blank - there seems to be no calculation going on. i also tried both checking and unchecking "Do not replace existing value for field." is there another relationship i need to define between the Invoices file and the Inventory file?


                  note that in my Invoices file, i do have a Sub-Summary part that totals the Quantity Sold for each item - that works just fine. i also have a portal to that number in my Inventory file. can i use that Sub-Summary number as part of the calculation to determine the amount of inventory on hand? that was my original thought, and what i had been trying to do with no luck.(smae thing, actually - field remains blank) what am i doing wrong?

                  • 6. Re: Need Help With Inventory Count and Management

                    Don't make this a number field with an auto-entered calculation. Make it a calculation field.


                    Enter the  fields name in Manage | Database | Fields.

                    Select "calculation" instead of number in the field type drop down on the right.

                    Click Create.

                    You'll get a specify calculation dialog.

                    Enter the calculation there.

                    • 7. Re: Need Help With Inventory Count and Management
                         YOU ARE MY HERO!!!!! i was SO hoping it was something stupid i was overlooking!) THANK YOU THANK YOU!!!!!!!