3 Replies Latest reply on May 19, 2010 2:46 PM by philmodjunk

    Inventory Database file



      Inventory Database file


      I am using file maker pro 7 on MAC OS 10.6.3


      I have an invoicing database in which I have each record with 

      units, item number and then16 total repetitions per invoice, as well as other irrelevant field.


      I would like to create a script I can run in which when an order is inputted into an invoice, the item number and number of units will subtract from my inventory database.

      The inventory database is arranged so that each record is a different product and has the fields for item number, last aquisition number of units, units sold, and total product.


      I created a script that runs generally:

      if item number = x

      set field inventory:products sold ; units sold + quantity


      And this seems to work, only I can't do this for more than one item number, or even multiple orders of the same item number in a single invoice so this is obviously misguided, not to mention I will have to repeat this script for several hundred Item numbers.


      What other script or similar method can I use to subtract the units for only that specific item number in that invoice from the units available on that specific item number in the inventory file? Thanks.

        • 1. Re: Inventory Database file

          You'll find life is a lot easier if you replace your repeating fields with a portal to a table of related records. That simplifies a lot of issues--including the script you want to run here.


          Here's a link to another person's thread who is asking similar questions:


          • 2. Re: Inventory Database file

            Unfortunately its very difficult for me to rearrange my invoice file as it is an annual file and now has several thousand records already in it. I am more than willing to add some script however, or modify my inventory database (which is a duplicate of my "products" file which saves all of the product numbers and names, just no stock numbers).

            Can anyone think of a solution to this?

            • 3. Re: Inventory Database file

              With a script and some for thought this change may be easier than you imagine. Last year I made exactly this change on a database with over 100,000 invoices. When you import records from one filemaker table to another, filemaker gives you the option to separate repeating field values into separate records. I was able to set up a script that imported the data twice, once into the invoices table and once into the new line items table. I set this up at 5:30 in the evening and let it run all night. In the morning I had my new file ready to test and further update.


              Using your current structure, you can do the following:


              Set variable [$I ; 1]


                 Set varible [$Item ; GetRepitition(YourTable::YourItemRepeatingfield ; $I ) ]

                 Set varible [$Qty ; GetRepitition(YourTable::YourQtyRepeatingfield ; $I ) ]

                 If [Not Isempty ( $Item )

                    Go to Layout [ Inventory ]

                    Enter FInd mode []

                    Set FIeld [Inventory::Item ; $Item ]

                    Perform Find []

                    Set FIeld [Inventory::QtySold  Inventory::QtySold ; $Qty]

                    Go to Layout [Invoice]

                 End If

                 Set Variable [$I ; Value: $I + 1 ]

                 Exit Loop if [$I > 16]

              End Loop


              With related tables and an inventory log table, you can accomplish the same results without any script at all.