4 Replies Latest reply on Aug 7, 2012 11:52 AM by philmodjunk

    Inventory Management



      Inventory Management


      We are using FileMaker Pro 12 for our inventory management at our retail shop. We'd like to take sales reports from our Square account and import them into our inventory database to reflect current quantities. For example, I want the items sold (which is shown on our Square report, which is an excel spreadsheet) to be automatically deducted from the units on hand in our FileMaker inventory. I have set up a table of our inventory, as well as a table for the Stock Transactions (which I have successfully managed to import my Square spreadhseet into). How do I get the two tables to talk with one another? They show that they are connected in the Relationships tab of the Database Management window, but I'm unsure of why it is not reflecting it in my units on hand. Help!

        • 1. Re: Inventory Management

          If Square provides an Excel worksheet detailing the product sold, it can be used to update you Filemaker Pro database.  The "key" to it working is the key field that indentifies the stock transactions to the Square report.  Import the Excel worksheet.  The first time you will need to map the data, that is align the fields in the Stock Transaction table to the columns in the spreadsheet.  Then perform the import.  Filemaker is VERY friendly to importing the information.  Post back if you specific questions, include screen prints of data files.

          • 2. Re: Inventory Management

            I am past the importing Square transactions into my Stock Transactions table (see first image) and now I need the two tables to talk to each other so that when I import items into the Stock Transaction table, they are automatically deducted from my inventory table (see second image)


            Stock Transactions


            Inventory Relationship


            I need to know how to get the Units Out box (from Stock Transactions) to subtract from the Units in Stock (in Inventory) upon importing the data. Is there a simple way to do this that I am overlooking? Should I build another table and have them relate instead of using the one provided for me in the template? I've had to change most of the settings to fit with our business needs anyway. 

            Please advise - thanks!

            • 3. Re: Inventory Management

              Sorry for the extra picture at the bottom - it's just a duplicate of the first image, you can ignore it! :)

              • 4. Re: Inventory Management

                I recognize the starter solution that you are using. What you are asking should be happening automatically.

                Units on Hand

                sums the Units In field and subtracts the sum of the Units out field to compute the units on hand.

                But this requires that you are correctly importing a value into the ITEM ID MATCH FIELD in the transactions field that uniquely identifies each inventory item and correctly matches to a value in the ITEM ID MATCH FIELD in Inventory.