1 2 Previous Next 22 Replies Latest reply on Sep 15, 2011 3:34 PM by philmodjunk

    Help with Scripting

    ChrisCameron

      Title

      Help with Scripting

      Post

      I am having a scripting issue. My issue is that since I have created a Barcode Script which when selecting a field within fm go a camera app will open up on my iphone/ipad and allow for picture to be taken of barcode it then will insert the field into the line items, however when I update the quantity the Update Inventory Button does not work. I have copied and pasted the scripts that I have for Barcode Scanning. I am using the FileMaker Pro Update Inventory script as well. Any advice would be greatly appreciated. Thanks.

       

      Barcode Scanning:

      Open URL

      "icody://scan?callback=fmp7script%3A//%24/" & Get ( FileName ) & "%3Fscript%3Dscan%26param%3D%23%23%23barcode%23%23%23"

      Return Scan

      Set Field Get ( ScriptParameter )

        • 1. Re: Help with Scripting
          philmodjunk

          Your "script" doesn't tell us enough for us to be able to help you.

          The Open Url step appears to open some other file or application, passing it some info such as the name of your database fiel, but you do not describe it or what it does.

          "Return Scan" is not a valid script step in filemaker. Perhaps you have a plug in installed? If so what one? If you do have a plug in installed, you should contact the plug in developer for assistance as they will be sufficiently familiar with their plug in and (hopefully) FileMaker to be better able to help you.

          • 2. Re: Help with Scripting
            ChrisCameron

            Ok, I have created a database using the Invoices from the Starter Solution within FileMaker. Under the invoices portion I have set up the first field under Product item to perform a script. The script will only run from fm go and when the field is selected a barcode app opens on my iphone/ipad and allows to scan a barcode. Once the barcode is scanned, the barcode is then transmitted and placed correctly into the product item and the rest of the information based on item is automatically placed in the rest of the line, (Description, Cost, etc) My issue lies in when I try to update the inventoryusing the button and script that came with the starter solution does not work. I am not sure if the update inventory script is somehow conflicting with the script that I have placed in the product item field. Please let me know if this helps or what other information you need to assist. Thanks.

            • 3. Re: Help with Scripting
              philmodjunk

              Ok,

              I don't think the scanner is interfering with the script that updates inventory. The inventory management portion of Invoices leaves a lot to be desired. Several very simple user errors, such as clicking the update button twice can produce erroneus inventory totals. As I recall, the script also checks a field in Products and does not update inventory unless the correct value is in this field. If I am remembering correctly, this is done so that you can selectively control which projects will have an updateable inventory and which will not. You might check to see if that field has the correct value in your file.

              • 4. Re: Help with Scripting
                ChrisCameron

                Since, I am not to familiar with scripting within FM. Is there an alternate easier script that will just update the inventory without all the other checks that the built-in script performs. 

                • 5. Re: Help with Scripting
                  philmodjunk

                  Not as part of the starter solution.

                  There is an alternative approach that computes inventory levels directly from the records in the line items table. This requires much less scripting, maybe none at all.

                  Search this forum site using "inventory Ledger" in the "search the forum" box at the top of this screen and see if what one of those threads describes sounds like a better approach to you.

                  • 6. Re: Help with Scripting
                    ChrisCameron

                    Is there anyway that you would be able to take a look at the update inventory script that is apart of the starter solution invoices. I tried to make a new database from scratch and only included the update inventory script and it will still not function. I am looking into the Inventory ledger right now but am hesitant if creating in table when I am hoping I can just use the line items and tables in place already. Thanks for all of your help. At least I have a few more places to look now.

                    • 7. Re: Help with Scripting
                      philmodjunk

                      The line items table can function as the inventory ledger with added fields. I'll add this thread to favorites and if I get the time, I'll take a look at that script. It's probably possible to delete a small portion of the code to eliminate that If statemtent. Or you could simply update the records in the Product table so that this is not an issue by putting the expected value in this field.

                      • 8. Re: Help with Scripting
                        ChrisCameron

                        Thank you for all of your help. I truly do appreciate it. I saw in another thread you suggested making the following table

                        TransDate

                        ItemID

                        AmtIn

                        AmtOut

                        cBal : AmtIn - AmtOut

                        STotalOnHand : Summary, total of cBal, running total, restart total on ItemID

                         

                        I have a Sales Date (TransDate) and Product ID(itemID) field, already within my Invoices table. Should I just add the Amtin, Amtout, Cbal and Stotalonhand fields.

                         

                        • 9. Re: Help with Scripting
                          ChrisCameron

                          Thank you for all of your help. I truly do appreciate it. I saw in another thread you suggested making the following table

                          TransDate

                          ItemID

                          AmtIn

                          AmtOut

                          cBal : AmtIn - AmtOut

                          STotalOnHand : Summary, total of cBal, running total, restart total on ItemID

                           

                          I have a Sales Date (TransDate) and Product ID(itemID) field, already within my Invoices table. Should I just add the Amtin, Amtout, Cbal and Stotalonhand fields.

                           

                          • 10. Re: Help with Scripting
                            philmodjunk

                            Take a look at Line Items.

                            AmtOut is the same field as the Qty purchased field in line items, so you can add these other fields to line items and set up TransDate to lookup the sales date from Invoices. (You wouldn't just use the Invoice Date field here as not all records in this table will necessarily link to a record in Invoices.)

                            • 11. Re: Help with Scripting
                              philmodjunk

                              I'm going to list several "fixes" in one post here:

                              For every product in your database, go to products and set a "reorder level" to some value 1 or larger. This should enable your update inventory script to work correctly.

                              If you want to remove this aspect of the script, open up the script and delete the very first and last lines of this script. This removes the If test that checks for a reorder level of 1 or greater before it permits an inventory update.

                              Here's a demo file for the inventory ledger method that I mentioned: http://www.4shared.com/file/zoOpZNwX/InvoicewInventoryLedgerDemo.html

                              • 12. Re: Help with Scripting
                                ChrisCameron

                                Okay, I went ahead and added the fields within LineItems. Can you take a look and make sure I have all the fields correctly set-up. Then I believe all I need to do is now figure out how to write the script! Thanks again for your help

                                 

                                AmtIn Number

                                cBal Calculation (Number) = AmtIn - Quantity AmtIn - Quantity

                                Product ID” is made, copy the first matching value from “Related Products::Price” If no match, use: “” Product ID Number

                                Quantity Quantity Number Auto-enter calculation: = 1, Evaluate even if all referenced fields are empty, replaces existing value

                                STotalOnHand Text Summary, total of cBal, running total, restart total on ItemID

                                TransDate Calculation (Number) Unstored, = Invoices::Sale Date

                                • 13. Re: Help with Scripting
                                  philmodjunk

                                  What script would that be?

                                  One of the advantages to the "ledger" approach is that inventory counts update automatically with no scripting required. The only scripting you might do is to automate the sorting and possibly a find on any reports you set up for checking inventory levels.

                                  cBal should only refer to two fields, not three. QuantityIn - QuantitySold (use your field names in place of mine).

                                  QuantitySold is placed in your line items ledger and records the number of items sold for that row in the portal. QuantityIN is a field you use to record the reception or manufacture of new product so that you can increase your inventory count. Typically, you'd have a different layout to this table where you'd log other inventory changes than sales such as receiving new product, shrinkage, returns to vendor, etc...

                                  TransDate should be a field of type date, but you can set a looked up value field option to auto-enter the invoice date. You need to set it up this way so that you can enter a date in this field when you recieve new product or log other non-sales changes to your inventory in the ledger table.

                                  You also need an InvoiceID field (often labeled _fk_InvoiceID) so that each sales record can link directly to the invoice table. (Non sales transactions will leave this field empty.)

                                  • 14. Re: Help with Scripting
                                    ChrisCameron

                                    I went ahead and changed the reorder level to 1 on all products and it will update inventory. However, when I remove the quantity from the invoice the update inventory is not available. Is there a simple change to make it work both ways? This would be ideal as I wouldn't have to modify anything else and just roll the database out. Thanks.

                                    1 2 Previous Next