13 Replies Latest reply on Jul 14, 2009 10:57 AM by WillGonz

    Log when items are scanned out of inventory.

    WillGonz

      Title

      Log when items are scanned out of inventory.

      Post

      So I modified the inventory database that comes with FileMaker Pro 10.  Now I can scan items in and out of the inventory.  

       

      How can I add a log that does a date stamp for every time something is logged out of the inventory?

       

      For example have a field value that appends instead of overwrites. 

       

        • 1. Re: Log when items are scanned out of inventory.
          philmodjunk
            

          If I were implementing an inventory database, I'd set it up like a check register. Each time you receive materials, you create a new record to log them in (just like a deposit). Each time you remove materials you would also create a new record (just like a check or withdrawal).

           

          Define a field to identify the material being logged, a time stamp or date field to record the date of the "transaction), separate number fields for "Materials added", "Materials Removed, a calculation field Materials added - materials removed and a summary field to compute a running total of the calculation field.

           

          I don't know if the sample that comes with FMP 10 works like that or not, but with the above approach, each inventory addition/removal can trigger the creation of a new time or date stamped record to log the change. Using that approach, I don't see the need for appending time stamp data to a field.

          • 2. Re: Log when items are scanned out of inventory.
            WillGonz
              

            We have 300+ items and some of each item can have 1000 items each.

            I created another table and then I will try to time and date stamp as items are removed and added.

             

            • 3. Re: Log when items are scanned out of inventory.
              WillGonz
                

              Is there a way to inject a value into a table?  For example when using Insert Calculated Result in a script I think it assumes that the field is in the Layout.  I want it to just add it into the table.

               

               

              • 4. Re: Log when items are scanned out of inventory.
                philmodjunk
                  

                Use Set Field instead. It does not require the fields it references to be present on the layout.

                 

                You can even use set field to create a new related record if you set things up correctly.

                • 5. Re: Log when items are scanned out of inventory.
                  WillGonz
                    

                  It doesn't appear to work. Here is my script. 

                  Set Error Capture [ On ] 

                  If [ IsEmpty(Inventory::BarCodeOut) ] 

                  Exit Script [  ] 

                  End If 

                  Enter Find Mode [  ] 

                  Set Field [ Inventory::ItemBarCode; Inventory::BarCodeOut ] 

                  Perform Find [  ] 

                  If [ Get(FoundCount)>0 ] 

                  Go to Record/Request/Page 

                  [ First ] 

                  Loop 

                  Set Variable [ $CurrentCount; Value:Inventory::Amount_In_Stock ] 

                  Insert Calculated Result [ Inventory::Amount_In_Stock; ($currentcount-1) ] 

                  [ Select ] 

                  Set Field [ BarCodes::BarCode; Inventory::BarCodeOut ] 

                  Set Field [ BarCodes::ScanOutDate; Get(CurrentDate) ] 

                  Set Field [ BarCodes::ItemName; Inventory::Item ] 

                  Go to Record/Request/Page 

                  [ Next; Exit after last ] 

                  End Loop 

                  End If 

                  If [ Get(LastError)="401" ] 

                  Speak [ Text: "No inventory items were found with this barcode number." ] 

                  [ Wait for completion ] 

                  End If 

                  Set Field [ Inventory::BarCodeOut; "" ]

                   

                  And then my tables in the other database are:

                  BarCode Text Indexed 

                  ItemName Text Indexed 

                  ReorderInDate Date 

                  ScanInDate Date 

                  ScanOutDate Date 

                   

                   

                  • 6. Re: Log when items are scanned out of inventory.
                    etripoli
                      

                    Like my suggestion in your previous thread, and as Phil suggested, have a table that contains records for each time an item is checked-in or -out.  It's hard to tell from the script what you are trying to accomplish from the Barcodes table, but it looks like you are trying to to tracking of some sort.  Two questions: do you have 1 record per item in the Inventory table, with a unique barcode per item, and do you have one record per barcode in the Barcode table?  If so, I don't see why they should be two separate tables.

                     

                    In any case you can replace:

                    Set Variable [ $CurrentCount; Value:Inventory::Amount_In_Stock ] 

                    Insert Calculated Result [ Inventory::Amount_In_Stock; ($currentcount-1) ]

                     

                    With:

                    Set Field [ Inventory::Amount_In_Stock; Inventory::Amount_In_Stock-1 ]

                     

                    I'm also not sure why you are looping through records in the Inventory table, if a single barcode is related to a single record.

                    • 7. Re: Log when items are scanned out of inventory.
                      WillGonz
                        

                      Each group of items has one barcode. So there might be 50+ items per 1 bar code.

                      We have 300 groups of items.

                       

                      What I want to do is track what items were scanned in and out with a time and date stamp. Append the data so I have an activity log.  So I thought I could create a new table with the values of the Item name, barcode, scanned in date, scanned in time, scanned out date and scanned out time.   I thought as the item is scanned in/out I could just drop the values in the other values in the other table.

                       

                      Thanks

                       

                      • 8. Re: Log when items are scanned out of inventory.
                        etripoli
                          

                        So, when you scan the barcode, you want qty 1 deducted from all the items in the group?  And you want 1 record created in the Activity Log for each of those items, everytime the barcode for a group is scanned? 

                         

                        I actually believe you can use a 'Set Field command to do what you want.  Through a layout, based on a table (Scan) that has a new record created everytime a barcode is scanned along with a created timestamp field, linked to a table (Items) that has 1 record per item by the barcode, and a global timestamp field.  In turn, that table, is linked to your Activity Log table, based on the item, barcode, and calculated timestamp, with the 'create records' option turned on.  Run the 'Set Field' command on a field in the Activity Log, and it should create the records and set the value.

                         

                        Create a new record in the first table,  set the direction, run a script to store the timestamp in a variable, then go to related records in the Items table (or use Perform find, my choice), set the global timestamp field from the variable, then use Set Field on one of the fields in the related Activity table.  Go back to the Scan table, create a new record, and wait for the next scan.

                         

                        Edit: fixed the spelling of 'Items'

                        • 9. Re: Log when items are scanned out of inventory.
                          WillGonz
                            

                          When you say "with the 'create records' option turned on." does that mean the area in the Relationships area under Manage Database and then the "=" sign and Edit Relationship?

                           

                          Thanks 

                          • 10. Re: Log when items are scanned out of inventory.
                            etripoli
                               Yes, '=' sign between the 'group/barcode', 'item', and 'timestamp' fields, with the 'Allow creation of records...' option checked.
                            • 11. Re: Log when items are scanned out of inventory.
                              WillGonz
                                

                              So here is what happens.  

                              When I scan a item for the first time it logs the Time, Date, Item name and barcode.  But when I scan it again it replaces the last time and date.  

                               

                              What I want it to do is keep the past time and dates.  I pretty much want to create a new record in the other table for each scan.

                               

                              The inventory function is working wonderfully.  Items scan in and scan out.  I just want to keep a log in another table to show what goes in and what goes out to help reporting.

                               

                              Thanks,

                               

                               

                              • 12. Re: Log when items are scanned out of inventory.
                                WillGonz
                                   Well I created a new script entirely and used the New Record/Request command and that works how I want it to.  I am able to create a new record with data in it.  But when I use it in my other script it adds the New Record to the Current Table.
                                • 13. Re: Log when items are scanned out of inventory.
                                  WillGonz
                                    

                                  I got it.  I used Go to layout and flipped to the Log then used New Record/Request and that works like I want.  After I fill up my tables with data I switch back to the original layout.

                                   

                                   

                                  Thank You for all your help!!!