9 Replies Latest reply on Feb 3, 2012 6:22 AM by philmodjunk

    Inventory Database

    JMoe

      Title

      Inventory Database & Sales Records Help Needed

      Post

      Hello-

      I'm new to FM Pro and am attempting to build myself an inventory system for my online resale business.

      I've downloaded FM Pro and have read the entire FM Pro Tutorial, so I have a basic understanding of how the program works.  
      I have managed to create a basic inventroy database for myself, and it is working great so far.  I can enter items to my inventory as they are puchased with description, etc.  

      Where I am stuck is that I want to be able to "mark" these items as sold in my inventory database, which would somehow activate fields where I could enter the sales information (price, date, etc) for that particular inventory item, and subsequently remove that item from my active inventory and put it in a "sales record" list of some sort.  

      I'm just wondering if this is at all possible, and if so could someone help me with the steps involved in doing this?  

      Any help would be greatly appreciated.  

      Thanks !

        • 1. Re: Inventory Database & Sales Records Help Needed
          philmodjunk

          It's definitely possible.

          If you use your DB for invoicing your sales, filling in the data on your sales invoice can enter the data you specify here for the item sold.

          You might examine the invoices starter solution or this simpler demo file (both use the same basic set of tables and relationships) for some ideas: http://fmforums.com/forum/showpost.php?post/309136/

          • 2. Re: Inventory Database & Sales Records Help Needed
            JMoe

            I don't use my database for doing invoicing, as most of my sales are done on eBay and they have their own built in invoicing system.

            I just need to keep track of my inventory and sales records for tax purposes, and would like to do so in the manner I described above.  

            I checked out the "inventory" starter solution and found that it was a bit more than I needed.  I will look at the "invoices" one and see if that would work.  

            Thanks

             

            • 3. Re: Inventory Database & Sales Records Help Needed
              philmodjunk

              You may not need to print invoices, but you do need to log sales information. The invoicing starter solution and the much simpler demo are one way to do that.

              However, it sounds like you can probably simplify this as you don't really need to see a list of different items for each purchase, just a list of items and whether each individual item is sold or still "in inventory".

              How do you track each item in your inventory now? Do you use bar codes, a serial number or ???

              Basically, what you describe, if items are sold invidually, is just the need to add a status field where you can format the field with a check box so that clicking it marks it as "sold" and then you'd add more fields to the same table to record the rest of the sales information. Don't see why you would need to "activate" a set of fields here, just go to a layout listing your items, find the one sold, click a check box and fill in the sales fields.

              Where this could change is if one sales transaction on Ebay sells a group of items to the customer. Then an invoicing system makes sense as it will let you enter the sales information once for the entire group instead of entering it over and over for each item sold.

              So it depends on your business. Do you sell groups of items in a single transaction or does each transaction sell a single item?

              • 4. Re: Inventory Database & Sales Records Help Needed
                JMoe

                Hello-

                I checked out both the Invoices starter solution and the demo file.  The Starter Solution looks to be way more that I need.  I would probably end up deleting many things in that solution, and worry that I would accidentally delete something that was needed to make the rest of the database function properly.  Also, the demo file seems to be "not enough" for what I need.

                I previously just tracked inventory in an Excel data sheet, but my business has grown to the point where I need to have an ID # for each item.  As I stated earlier, I have managed to create a basic inventory input database with FMP, and am using the serial number function to give each entry a unique ID.  

                Regarding activating a set of fields once an item is marked sold, I thought it would be a good idea so that information wouldn't accidently be placed in any of the "sold item detail" fields unless at item was in fact marked as sold.  I guess its not actually necessary, I was just wondering if it was even possible to do in FMP.  I would also say that 99% of my eBay sales transactions involve a single item, or soon to be "single inventory item" so that is why I don't think an invoicing system is necessary.  The only thing I am really looking for is how to add sales information to an item in active inventory once it is sold, and then have the sold item be removed from the active inventory list and be placed on some sort of "sales records" list.  

                • 5. Re: Inventory Database & Sales Records Help Needed
                  philmodjunk

                  Neither the demo file nor the starter solution are intended to be exactly what you need, just example files you can tear apart and look at to find ideas and concepts to incorporate into your own solution.

                  And that's why I asked that last question. if items are almost always sold as individual sales transactions, there's no need in your case for an invoice table.

                  You can just add the addition fields you need to your inventory table to record sales status and other sales information. A status field can mark it as sold. You can then set validation rules on any sales data fields so that the input is rejected and the user gets an error message if they enter data in them and the status field is not set to record the item as "sold".

                  One frequently used trick for such a status field is to define it as a number field. Then format it with a checkbox group that has the number 1 as its only value. Resize the field so that the check box is visible, but the number 1 is not. You can then click this check box field to mark an item as sold. The mouse click enters a 1 into the field so you can count or sum on this field to get a count of the number of items sold.

                  • 6. Re: Inventory Database & Sales Records Help Needed
                    JMoe

                    I looked at the inventory starter solution again and decided to try and customize it to suit me.  I got pretty far on it in fact, and everything seems to be working.

                    There are two check box fields in the starter that use the method that you described with assigning the checkbox a value of one.  I would like to make one of these checkboxes my "sold" validation field so that I can "unlock" my sold item details fields as I had described.  The only problem is, I have no idea how to do this.  I'm pretty sure I make to make a script, but have no idea what commands, etc. I need to use in order to make it function.  Any help would be greatly appreciated.  Thanks! 

                    • 7. Re: Inventory Database & Sales Records Help Needed
                      philmodjunk

                      You may not need that script, but using one can make the interface a bit more friendly.

                      My original suggestion here was to use a validation rule as it does not require scripting and will prevent storing data in any sales data field on all layouts in your system. It has one drawback, that it allows the user to enter data in to the validated field, then pops up an error message rejecting the data when the user exits the field.

                      Let's say your "sold" field is a number field with that name and that a 1 in it means it is sold and if it is empty, it has not been sold.

                      Open manage | database | fields and find one of your sales fields.

                      Double Click it to bring up field options

                      Click the validation tab in field options

                      Select the calculation option

                      Enter the name of your "sold" field as the sole term in the calculation:

                      Sold

                      Then enter a custom validation error message in the box provided. Something like: "You cannot enter a sales price if the item has not been sold."

                      Repeat this process for each field you want to protect in this fashion.

                      If you want to keep the user from entering/selecting data in the field if it is an unsold item rather than popping up an error message after they do it, you can use the OnObjectEnter trigger to perform a script that checks the sold status of the current record and denies entry in to the field if it is not sold:

                      If [Not YourTable::Sold ]
                         Go to Next Field
                      End IF

                      You can use Go to next field to put the cursor in the next field in your tab order. You can also use Go to Field or go to object to put the cursor somewhere other than the current field.

                      Note that you can put both of these methods in place for the same database if you want.

                      • 8. Re: Inventory Database & Sales Records Help Needed
                        JMoe

                        Hello-

                         

                        FIrst of all, I want to thank you for all the help you've proveide me with my database thus far.  I've taken your suggestions, and have everything working the way I want it to.  I'm very impressed with the capabilities of Filemaker!

                        I have one other question that I need help with.  For my inventory database, I have also created a "price tag" label layout so that I can print physical price tags with item info, etc. on them.  The problem I am running inot is how to be able to select the records I want to print, as opposed to either printing ALL records or just one, which are the only two options I see.  SO, say I want to print records with inventory #'s 1000 to 1015, and then 1030 to 1040, and 1042 and 1045.

                        Is there a way to do this?  Thanks in advance...

                         

                        • 9. Re: Inventory Database & Sales Records Help Needed
                          philmodjunk

                          The standard method for doing this is to perform a find to find the records for which you want to print tags, then print with the records being browsed option selected.

                          If the inventory#'s are number fields or text fields with a consistent number of characters to each value, you can enter find mode and specify:

                          1000...1015

                          In the inventory number field, then perfomr the find.

                          You can do this by hand or with a script where you specify the range of numbers in a field with gloal storage specified then perform a script via trigger on that field or by clicking a button and the script then takes the data from the global field or fields and then sets up and performs the find for you.