5 Replies Latest reply on Sep 11, 2013 8:48 AM by philmodjunk

    Creating Invetory Count and Records



      Creating Invetory Count and Records


           I am finalizing a database for a customer who has thrown me another request. Basically what the customer has asked for is a simple invoice database to track information about their purchases and sales, and it will be used on their Ipad. I have done this before without much of a problem, however the customer now is asking for an accurate inventory count to be kept on the Ipad and I'm not sure the best way to go about it. Hopefully someone can give me some direction.

           Currently I very few tables in my database, with an 'Invoice Status' field in my invoice table/data entry layout having a drop-down selection of 'Payment' or 'Sale'. . My questions are as follows:

           1) I have looked at the Stater Solution:Inventory and it seems somewhat simple. The portal on the inventory data entry screen has the options of 'units in'/'units out' from the Stock Transactions Table. The 'Inventory Table' has a count on it that basically calculates the product ID's units in minus units out.

           My layout can't really have units in and units out because of the way the customer wishes the screen to mimic their old paper forms, so I am wondering: Should I create a separate layout for Payment Invoices and Sale Invoices (and create a stock transactions table)? This would allow me to set the status type for each form and separate the units in and units out field.

           2) Someone else suggested keeping everything the same but writing a script that triggers on record exit that checks the invoice status, adding or deducting from the inventory total in that way.

           If you have any opinions please respond I'm just wanting to do this the right way...

        • 1. Re: Creating Invetory Count and Records

               1) you don't have to have units in and units out on your sales layout, just units out--which can be the quantity field in your LineItems table.

               2) Not my recommended way to do this. A failure here can leave your counts either too high by not taking an item out of inventory or too low by taking it out twice.

               See this thread for how to set up your LineItems (InvoiceData) table so that it also functions like Transactions in the Inventory solution: Managing Inventory using a Transactions Ledger

          • 2. Re: Creating Invetory Count and Records

                 Thanks Phil, that thread sheds some light on inventory in FMP. I know it wouldn't be necessary to have a 'units in' field on a Sales Layout, it's just that currently I have one layout for both Sales and Purchases, with a drop-down menu for either one (with conditional formatting to change the Labels/Colors on the form based upon the selection). My issue is that I'm not sure if I want to create a separate layout after getting everything else working great before the customer asked about adding inventory functionality.

                 Something else I'm curious about then is this: Is there any way to change the field on the 'Invoice Details' type layout to be either 'units in' or 'units out' based upon the 'Invoice Status' field (being Sale or Purchase) ? That would solve the issue of writing a significant amount of new scripts and creating new layouts.

            • 3. Re: Creating Invetory Count and Records

                   You can't change what field is getting the quantity, but you could use one number field to log both in and out values. the In and out fields then can be replaced by a calculation field that multiplies all "out" values by negative one, for example.

                   Another option is to duplicate your layout and customize one for sales and another for purchases.

              • 4. Re: Creating Invetory Count and Records

                     Can you expand on that first part a little more? I think I see what you're saying but I'm not sure how to implement that.

                     The biggest reason alone I don't want to create a new layout and have it customized to sales or purchases is that the customer previously used the same form for purchases and sales (with the header/title being the only difference).

                     My DB started out (and is still) similar to the invoice starter solution, and I've started creating multiple layouts on a different save file just to see what issues it might cause. I'm having trouble converting and rewriting some of the scripts to create a new invoice using the sale layout or the purchase layout, it just adds a new invoice line item instead. I wanted the customer details layout to have a button for each type of layout, 'purchase or sales'.


                • 5. Re: Creating Invetory Count and Records

                       In my original example, there's a field called cBal defined as QtyIn - QtyOut and which is then summarized by the summary field.

                       But it could also be defined as:

                       If ( Invoices::Status = "Sales" ; -1 ; 1 ) * Qty