13 Replies Latest reply on Jul 9, 2014 4:30 AM by GokhanKamu

    Barcode System for rental equipments

    GokhanKamu

      Title

      Barcode System for rental equipments

      Post

           Hello,

            

           I'm trying to create barcode system for my company. In our invoice page we have "commit inventory" and "recall inventory items" buttons and they are working fine.

           I setted up global barcode scanning system which I saw on this forum and it is working fine too.

           Now my problem is recalling. I want to scan all returning items and after that clicking "recall inventory" must retrieve those items to inventory (amount of stock, and item history will be automatically updated with code. It is already setted when I manually recall them it works.)

           How can I create layout or which script work for returning scans?

        • 1. Re: Barcode System for rental equipments
          philmodjunk

               Most inventory systems track inventory through one of two methods: Summary fields in a transactions table (History might be adapted to this purpose), or a record in a table with one record for each bar code, is updated each time an item is added to or removed from inventory.

               Which method will you use?

               Does the bar code identify a specific piece of equipment or a type of equipment. (If you have 3 identical pieces of equipment, is the bar code of each one the same or unique?)

          • 2. Re: Barcode System for rental equipments
            GokhanKamu

                 Hi Phil,

                  

                 Barcodes identifies specific piece of equipment. One item = one barcode. 

                 I'm not sure but I think we are tracking our items through summary fields.

                 When I add item to invoice and click commit button manually it creates new line to "line items page" and it automatically updates item history and same goes for revert inventory button. We can see last activity there and easily track where is our equipment right now.

                 We want to set up new filemaker to our computer at warehouse so he can scan in and scan out equipments easily there. He doesn't know using computer much. We are having problem with excel + barcode scans there and we are trying to easen things a bit.

                 I looked your previous comments and saw one with globalbarcoding. I tried it and it works but I couldn't find how to do similar script for returning items.

            • 3. Re: Barcode System for rental equipments
              philmodjunk

                   You'll need to look at how you would do this manually, without a bar code and then figure out how to use the script with a barcode to do the same thing. It shouldn't be terribly different from those other scripts, just like it does, your script can use the scanned bar code to find the record that logged the item out and update it to show when it was returned.

                   And a script to create a new record to log the item out is even simpler as no find need be performed, it should simply be a case of creating a new record and setting to a field to the value of the global field into which you scanned a barcode. A timestamp field can auto-enter the date and time the item was logged out of inventory.

                   But note how many guesses that I had to make about how you are set up to do this. Those guesses could easily be wrong.

              • 4. Re: Barcode System for rental equipments
                GokhanKamu

                     Allow User Abort [ Off ] 

                     Set Error Capture [ On ] 

                      Commit Records/Requests [ No dialog ]

                     Freeze Window 

                     If [ not invoices_PRODUCTS::id_constant ]

                     Exit Script [  ] 

                     End If 

                      If [ Get ( ScriptParameter ) = "commit" and Invoices::flag_items_committed ≠1] 

                     Set Field [ Invoices::flag_items_committed ; 1 ] 

                     Else If [ Get ( ScriptParameter ) = "commit" and Invoices::flag_items_committed = 1 ] 

                     Exit Script [  ] 

                      Else If [ Get ( ScriptParameter ) = "uncommit" and Invoices::flag_items_committed = 1 ] 

                     Set Field [ Invoices::flag_items_committed ; "" ] 

                     Else If [ Get ( ScriptParameter ) = "uncommit" and Invoices::flag_items_committed ≠1] 

                     Exit Script [  ] 

                     End If 

                      Set Variable [ $id_invoice; Value:Invoices::ID_Invoice ] 

                      Loop 

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

                     Go to Portal Row [ Select; No dialog; $row] 

                     Exit Loop If [ Get ( LastError ) = 101 ]

                     If [ T12j1_invoices::id_constant ] 

                     Set Variable [ $quantity ; Value:Invoices::Quantity ]
                      
                     Go to Related Record [ From table: “Invoices” ; Using layout: “Product_Entry” (PRODUCTS) ]
                      
                     Set Variable [ $old_stock; Value:Products::Amount_in_Stock ] 
                     Set Variable [ $id_product; Value:Products::ID_Product ] 
                      If [ Get ( ScriptParameter ) = "commit" ] 
                     Set Field [ Products::Amount_in_Stock ; Products::Amount_in_Stock - $quantity] 
                     Else If [ Get (ScriptParameter) = "uncommit" ] 
                     Set Field [ Products::Amount_in_Stock ; Products::Amount_in_Stock + $quantity ] 
                     End If 
                      
                     Set Variable [ $new_stock; Value:Products::Amount_in_Stock ] 
                     If [ Products::Amount_in_Stock < 0 ] 
                     Set Variable [ $error; Value:$error & Products::Product & ": " & Products::Amount_in_Stock & ¶] 
                     End If 
                      
                     Go to Layout [ “Audit_Log ] 
                     New Record/Request 
                     Set Field [ Audit_Log::id_product ; $id_product ] 
                     Set Field [ Audit_Log::Log_Table; "Products" ] 
                     Set Field [ Audit_Log::id_invoice ; $id_invoice ] 
                     Set Field [ Audit_Log::Log_Date; Get (CurrentDate) ] 
                     Set Field [ Audit_Log::Log_Time; Get (CurrentTime) ] 
                     Set Field [ Audit_Log::Log_Account ; Get(UserName) ] 
                     Set Field [ Audit_Log::Log_Field; RightWords ( Substitute ( GetFieldName ( Products:: Amount_in_Stock ) ; "::" ; "  " ) ; 3) ] 
                     Set Field [ Audit_Log::Log_From ; $old_stock ]
                     Set Field [ Audit_Log::Log_From ; $new_stock ]
                     Go to Layout [ original layout ] 
                     End If
                     End Loop 
                     Commit Records/Requests [ No dialog ]
                     If [ $error ≠"" ] 
                     Show Custom Dialog [ Title: "Warning"; Message: "The following items are out of stock.  Here are the stock amounts for each that is out." & ¶ & ¶ & $error; Default Button:“OK”, Commit:“Yes”] 
                      
                      
                     This is the commit / uncommit script. It took me an hour to copy them. Sorry if I'm being noob about coding I don't have experience really.
                      
                     I think Item History is managed from Audit_Log.
                      
                     I found what I want to do but I really don't know how to do. I'll use global barcode filed and continue to use this script to commiting items.
                     But for returning items I'll create new script or even I can use bottom part of (audit_log) this script and merge it with your global barcode script?
                      
                     Is there any reson for this won't work?
                • 5. Re: Barcode System for rental equipments
                  philmodjunk

                       Is this a script that you wrote and thus understand or was it created by someone else?

                       Does this script work correctly? (There's a go to related records step that looks like it may be incorrect and you refer to two different occurrences of both Projects and Invoices in your script--which might lead to "context" errors here.)

                       The script refers to the following Tutorial: What are Table Occurrences? names:

                       invoices_PRODUCTS
                       Invoices
                       Products
                       T12j1_invoices

                       When you perform this script, you are on a layout that will refer to a table occurrence name in Layout Setup | Show Records From. This layout apparently has a portal. This portal will also refer to a table occurrence name in Portal Setup | Show Related Records From. What are those two names?

                       And how are all of these table occurrences linked to each other in relationships? (You may want to upload a screen shot of Manage | Database |Relationships, but cropped to show only these table occurrences. If you do that, please make sure to expose the match field names before capturing the screen shot.)

                       What value in encoded into your barcode labels? Product ID?
                        
                       While not relevant to the main issue of your post here, I spotted a small script issue with these steps:
                        
                       Set Field [ Audit_Log::Log_From ; $old_stock ]
                       Set Field [ Audit_Log::Log_From ; $new_stock ]

                       Perhaps that's an error made when typing in your script, but notice that these two steps assign values to the same field. Thus, the second set field step will overwrite the data set to the field by the first step.
                        
                       For future reference:

                       To post a script to the forum:

                         
                  1.           You can upload a screen shot of your script by using the Upload an Image controls located just below Post a New Answer.
                  2.      
                  3.           You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF to your clipboard for pasting here. (with this approach, you can get multiple script steps on the same line, please edit the pasted text by inserting some returns to separate those steps.)
                  4.      
                  5.           If You have FileMaker Advanced, you can generate a database design report and copy the script as text from there.
                  6.      
                  7.           If you paste a text form of the script, you can use the Script Pretty box in the Known Bugs List database to paste a version that is single spaced and indented for a more professional and easier to read format.
                  • 6. Re: Barcode System for rental equipments
                    GokhanKamu

                         You have absolutely great eye.

                         This script was created someone else I don't have that knowledge to be honest. :/

                          

                    invoices_PRODUCTS
                    Invoices
                    Products
                    T12j1_invoices    (I think I messed this up when I write. There isn't Invoices_PRODUCTS and T12j1_invoices. 

                    Invoices_PRODUCTS and T12j1_Invoices are = Invoices

                          

                         I tried to upload image but couldn't do it.

                    Table is like this;

                          

                    Products------Inv_LineItems-----Invoices

                          

                    Products::id_Product = inv_lineItems::id_Product (allow creation

                    inv_lineitems::id_constant  = Invoices::flag_product_id 

                    inv_lineitems::id_invoice  = Invoices::id_invoice

                    inv_lineitems::id_product  = Invoices::id_product

                    inv_lineitems::id_project  = Invoices::id_project

                    and audit log takes id_constant from Products and id_invoice from Invoices.

                          

                    Product ID's our barcodes.

                          

                    Set Field [ Audit_Log::Log_From ; $old_stock ]

                         Set Field [ Audit_Log::Log_From ; $new_stock ] (this one is actually "Log_To"
                    • 7. Re: Barcode System for rental equipments
                      philmodjunk

                      Invoices_PRODUCTS and T12j1_Invoices are = Invoices

                      Are you sure about that?

                      by common naming conventions, the first would be an occcurrence of the Products table and the second would be an occurrence of the Invoices table.

                      Products------<Inv_LineItems-----Invoices

                      Products::id_Product = inv_lineItems::id_Product (allow creation

                           makes sense (though allow creation is not typical for this).

                      inv_lineitems::id_constant  = Invoices::flag_product_id 

                      inv_lineitems::id_invoice  = Invoices::id_invoice

                      inv_lineitems::id_product  = Invoices::id_product

                      inv_lineitems::id_project  = Invoices::id_project

                      does not make sense.

                      What kind of field is id_constant? why is it named "Constant"--which implies a value that is the same for all records?

                      Why the additional match fields (id_constant, id_product and id_project) linking invoices to inv_lineitems?

                      What would make sense to me, as it is the typical relationships for an invoicing system is:

                      Invoices----<Inv_lineItems>------Products

                      Invoices::id_invoice = Inv_lineItems::id_invoice (allow creation enabled for inv_lineitems)
                           Products::id_product = inv_lineItems::id_product

                      This allows you to set up invoices on a layout based on invoices with a portal to inv_LineItems. LineItems::id_product would then be placed in that portal and set up with a drop down list or popup menu for selecting products to be listed on that invoice.

                           

                                I tried to upload image but couldn't do it.

                           Feel free to describe what you tried to do and where it failed for you. Once you have an image file of one of the three file types permitted, it's pretty easy to upload that image file.

                      • 8. Re: Barcode System for rental equipments
                        GokhanKamu

                             Yes you are right I messed it up. id_constant it for flagging actually. There is a tickbox product is availible for invoice or not. If you select that then id_constant is 1 if you don't its blank

                        • 9. Re: Barcode System for rental equipments
                          philmodjunk

                               That clears things up quite a bit. The typical invoicing relationships can be found as:

                               Invoices----<Inv_Line_Item2>------Products

                               And how do you record whether an item is in or out of inventory?

                               Do you set a value to a field in the inventory table for that purpose?

                               A rough outline of a script to use barcode scanning to return items back to inventory:

                               Scan bar code into global field

                               Use bar code data in global field to perform a find or to use Go to Related Records to find the record for that item in the Inventory table.

                               Update the field to show that it is back in inventory

                               An unstored calculation field in invoices can update to show when all the items listed in inv_LINE_ITEMS on that invoice have been returned

                               Note: this assumes that you don't set up invoices for "future rentals"  or reservations but only create an invoice at the time the person renting the equipment takes possession of the equipment.

                          • 10. Re: Barcode System for rental equipments
                            GokhanKamu
                                 Set Field [ Audit_Log::id_product ; $id_product ] 
                                 Set Field [ Audit_Log::Log_Table; "Products" ] 
                                 Set Field [ Audit_Log::id_invoice ; $id_invoice ] 
                                 Set Field [ Audit_Log::Log_Date; Get (CurrentDate) ] 
                                 Set Field [ Audit_Log::Log_Time; Get (CurrentTime) ] 
                                 Set Field [ Audit_Log::Log_Account ; Get(UserName) ] 
                                 Set Field [ Audit_Log::Log_Field; RightWords ( Substitute ( GetFieldName ( Products:: Amount_in_Stock ) ; "::" ; "  " ) ; 3) ] 
                                 Set Field [ Audit_Log::Log_From ; $old_stock ]
                                 Set Field [ Audit_Log::Log_From ; $new_stock ]
                                  
                                 This new stock and old stock keeps its records. In my 3rd post I tried to write the script. Commit and revert buttons records item movements.
                                 And on product page there is a portal from Audit_Log for each product-related entry to show.
                                  
                                 Now I got your point. Can I do same thing instead of Inventory table to Audit_Log. Because audit log has permission to edit line_items and amount in Stock at inventory table/product page.
                                  
                                 So in our page I don't need to update the field instead of it I need to create new record at audit_log.
                                  
                                  
                            • 11. Re: Barcode System for rental equipments
                              GokhanKamu

                                   In that thable Ürün Kodu = Id_Product and it is barcodded line. When you add Id_Product in line it automaticaly adds Ürün. 

                                   Önceki sayı is amount of stock before adding line and sonraki sayı is after adding line.

                                   When I click commit or revert button on invoice it automaticaly stamps times but When I manually enter id_Product to Ürün Kodu it doesn't do that.

                                    

                                   Btw we wont do anything like future rentals.

                              • 12. Re: Barcode System for rental equipments
                                philmodjunk

                                     What would be the purpose to doing this in the audit log?

                                     This, by the way, is a part of your system that I originally misunderstood to be used as a way to track changes to your other tables.

                                     Using a transactional log to track what is in or out of inventory could be accomplished through using summary fields. This is the basic method described in my Inventory Ledger example--which also avoids the need for a separate table as it does it all in the line items table: Managing Inventory using a Transactions Ledger

                                     But this is most often used when each inventory record identifies a group of objects, not one where you have a separate record for each equipment item that you have available to rent. It would seem much simpler just to update a field in this table to mark whether an item is in or out than to set up a completely separate transactions type table to log the same information.

                                • 13. Re: Barcode System for rental equipments
                                  GokhanKamu

                                       Sorry for late reply. I talked with the guys at the warehouse who is responsible scanning incoming/outgoing items. He said he can't handle this and need basic approach. 

                                        

                                       I created 2 page for his version of filemaker with security.

                                       He will see only two pages one of them Outgoing and other one is incoming.

                                       I put line item portal and commit button there and it works perfect.

                                       I'm still having problem with incoming page of that. Should I add another line item there. and revert button.

                                       And I have one last question I know its easy one but I don't really know how. Our scanner works with "enter" button. After adding code it hits "enter"

                                       Whats the code for after hitting "enter" go to next line and ready for another entry in line item? If I do that I think everything will work perfect