4 Replies Latest reply on Sep 21, 2012 4:25 PM by philmodjunk

    Database Script Help



      Database Script Help



           Im trying to make a databse that has one small purpose. To count UPC Codes. This is for my store inventory. Im using Filemaker Pro 11, and a Bad Code Scanner on my Notebook.

           What I want is to be able to just  scan the bar code and move on and at the end, it has a count of every quantity of Barcodes I have scanned. So I can export them to Excel and import them Back into my POS System. But I cant get it to do that. So Logically it would need to see if the barcode exists, if it does it counts +1, and if it doesnt then it needs to create a record, and count +1. I cant seem to get it to work.


           Any Suggestions?



        • 1. Re: Database Script Help

               It might be simpler just to set up a table where a new record is created in it every time you scan a barcode. You can then sort your records by barcode field to group them and use the grouped data option to export your data to excel using a 'count of' summary field to count the number of records with each UPC code.

               Another option is to use a relationship with auto-create that works like this:

               MainTable::ScanField = ExportTable::UPCCode

               enable auto-creation for ExportTable in this relationship and then your script can be:

               Set Field [ExportTable::Count ; ExportTable::Count + 1]

               If a scanned UPC code does not exist in ExportTable the set field step will create that record and enter a 1. If it exists, it will increment the count feld.

          • 2. Re: Database Script Help

                 OK so I made a NewDbase. I made 2 Tables one MainTable with a Single Field call "ScanField". Then a Second Table ExportTable with a Singular field name "UPCCode. I related them with Auto Creation turned on for Auto Creation. But im not sure where to put the script, or how it auto runs? Im kind of new at this level of scripting and the multi table thing. So can you be more specific in your post..






            • 3. Re: Database Script Help
               Also I dont thing its totally what I meant.. This will just Count UPCS into one Number.. Im looking for Each UPC to have its own Count.. So if I have 100 Items,I will have 100 UPC Codes Each with an Inventory Count. UPC 1 QTY 4 UPC 2 QTY 5 UPC 3 QTY 2 ETC...
              • 4. Re: Database Script Help

                     Each UPC will have it's own count.The relationship matches by scanned barcode data so as new codes are entered, the relationship matches to different records and creates a new record each time a new barcode is scanned.

                     IF your scanner can be configured to put characters at the beginning and end of the scan as most can, you can use script triggers to automate the scanning process, but to keep it simple and just for script testing purposes, you can put a button next to your scan field, click in the field to put the cursor there and then click the button.

                     Add one more script step to your script at the end:

                     Set Field [Maintable::ScanField ; "" ]

                     So that each time you click the button, the script will clear the scanned data for the next one.

                     These are all "just getting started" suggestions. It will take more work to make this a full up solution.