9 Replies Latest reply on Aug 9, 2015 1:19 PM by AndrewCrisford

    Using field value to trigger script

    AndrewCrisford

      Title

      Using field value to trigger script

      Post

      Hi, 

       

      Im trying to make an inventory database, that tracks inventory movements. 

      So far, I have a stock database, which is all the inventory we have in the factory.

      When something goes out to site, i want it to appear in a new location but also subtract the amount that went to site from the inventory we have in the factory.

      For example, if we have 40 kits of epoxy in stock, and then 10 goto site, I'd need it to subtract 10 from the factory and add 10 to the new location.

      Is there a way to do this without the user having to enter units out, and then also adding a new record for units in? Because that could very easily lead to user input mistakes, should someone forget they need to do both.

      So i was thinking there could be a script with an If statement, which triggers only if the location of the item is not equal to 'Factory', then also the calculation part which would need to identify the item name and its location then subtract by the quantity entered.

      Im sure the answer is quite obvious, but I'm still quite new to this program,

      Thanks in advance, you're help is much appreciated

        • 1. Re: Using field value to trigger script
          philmodjunk

          I'd put a popover botton labeled Transfer with global fields for recording the Qty, the location TO which the stock is being transferred, the Location FROM which the material is being transferred, plus any other fields you need to document the transfer. A "Save" button in the popover panel can then run the scrip that generates both transactions in your inventory transactions table.

          • 2. Re: Using field value to trigger script
            AndrewCrisford

            Hi Phil, 

            Thanks, I've organised a seperate layout to include location to, location from, quantity and date.

            How do i go about creating the script? Im in the script workspace now, and I've created one called Inventory Movements and appended it to the Save button. But I have absolutely no idea how to go about writing the script

            Sorry I'm still very new to this program

            Thanks for your support :)

            • 3. Re: Using field value to trigger script
              MarkGores

              not to step on any toes but you'd probably want something that:

              Set field (factoryquantitiy; factoryquantity - movedquantity)

              Set field (locationquantity; locationquantity + movedquantity)

               

              • 4. Re: Using field value to trigger script
                AndrewCrisford

                Thanks Mark, 

                So, would i append the quantities to the locations, with items and locations having a parent-child relation, the item being the parent?

                I think that might work, ill give it a try but it will mean redesigning the tables and relationships

                • 5. Re: Using field value to trigger script
                  MarkGores

                  I didn't mean to change any tables or relationships, just have a script that uses two set field commands to either add or subtract the "qty being moved" to/from the two location qty fields.

                  • 6. Re: Using field value to trigger script
                    philmodjunk

                    Mark seems to be describing the "other method" for tracking inventory changes where Andrew appears to be describing a Transactions Table approach where each change is logged as a separate record and summary fields compute the current inventory levels. There are advantages and disadvantages to both approaches and some systems end up as a hybrid between the two approaches.

                    What I described does not use a separate layout as the popover is a button that can be placed right on the "ledger" layout where your users are logging inventory changes. This assumes that you are using FileMaker 13 or newer.

                    • 7. Re: Using field value to trigger script
                      AndrewCrisford

                      Thanks Mark & Phil, 

                      Mark, for your method, what would happen if the item record was not already present at a location? Does the script need some kind of duplicate record function to copy all the item information, then set field to change location and quantity? And would that need some kind of If Then Else function before that to determine if there is or isn't a record for that item already present?

                      • 8. Re: Using field value to trigger script
                        AndrewCrisford

                        So this is what i have so far in my scripts.

                        Im really unsure about the format the perform find should be in, since i have a 2 level value list for the items, which starts with category, then the items. Can i still search direct for items? Or do i search category first, then item?

                        • 9. Re: Using field value to trigger script
                          AndrewCrisford

                          In what format can i enter the criteria in the find?

                          If i execute this script, then goto modify find to check what it actually searched for, it is searching for = Stock Transactions::Item, rather than the actual value of that field. Do i need to put brackets or speech marks around it or something so it recognises it as a field?